This is probably very easy but I cannot figure it out. I just want to combine two tables in the table builder. I want to list all of the transactions for a specific time period in the Sales Invoice Header and the Sales Cr.Memo Header. I need these to be a table so I can put the info into a pivot table.
Any help is GREATLY appreciated
1 comment
-
Jet Reports Historic Posts Hi -
Here is a simple example that I put together.
I used three NL(Table) functions to list my Customer, Sales Invoice Header, and Sales Cr.Memo Header tables:
(I included the Customer table as… let's call it the "Key" table… so that I would have a field with unique values - something that we'll need in a moment)
Next, I ensure that I have selected a cell in my "Key" table (in this case, Customer) and then click the PivotTable button on the Insert ribbon.
The table name should match the name I supplied in my NL(Table) function:
=NL("Table","Customer",$K$6:$L$6,"Headers=",$K$5:$L$5,"TableName=","Customer","IncludeDuplicates=","True")
and I want to ensure that I check the box for Add this data to the Data Model.
In the PivotTable Fields window, click All
You will see all the available tables and field.
I added the customer number and name. When I added the Amount field (from either the Sales_Invoice_Header or Sales_CR._Memo_Header), I was asked about defining the relationship between the tables:
Excel was able to AUTO-DETECT this:
and the correct data was displayed in the pivot table.
I hope that helps.