Friday, January 7, 2011

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.

6 comments:

  1. Well, well. But say, the user has to decide to select the companies with a QueryRange like 'CA?'. Is this possible too?

    ReplyDelete
  2. AFAIK: You cannot use wildcards with addCompanyRange. But you could do something like this:

    while select DataArea
    where DataArea.id like 'CA?'
    {
    this.query().addCompanyRange(DataArea.id);
    }
    }

    ReplyDelete
  3. hi,
    plz smeone help me...its urgent..my query is how can we block replication among countries in case of fiscal calendars?
    any immediate rply ll be appreciated.plz
    Thanks,

    ReplyDelete
  4. Thank you for sharing real records. it's miles a exquisite informative publish. your article is absolutely too nicely.
    Maintain posting those articles continuously.
    for more information please check the site
    oracle fusion procurement online training
    oracle fusion procurement training

    ReplyDelete
  5. I need to upload citibank bankstatements in ax 2009 if any one can help me

    ReplyDelete