0

Linking tables when fields do not match

Teresa Roberts

May 15, 2018 08:36

None

Follow

Hi,

I'm trying to get a list of all purchase order lines where the location code on the header does not match the location card on the line. I'm also trying to count them for a summary.

At the moment, I'm just pulling back all lines, comparing them in excel and hiding the ones that match. I've tried looking at "Link=" but I can't seem to get <> working with it. I've looked at NP("Difference") but it gets very complicated with multi value keys.

Does anyone have any ideas?

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Harry Lewis May 15, 2018 15:06

  Hi Teresa -

  Here is an example using NAV's Sales Invoice Line and Sales Invoice Header:

  =NL("Rows","Sales Invoice Line",,"=NL(""First"",""Sales Invoice Header"",""Location Code"",""No."",NF(,""Document No.""))","=""<>@@""&NF(,""Location Code"")")

  or, slightly easier:

  =NL("Rows","Sales Invoice Line",,"Location Code","=""<>@@""&NL(""First"",""Sales Invoice Header"",""Location Code"",""No."",NF(,""Document No.""))")

  If I show the Location Code for both tables:

  

  Edited by Harry Lewis May 15, 2018 15:09