Ledger Entry Report for all Companies

Business Central/NAV Developers Forum
1

Hi Everybody!!!
I have a requirement of creating a report which will show all the ledger entries of a Customer/Vendor throughout all companies in the database if the Customer/Vendor is present in other companies as well. I know the “CHANGECOMPANY” function to use in this case. But can not understand that how I can run that dynamically for all companies present in the database. I have taken two dataitems i.e Customer & Cust. Ledger Entry. Any help will be much appreciated. Thanks in advance!!!

Regards,
Anirban

2

Hi. Presume you mean in a report. Also presume you have set up the consolidated reporting business / company with the appropriate business units. If so all you are doing is then using ChangeCompany as the appropriate point in the report.
Neil

3

Hi Neil!!
Yes,it’s a report only in 2013 R2 version. There is no other setup done for consolidation. It’s something like that ‘Cust0001’ is present in both ‘Company A’ and ‘Company B’. In this situation, if I run the report for ‘Cust0001’ then it should display all the transaction of ‘Cust0001’ from both ‘Company A’ and ‘Company B’. Now, as in the required DB there are more than 10 companies, and more can be added later, so I need to use the ‘CHANGECOMPANY’ function dynamically for all companies. That is where I got stuck. :frowning:

4

Hi Anirban
So you are running this from one of the companies on the Nav database which then runns through the customer list and collects data for the current company and then other companies on the same database? So it does this from the customer list on the company logged into?
In which case all you do is set up a global like Cust (which is table 18), get the data for the company logged into, add code Cust.CHANGECOMPANY(NextCompanyName), get data, CHANGECOMPANY again to the next company name and then when finished CHANGECOMPANY back to the original logged into company.
Better to do this from a new company which has the common customer list in it and all the companies listed as business units and you can then create a report with Business Unit as the first dataitem with the customer table indented as the 2nd dataitem. Then CHANGECOMPANY OnAfterGetRecord on business unit dataitem.
Make sense? Apologies bit of a techie answer.
Neil

5

Hi Neil,

You got the situation absolutely correct. The solution you have iven, I will try and give you feeddback whether I am able to or not. But, the problem is, how can I run the loop for a customer exactly as many times as many companies are there and the no of companies also to be taken dynamically. Thanks!!!

Rgds,
AB

6

Hi Anirban,

Assuming that all possible customers are present in the company from which you are running the report, then you could do something like this.

Personally what I like to do when creating reports like this, is to start by adding the data I need to a buffer table (a temporary table is free in the sense of object numbers 50000-99999 and is not required in the license). You would then add the fields to the buffer table, which you need in your report and so working with the data in the report designer is also much easier.

If the customer ledger entry table is where you have most of the fields, then you could make a short cut, by creating a copy of this table and remove the fields you do not need. Add the company name as a field to this table also.

Cust.RESET;
IF Cust.FINDSET THEN
  REPEAT
    Company.RESET;
    Company.FINDSET;
    REPEAT
      CustLedgEntry.CHANGECOMPANY(Company.Name);
      CustLedgEntry.SETRANGE("Customer No.",Cust."No.");
      IF CustLedgEntry.FINDSET THEN
        REPEAT
          CopyToBuffer(CustLedgEntry,Company.Name);
        UNTIL CustLedgEntry.NEXT = 0;
    UNTIL Company.NEXT = 0;
  UNTIL Cust.NEXT = 0;