Hello Jet supporters,
I want to create a report that shows the delivery performance of some of our vendors. I use the Purchase receipt header table to get the posting date and I use the Purchase receipt line table to get the expected receipt date. For a given period and for certain vendors I want to count the number of posted goods receipt where posting date <= expected receipt date. I made a formula that obviously will not work but gives you an impression of the direction in which I was trying to find a solution.
=NL("Count";"Purch. Rcpt. Header";;"Buy-from Vendor No.";$E6;"Posting Date";<NL("Filter";"Purch. Rcpt. Line";"Expected Receipt Date";"Document No.";"IS THE SAME AS ON THE PURCHASE HEADER") :o
I know how to work around it by creating rows for every posted goods receipt an make the date comparison per line and combine the with an excel COUNTIF function etc. etc. but there must be an smarter way.
Can somebody help me out?
Best regards,
Arnoud
Date
Votes
4 comments
-
Jet Reports Historic Posts Wow :!: You sure don't want anything easy. I tried to make this work over the weekend and couldn't get it to work until a figured out a few things.
First, here is the formula:
=NL("Count","Purch. Rcpt. Header",,"Buy-From Vendor No.",$E6,"Posting Date",$E$3,"No.","=IF(NF(,""Posting Date"")<=NL(,""Purch. Rcpt. Line"",""Expected Receipt Date"",""Expected Receipt Date"",NP(""DateFilter"",,NF(,""Posting Date"")),""Document No."",NF(,""No."")),NF(,""No.""))")
This part of the formula is a standard "Count" formula where you get the "Count" of the "Purch. Rcpt. Header" that match your filters;
=NL("Count","Purch. Rcpt. Header",,"Buy-From Vendor No.",$E6,"Posting Date",$E$3
The next part of the formula is tricky part;
=NL("Count","Purch. Rcpt. Header",,"Buy-From Vendor No.",$E6,"Posting Date",$E$3,"No.","=IF(NF(,""Posting Date"")<=NL(,""Purch. Rcpt. Line"",""Expected Receipt Date"",""Expected Receipt Date"",NP(""DateFilter"",,NF(,""Posting Date"")),""Document No."",NF(,""No."")),NF(,""No.""))")
If you have an NF(,fieldname) in your NL formula, then you force Jet Reports to look at the current record it is currently on. So, here we are telling Jet Reports to use the "Posting Date" from the current record in the NL. Same for the "No." field.
Because we are working with an IF statement within the NL we have to put the entire IF within quotes " which means that anything that would normally have quotes, now has to have 2 sets of quotes "" around the fieldname. This makes writing the formula complex and easy to get wrong. You have to take your time with this to get the number of quotes correct.
Anyway, I hope this helps. -
Jet Reports Historic Posts PS,
I would expect that if your filters have a large number of records, this formula could be slow as it has to look at each record that matches your filter criteria ("Buy-from Vendor" and "Posting Date").
Also, I just noticed, you use semicolons ";" instead of commas ",". You would need to change all the commas in my example to semicolons. -
Jet Reports Historic Posts Hi Chuck,
thanks a lot, the report is exactly what I needed and this is how our purchase manager looks right now: :D
Arnoud -
Jet Reports Historic Posts It seems this would be easy to into a report by taking the orginal formula
=NL("Count","Purch. Rcpt. Header",,"Buy-From Vendor No.",$E6,"Posting Date",$E$3,"No.","=IF(NF(,""Posting Date"")<=NL(,""Purch. Rcpt. Line"",""Expected Receipt Date"",""Expected Receipt Date"",NP(""DateFilter"",,NF(,""Posting Date"")),""Document No."",NF(,""No."")),NF(,""No.""))")
and change the NL("Count") to NL("Rows")=NL("Rows","Purch. Rcpt. Header",,"Buy-From Vendor No.",$E6,"Posting Date",$E$3,"No.","=IF(NF(,""Posting Date"")<=NL(,""Purch. Rcpt. Line"",""Expected Receipt Date"",""Expected Receipt Date"",NP(""DateFilter"",,NF(,""Posting Date"")),""Document No."",NF(,""No."")),NF(,""No.""))")and add some NF's to get the fields you want and now we have a report that is exactly what I was looking for and I was sure it couldn't be done.
Thanks
Please sign in to leave a comment.