0

Adding Sales Line Archive

Jet Reports Historic Posts

March 05, 2012 20:37

None

Follow

Hi Experts,

I want to add a field [Line Amount] in the table [Sales Line Archive] only for records in the table [Sales Header Archive], which have the highest version with an order date in a date range.
There are some ideas from ElenaNAV in June or July 2011, but this don´t work, I thing.
to solve the problem has cost me a lot of time. So I must ask the experts in the Jet community.

Thanks for your efforts.

Frank

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts March 05, 2012 22:40

  Hi Frank,
  
  Just to be clear, when you say you want to "add a field", do you mean you want to retrieve the values of the field in Jet Reports? Or do you mean you actually want to add a new field in Navision? I'm not sure I understand exactly what you are trying to do. Can you provide some sample data for the 2 tables and what it is you are trying to retrieve?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 06, 2012 21:04

  Sales Header Archive
  No. Order date Version
  99998 05.01.2012 1
  99998 10.01.2012 2
  99999 01.01.2012 1
  99999 11.01.2012 2
  99999 12.01.2012 3
  
  Sales Line Archive
  Document Type Document No. Line No. Version No. Line Amount
  Order 99998 10000 1 327,80
  Order 99998 20000 1 232,00
  Order 99998 10000 2 380,80
  Order 99998 20000 2 245,00
  Order 99999 10000 1 80,00
  Order 99999 20000 1 124,80
  Order 99999 10000 2 95,20
  Order 99999 20000 2 150,00
  Order 99999 10000 3 120,00
  Order 99999 20000 3 200,00
  
  I want to sum f. e. the field Line Amount for all archived Orders with the highest version, where the order date is in a date range:
  Date filter: 100112..110112
  
  Headers
  Order 99998 10.01.2012 2
  Order 99999 11.01.2012 2
  
  Lines
  Order 99998 10000 2 380,80
  Order 99998 20000 2 245,00
  Order 99999 10000 2 95,20
  Order 99999 20000 2 150,00
  
  The result of the NL("Sum") Funktion has to be 871,00
• 0
  
  Jet Reports Historic Posts March 07, 2012 13:06

  NL( "Sum", "Sales Line Archive", "Line Amount", "Link=Sales Line Archive", "Sales Header Archive"; "Document Type"; "=Document Type"; "No."; "=Document No."; "Document Date"; "01-01-2011..31-12-2011"; "Link=Sales Line Archive"; "Sales Header"; "Document Type"; "=Document Type"; "No."; "=Document No."; "No. of Archived Versions"; "=Version No." )

Give me a sum of the "Sales Line Archive" "Line Amount" NL( "Sum", "Sales Line Archive", "Line Amount",
  Link the "Sales Header Archive" with the "Sales Line Archive"
  "Link=Sales Line Archive", "Sales Header Archive";
  Only the "Sales Line Archive" where "Sales Header Archive" "Document Date" in range
  "Document Type"; "=Document Type"; "No."; "=Document No."; "Document Date"; "01-01-2011..31-12-2011";
  Link the "Sales Header" with the "Sales Line Archive"
  "Link=Sales Line Archive"; "Sales Header";
  Only the "Sales Line Archive" where the "Sales Header" "No. of Archived Versions" matches.
  "Document Type"; "=Document Type"; "No."; "=Document No."; "No. of Archived Versions"; "=Version No."
• 0
  
  Jet Reports Historic Posts March 07, 2012 15:28

  Hallo Sebastiaan,
  
  thanks for your solution. But what is, when there is no sales Header anymore, because the Sales Order is deleted oder invoiced?
  
  Frank

• 0
  
  Jet Reports Historic Posts March 13, 2012 19:28

  Is there anybody who has a solution for my problem?

• 0
  
  Jet Reports Historic Posts March 14, 2012 08:01

  Hi Mielcke,
  I solved the problem by adding a flag ("Last Version") with the "Sales Archive Header" and "Sales Archive Lines tables" :D
  And also added a short code that will add a new archive every time the status of the Sales Order changes.
  
  Elena