Table Builder

Discussion & Feedback for Jet Enterprise
a1becker
Posts: 13
Joined: Fri Nov 09, 2012 9:40 am

Table Builder

Postby a1becker » Wed Dec 21, 2016 9:37 am

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

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 442
Joined: Wed May 26, 2010 3:40 pm

Re: Table Builder

Postby HPDeskJet » Wed Dec 21, 2016 11:48 am

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:
three_tables_0.png
three_tables_0.png (5.77 KiB) Viewed 289 times
(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.
three_tables_1.png
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")
three_tables_2.png
three_tables_2.png (14.15 KiB) Viewed 289 times
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.
three_tables_3.png
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:
three_tables_4.png
three_tables_4.png (6.57 KiB) Viewed 289 times
Excel was able to AUTO-DETECT this:
three_tables_5.png
three_tables_5.png (6.27 KiB) Viewed 289 times
and the correct data was displayed in the pivot table.

I hope that helps.
-HP
_______________________
Jet 2015 Certified Trainer


Return to “How do I?”

Who is online

Users browsing this forum: No registered users and 2 guests