0

Retrieving data from multiple companies

Laurentiu Dumitru

September 02, 2021 09:05

None

Follow

Hello everyone,

I've recently started using Jet Reports and I'm really stuck on this matter:

I need to create a report with data from all existing companies that looks like this:

Company_Name |  Column_1 | Column_2 | Column_3 | Column_4

The columns above are from the table "Approval Entry".

I can easily retrieve data from all companies with an NL for every company but unfortunately I'm hardcoding the [Company Name] column.

Is there a nicer way of doing this? I've tried the table builder where I selected the "Approval Entry" and the "Company Information" table but I didn't let me to finish it, the ok button for the table builder was grayed out.

Any help si greatly appreciated!

 

 

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Marc October 12, 2021 14:16

  There are a few methods for returning multiple companies:

  • NL("Rows",NP("Companies"))
  • NL("Rows","Company","Name")

  The benefit of the second one is you can then limit the companies by specific companies. In this example, only companies 123, 456 and 789 will be returned:

  • NL("Rows","Company","Name","Name","123|456|789")

   

  Your Column_ fields would then need to add the extra filter "Company=",[cell number of the NL(Rows)]. Assuming the company name is now in B2, B3 would look like:

  • NL("Rows","Approval Entry","User ID","Company=",B2, and any other filters you have)

   

  You can pull a table (via table builder) for a single company, but you still need to use the repeater (rows, columns, sheets) to generate the company name, and use the "Company=" filter in the NL(Table) function after you've closed the Builder helper. However, one of the filters in the NL(Table) is "TableName=" - if you don't do anything about this, you will get an error when Jet tries to pull the table for the second company. This is because the table name will already exist. To get round this, you can use "TableName=",[cell of the company]&"table name" as shown:

  • NL("Table","Approval Entry",B3:H3,"Headers=",B2:H2,"TableName=",B5&"ApprovalEntry","Company=",B5,other filters

  Hope this helps. Good luck.

• 0
  
  BryanAtTigunia December 16, 2021 18:23

  To add to the conversation above, there are only two functions that support pulling data from multiple companies in one function.  The GL function and NL(Sum) will allow you to specify multiple companies within NAV/BC and GP.