Hi,
I'm using union of Sales Invoice Line and Sales Cr.Memo Line as to list out Document No of both the tables as per below…
=NL("Rows", NP("Union", NL("filter","Sales Invoice Header","No.","Posting Date","01/07/08..31/07/08"), NL("filter","Sales Cr.Memo Header","No.","Posting Date","01/07/08..31/07/08")))
The question is… In the following cell, I would like to also get the tablename of which the Document No is taken from… whether it's from Invoice Line or Cr Memo Line.
How do i do that?
Cheers
RB
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Richardboak,
If the document no. has a character in it that would tell you if the document is an invoice or credit memo, then it would be easy: =IF(LEFT(CellRefToYourNL,1)="I","Invoice","Cr. memo").
However, if you cannot use that option you need to do it differently. Then you need to add something to your function so that you can distinguish the difference afterwards.
Instead of building an array on "No." only, you need to add something to that field. Let's say "I" for "Invoice" and "C" for "Credit memo":
- NL("Filter","Sales Invoice Header","=""I""&NF(,""No."")","Posting Date","01/07/08..31/07/08")
- NL("Filter","Sales Cr.Memo Header","=""C""&NF(,""No."")","Posting Date","01/07/08..31/07/08")
Now, Jet will add a character in front of the document no. A character that you may use with the IF function in the first line of my reply to display the document type.
I split your function for explanation purposes only. Of course you may put it all together into 1 single function.
Please let us know if this works for you. -
Jet Reports Historic Posts Superb…Thanks Hans,
Please sign in to leave a comment.