0

Filter by newest version sales order

Jet Reports Historic Posts

September 15, 2016 14:45

None

Follow

Hello,
in table sales order archive we have a few versions of one sales order (version 1, 2 ,3 …). In filter I need to use the newest version (in case of 5 versions, I need the 5th one). How can I do filter with the highest number?

THX.

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts September 19, 2016 14:29

  I don't use sales orders on my system, but the sales order archive table seems to have a field called "No. of archived versions". Could you use that as a filter? E.g. only return rows where version no. = no. of archived versions.

• 0
  
  Jet Reports Historic Posts September 23, 2016 08:47

  Hello Teresa,
  
  I think you don´t understand me :-(
  
  Look at this formula: =NL("First";"Sales Header Archive;"No. Order";"No.";$K6;"No. version";"The highest number of version /Číslo verze in attachments/).
  
  Thank you.
  
  V.

  • sales archive.png
• 0
  
  Jet Reports Historic Posts September 26, 2016 06:19

  Hi,
  
  Shouldn't a "Last" together with a sort of the archived versions in the formula do the job you need?
  =NL("Last";"Sales Header Archive;"No. Order";"No.";$K6;"+No. of Archived Versions";"*")
  
  Cheers,
  André

• 0
  
  Jet Reports Historic Posts September 26, 2016 06:54

  Apparently not!
  
  This is what I would do:
  
  NL("Rows","Sales Header Archive",,"Version No.","=NF(,""No. of archived versions"")")
  
  That would return just the first row in the test data you gave.

• 0
  
  Jet Reports Historic Posts October 20, 2016 13:30

  HI Both,
  I am trying to do this as well, but I expect I am missing some earlier parts of the report, can you upload the report for me to view?
  I usually use Table builder & this formula I assume wont work with this style of report?
  I am actually trying to produce a report of all Sales Orders below £500 (invoiced), preferably with the sales lines I expect you can used the posted invoices but only if grouped on the Sales Order no (we often part invoice so using a unique invoice no & amount does not work) or I thought it might be easier to use the Sales Order Archive table & use the last version but this is where i got stuck.
  
  Any help appreciated

• 0
  
  Jet Reports Historic Posts May 31, 2017 21:31

  Did you get this to work? I'm facing a similar issue. I want the last version number within a specific date range. So if the order was archived 3 times, on 03/21/2017, 04/13/2017, & 05/05/2017. I want the last one in the date range of 03/01/2017..04/30/2017. For this, I can't use the no. of archived versions.
  
  My formula is as follows, except I can't figure out how to get the version no. I need.
  
  NL("rows","sales line archive",,"document type","<>quote","quantity","<>0","link=","sales header archive","No.","=Document No.","date archived",$D$2,"version no.",????)
  
  Any help would be greatly appreciated!!