How to display records from different company accounts in one form

One of the previous posts on this blog discussed getting records from different company accounts with one select statement. For this, the crossCompany keyword is used.
Now we'll do something similar, but this time we'll display the records in a form. And the good news is, it's really easy. All ya have to do, is set a property on the datasource of the form.

You can use the crossCompanyAutoQuery property, located on the forms datasource. Standard value is 'No', so set it to 'Yes'.

But what if we want to filter on selected, different company accounts, doing so by code?


We would use the dataAreaId field of the table for this. For example, by adding following line of code in the init method of the datasource, right after the call to super:

this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,dataAreaId)).value('CA1');


If we open the form, as a result, we get an error though:


Filtering at the data source level is not allowed for a cross-company query.

Mmm, inconvenient.

Any workaround? Must be, as you can filter by the dataareaid field manually in the form.

Here is a short piece of code that does the trick:

   Query myQuery;
   ;

   myQuery=CustTable_ds.query();
   myQuery.addCompanyRange('CA1');
   myQuery.addCompanyRange('CA2');

   CustTable_ds.executeQuery();

Place the code displayed above right after the super call in the run method of your form for example, or call it by pressing a button. (Just make sure you use executeQuery after setting the ranges.)

As you see from the code sample, we're using addCompanyRange to set the required company accounts as filter in our query. You can call it multiple times, they are accumulated as OR statements.