Cross Company-(Reports loading takes to long)

Hi guys,

My reports loading it takes 4 minutes to load, when i put this code:

 while   select crossCompany ShipCarrierDeliveryContact, dataAreaId from salesTable
          order by dataAreaId desc
                    {
                 changeCompany(salesTable.dataAreaId)
                    {
                       Remarks =  SalesTable::find(salesLine.SalesId).ShipCarrierDeliveryContact;
                    }
                }

It gives me a correct output, but i need to wait 4 minutes to load the remotes.

can anyone rephrase my code?

TIA

Your code is very inefficient and for no reason. You make a new request to database for every sales order in your system, which is just crazy. And all that just to get ShipCarrierDeliveryContact of the single last order.

The equivalent of your code is this:

select firstOnly crossCompany ShipCarrierDeliveryContact from salesTable
    order by ...; // You need order by, otherwise you can get unpredictable results!
remark = salesTable.ShipCarrierDeliveryContact;

Nevetheless it doesn’t make much sense without any filter (WHERE clause).

If you wanted to get all contacts, it would look like this:

while select crossCompany ShipCarrierDeliveryContact from salesTable
{
    contacts.add(salesTable.ShipCarrierDeliveryContact);
}

This is just a single request to database. If you have fifty thousand orders, the code becomes roughly fifty thousand times more efficient.

Hi Martin,

Now i clearly understand.

in this code:

order by dataAreaId desc
ChangeCompany(salesTable.dataAreaId)
{
Remarks = SalesTable::find(salesLine.SalesId).ShipCarrierDeliveryContact;
}

i rephrase my code to this:

while select crossCompany ShipCarrierDeliveryContact from salesTable
where salesTable.InterCompanyOrigin == 0
&& salesTable.SalesId == salesLine.SalesId
{
Remarks = salesTable.ShipCarrierDeliveryContact; 
}


Thank you very much Martin it helps me a lot. :slight_smile:

No, your code still isn’t correct. Why do you use while select at all if you can’t get more than a single contact? Get rid of the while select.

And why do you query across all companies, if you already have a sales line and therefore you know in which company it is? And what if the same sales ID exists in several companies? If the order always is in the current company, remove crossCompany keyword. If it can be in a different company, filter by the company (which you’ll find in salesLine.DataAreaId).

actually martin i already did what you say, before i use Crosscompany, i tried first to filter but the data show’s nothing.
then i discovered crosscompany and study it, to know how it works… and also it is impossible to have same salesid …
Ex: INF-SO-… and WIZ-SO-…