0

Table links

Malcolm Clarke

May 24, 2019 13:35

None

Follow

Hi,

I am very new to Jet Reports, and really struggling to understand how to link tables, or use formula and so on.

My scenario is the following;

We need a report that shows some Customer information, with the ship-to addresses, as well as the dimensions for these customers. I want to display the salesperson name from the customer, so need to add the salepeople table, and then to show two of the default dimensions.

I also need to see all the ship-to addresses assigned to the customer. I have tried this using the Customer table as the base table, and using the Ship-to table as the base, but I just cannot get the report to show me the value of dimension 1 and then of dimension 2, it picks the first default dimension only and that is that.

I have also created a table that links the item to the item category, which is linked to the product group table, which again brings in the correct code, but when I want to show the description of the product group, it only picks the first one for all codes. I just cannot figure this out.

Thanks,

Malcolm

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Harry Lewis May 28, 2019 14:12

  Hello Malcolm -

   

  When using NL(Table) or the Table Builder, the resulting table will show (at most) the number of records present in the first table you use.

  So, if you start with the customer table and you have 100 customers, the Excel table will have (at most) 100 rows (regardless of how much data you attempt to include through additional database tables).

  The first concept is to start with a transaction-type table (which contains multiple records with varying information) and then add in header-type tables.  This may (depending upon your filtering) result in more data than you are looking for, but you can then use Excel's pivot capabilities to display summarized information.

  Alternately, you can create a Grouping Report.  Anyone who has read my responses on this site know that I recommend these quite often.  The reason for that is that it is such a powerful method.  In this way, you could start by listing your Customers and then nest additional NL(Rows) functions to include whatever data you want from other related tables.