0

NL(table) - multiple results from linked table

Rozemarijn Verbruggen

February 18, 2019 15:49

Answered

Follow

Hi everyone

I currently have a report with an NL(rows=2) function on one line getting a list of article numbers invoiced during a certain period of time. The results for this function are copied to the next row and are combined with an NL(rows) function getting a certain recycling tax family (with one or more subtypes) from a linked table. I need to show the different subtypes within the chosen recycling tax family for each article number returned by the first NL(rows=2) function. I then hide the first line because it doesn't contain all the info I need and just clutters my view.

The problem is that a pivot table, created straight from the report data, takes the hidden lines into account cluttering the pivot table. The second problem is that the pivot table needs to be rebuilt every time the report runs.

To make this report more user-friendly for my less Excel-savvy colleagues, I want to use the NL(table) function to build a table and make a pivot table based on said table that will refresh every time the report runs. I'm using the Sales Invoice Line table to filter for articles sold during a certain period of time (user-defined), and I've linked it to the Surcharge Item-Resource table to filter for a the tax family I want. The problem here is that the report only returns one line with the first tax subtype it encounters in the Surcharge Item-Resource table, even when there are more lines for this article with the same tax family.
Basically, if I have an article with tax family 1, tax subtype 1.1 AND tax subtype 1.2 that has been sold twice, I'm getting the following result:

1. article + invoice 1 + tax subtype 1.1
2. article + invoice 2 + tax subtype 1.1

 

I actually want the following result:

1. article + invoice 1 + tax subtype 1.1
2. article + invoice 1 + tax subtype 1.2
3. article + invoice 2 + tax subtype 1.1
4. article + invoice 2 + tax subtype 1.2

 

Is there a way to get all tax subtypes for a certain article number from the Surcharge Item-Resource table with the NL(table) function?

Thanks!

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Harry Lewis February 19, 2019 18:55

  Hello Rozemarijn -

  I tried to relate your query to one I could show using a Cronus database.

  It's not a perfect example, but hopefully it illustrates the concept.

   

  I want a list of all LOCATION CODES (from my Item Ledger Entry table) associated with the NO. for each item sold in a user-defined time frame.

  I'll get the NO. field from the Item table and I'll use the POSTING DATE field in the Sales Invoice Line table.

  (it could be done without the Item table, too).

  Since I'm going to want every applicable LOCATION CODE, I have to start with that as the main table in the Table Builder (if, for example, I started with the Item table, it only has one record per item, so my NL(Table) would only have 1 row per item number... that's not what I want).

  Here is what my Table Builder looked like:

  

  When I run the report, my table will have a lot of duplicates (I'm not filtering the data by anything other than date... and there are a lot of records where item X is sourced out of Location Y).  That may be what I want, because I may want some other value included in each of those records.  If I don't want those duplicates, I can turn them off (more on that in a moment).

  So, when I run the report, I get something like this:

  

  and I can then click anywhere in the resulting table and choose Pivot Table from the Insert ribbon and create my pivot table:

  

   

  Note:  If I don't want the duplicate values, I can set the "IncludeDuplicates=" filter in the NL(Table) function to "False":

  

  and only have unique values in my table:

  

   

  I hope that helps get you pointed in the right direction.

   

• 0
  
  Rozemarijn Verbruggen February 20, 2019 16:12

  Hello Harry

  I switched to the Surcharge Item-Resource table as the base table and linked the Sales Invoice Line table, according to your example. This gave me the lines I needed, but did not completely solve my problem. I also needed quantities, and I only got the first line from my Sales Invoice Line table.

  Until I noticed I had chosen the quantity column from the "Fields" part of the table builder. Once I switched to the quantity column from the "Sum Fields" part, I got the results I needed.

  Thanks for pointing me in the right direction!