0

Purchase Header Table / NAV

Jet Reports Historic Posts

January 10, 2013 20:51

None

Follow

Hello Community,

I am looking to prepare a jet report measuring the ratio with which the "Vendor Order No." field is not filled.
For this I am looking to calculate as follows:
[count of purchase header entries where vendor order no is not entered] / [count of all purchase header entries] *** with certain additional filters ***


The formula for just the purchase header table I am using is shown below and functions properly:
=NL("Count","Purchase Header",,"Document Date",[date range],"Location Code",[specified location],"Assigned User ID",[specified user],"Document Type","Order|Return Order")
=NL("Count","Purchase Header",,"Document Date",[date range],"Location Code",[specified location],"Assigned User ID",[specified user],"Document Type","Order|Return Order","Vendor Order No.","@@")

My question is, if I am looking at historical data, where do I need to look [which table]?
Do I need to search in the Purchase Header Archive table? [if so, how do I ensure I am not counting multiple versions more than once, but only the very last version in the archive?]
Or do I need to construct something using the Purch. Rcpt. Header table?

…I guess this is more of a Navision question, any help that could be given is greatly appreciated…

Thank you and best regards,
Nicolai

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Jet Reports Historic Posts January 14, 2013 06:30

  Hi Nicolai,
  
  You're right, if you want to look for historical PO data, you need to look in Purchase Header Archive table.
  
  And luckily for you, there is one field called "No. of Archived Versions" that tells you how many versions there are for any given PO No.
  And therefore, you can modify your query into this to make it work:
  
  =NL("Count","Purchase Header Archive",,"Document Date",[date range],"Location Code",[specified location],"Assigned User ID",[specified user],"Document Type","Order|Return Order","Version No.","=NF(,""No. of Archived Versions"")")
  and
  
  =NL("Count","Purchase Header Archive",,"Document Date",[date range],"Location Code",[specified location],"Assigned User ID",[specified user],"Document Type","Order|Return Order","Vendor Order No.","@@","Version No.","=NF(,""No. of Archived Versions"")")
  these formulas will make sure you get only the latest version of the archived PO.
  
  Please let me know whether it work for you or not.
  
  Andy