Combine multiple (unrelated) tables

Hi, I'm relatively new to Jet Reporting and have previously only used tables.

I want a report showing rows of:

sales invoice lines
sales cr. memo lines
purchase invoice lines
purchase cr. memo lines

based on a user-defined Global Dimension 1 filter.

I want the resulting report to show the following fields, which are common (albeit slightly differently named) to each table (or their respective header tables):

Posting date,
Document type,
Document no.
Account (either customer or vendor name),
Currency Code,
Currency Factor,
Amount, and
Remaining amount.

I am able to get the information I need using 4 separate NL("Rows" functions on top of each other, however if the filtered dimension doesn't appear in any one of those tables, the report crashes citing: 'empty filter not allowed' (or something similar)

Any help gratefully received.

Thanks!

Joe

Bryan Robinson May 08, 2020 12:32

There are some techniques using Array Calculations and NP Union to combine unrelated tables into one replicator but it often is more work than it is worth since the results are the same as using the technique you mentioned above with 4 replicators. Additionally, if you use the Union technique, the field names would need to be EXACTLY the same in every table. Ultimately, it is more work, not any more efficient and harder to build doing it using NP Union so i wouldn't recommend that technique for this report.

Empty filter not allowed typically means you have a function pointing at a blank cell not the use of multiple stacked rows replicators. I would review this article: https://support.jetglobal.com/hc/en-us/articles/115007349748-Error-The-FilterField-has-an-empty-Filter-argument-

Combine multiple (unrelated) tables

1 comment