We have two companies I am querying in which I need to pull unique values (GL accounts) from each company via a NL Rows function. Each company has many matching accounts, but a handfull of company specific accounts. In SQL terms I am wanting to join two tables and select DISTINCT accounts.
Here is what I have tried so far with no success:
I get #Value! returned
=NL("rows","G/L Account","No.",,,"No.","40000..49998","Company=","CompanyA|CompanyB")
This ends up creating a concatenation.
=NL("rows","G/L Account","No.",,,"No.","40000..49998","Company=","CompanyA")&NL("rows","G/L Account","No.",,,"No.","40000..49998","Company=","CompanyB")
Thank you in advance for looking at this!
Jet Essentials 2013
US Dynamics NAV 6.0 R2
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment You need to pull the list of accounts from both companies separately and join them afterwards.
As far as I know, only GL() can handle more than one company in the latest version of Jet Essentials.
D5=NL("Filter","G/L Account","No.","No.","40000..49998","Company=","CompanyA")
D6=NL("Filter","G/L Account","No.","No.","40000..49998","Company=","CompanyB")
D7=NL("Rows",NP("Union",D5,D6))
Will that do the trick?
rmw -
Jet Reports Historic Posts This is exactly what I need. I appreciate your quick reply!
Much appreciated rmw!
Please sign in to leave a comment.