0

Compare 2 fields on a Linked Table

Laura Porter

March 04, 2019 22:11

None

Follow

Hi:  I am wondering if you can compare fields on a linked table?  I am getting an invalid field error and I am using the same comparison i used without the linked table part.....  Any suggestions would be great!

=NL("Rows","Sales Invoice Header","Order No.","Posting Date",$C$5,"Link=","Sales Invoice Line","Document No.","=No.","No.",$C13,"Quantity","=""<>""&nf(,""Original Quantity"")")

I am using this in the Rows=4 statement above it but without a linked table and that part runs fine... not sure why it doesn't recognize the field in the linked version?

=NL("Rows=4","Sales Invoice Line","No.","No.",$C$3,"Location Code",$C$4,"Posting Date",$C$5,"Quantity","=""<>""&nf(,""Original Quantity"")","Type","Item","Link=","Stockkeeping Unit","Item No.","=No.","Quantity on Hand",">0","Location Code","SURREY")

 

 

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis March 05, 2019 15:14

  Hello Laura -

  You are having the same issue (same field) in both functions?

  Does this function work for you?

  =NL("Rows","Sales Invoice Line","Original Quantity")

   

   

• 0
  
  Laura Porter March 05, 2019 20:54

  When I am in the NL Window the field Original Quantity appears on the left side of the filter window, but not on the right once I link to the table.  Yes the field is in the Sales Invoice Line Table, but seems unavailable to filter in a link situation.

• 0
  
  Harry Lewis March 06, 2019 18:20

  Thanks, Laura.  I wanted to make sure that I understood the issue (since my sample Cronus database does not include the Original Quantity field).

  The construct you are using:

  "=""<>""&NF(,""Original Quantity"")")

  is referred to as a "CalcFilter" (the filter is a "calculated" value, rather than something that is pulled directly from the database).

  Since CalcFilters require the use of the equal sign - and that character is used within LINK= only to represent linking fields between the tables - LINK= is not able to support the user of CalcFilters.

  You might be able to use the NL("Filter") function to create a group of documents numbers where the Quantity and Original Quantity fields do not match.

  for example:

  =NL("Filter","Sales Invoice Line","Document No.","Quantity","=""<>""&NF(,""Original Quantity"")")
  
  

  You could then use that to filter your function that returns the Document No. in cell C13.

  Since only document numbers matching the criteria would be in C13, you could then use a simpler version of your function:

  =NL("Rows","Sales Invoice Header","Order No.","Posting Date",$C$5,"No.",$C13)