0

Table Design. Combine Multiple Source tables

Jet Reports Historic Posts

August 29, 2014 13:59

None

Follow

Hi,

I have an issue, which I have been working on for a while.

I need in many cases to combine Sales Invoice Line and Sales Cr.memo Line into one table, because I need information from the lines of both tables. I know that with a NL("Rows";…..) function and a NP("Union"…) I can get the result, but as the 2 source tables give me several thousand lines, the report will be extremely slow.

That's why I need to either combine the 2 tables or get a workaround in Pivot to combine the 2 tables. I have tried Excels Pivottables and Pivotdiagram wizard, but without any luck.

Therefore, does anyone have any experience in combining these two tables in a Table Query or have any luck combining them in a pivot (without the Headers from the last table and with dates that Work) ?

I attach a simplicity report of the two tables, very very simple report with only Posting date, Document no. and Amount from each table, but I cannot get the result combined.

Any suggestions are highly appreciated :)

Thanks.

Allan

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts August 29, 2014 18:17

  I don't know of a way in Essentials outside of the way you mentioned to create the table first and then create your pivot table.
  
  I know using Jet Enterprise, you could pull data straight into a pivot table.
  
  I also noticed that you mentioned attaching a simple report but there was nothing attached to your post.
  
  Would be interested to see what you currently have.

• 0
  
  Jet Reports Historic Posts September 01, 2014 13:16

  Hi,
  
  :roll: Thought I did attach it :-)
  
  I try again. This a pretty simple example, it is not taken from a finished report, it is just being build to the occasion
  
  I only have Jet Essentials for this, no Enterprise.
  
  Thanks in advance.
  
  Allan

  • Table - Sales Invoice Line vs. Sales Cr.memo Line.xlsx
• 0
  
  Jet Reports Historic Posts September 16, 2014 17:26

  Hello. Were you able to find solution to your issue?

• 0
  
  Jet Reports Historic Posts September 16, 2014 18:38

  No, sorry, that is still an unsolved issue for me.

• 0
  
  Jet Reports Historic Posts September 17, 2014 08:27

  Hi,
  why do you want to use invoice and cr.memo lines?
  Isn't it possible to get the results in your example by using customer ledger entries?
  
  regards
  jetsetter

• 0
  
  Jet Reports Historic Posts September 25, 2014 14:35

  Hi,
  
  I need (among other fields) quantity and amount from the sales invoice lines and the credit memo lines combined into for a pivot, and I need to specify it per line in the pivot. And there is a lot of data, so it need to be done in a table.
  
  I agree, if it was the sum of lines, I could use the customer ledger entry. But unfortunately I need details from every line from both tables (invoice and credit memo).
  
  So any ideas are more than welcome :)
  otherwise I must realize that it is probably not possible.
  
  Regards,
  Allan