0

Sort by a field from another table - BC

Amy Baker-Ramstead

June 07, 2023 20:57

None

Follow

Is it possible to sort by a field from another table? I thought it was, and I thought it was the same technique as Sort by Sum. However, I can't get it to work.

If it's possible, please share technique. If not possible, I'll survive. 

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  CascadiaDataworks June 07, 2023 22:51

  If you aren't looking for the sort by sum, you may need to use NL(Filter) or Link=.

  For example, if I wanted to sort on customer name but need a list of customers with invoices in January 2023, my query might look something like this:

  =NL("Rows","Customer","No.","+Name","*","Link=","Cust. Ledger Entry","Customer No.","=No.","Posting Date","01/01/23..01/31/23","Document Type","Invoice")

  OR

  =NL("Filter","Cust. Ledger Entry","Customer No.",Posting Date","01/01/23..01/31/23","Document Type","Invoice")

  =NL("Rows","Customer","No.","+Name","*","No.", [REFERENCE TO NL FILTER])

  It depends on your report and what tables you need to use.

• 0
  
  Marc July 22, 2023 17:07

  This is difficult to answer properly without more information. I'm assuming you're using Table Builder or NL(Table).

   

  That being the case, you should be able to add a sort after the new table has been created. So your complete formula might look something like:

  =NL("Table","[table 1]","Fields=",{B5:H5},"TableName=","Whatever","Filters=",B2:C3,"Link=,B4,"+Field in new table","*")

  Using https://support.jetglobal.com/hc/en-us/articles/218953718-Sorting-Your-Jet-Reports as a source.