Joining two non related datasources at runtime

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi,

I have a scenario where I need to join two tables in a form, they have two common fields invoice and purchid. I do not wish to create relation at table level, as this form information will serve only information purpose. I want to display two fields from Tab2 based on the values present in Tab1 invoice and purchid. All the records from Tab1 should be fetched and those two fields with datasource Tab2 should be populated in a grid whose datasource in Tab1. (i.e. Tab1 should be joined with Tab2 with an outer join)

Say those tables are Tab1 and Tab2 and I tried the following code

Tab1 linkActive()

{

(Query) q = Tab1_ds.query();

qbdsTab1 = q.datasourceTable(tablenum(Tab1));

qbdsTab2 = qbdsTab1.addDatasourceTable(tablenum(Tab2));

qbdsTab2.addLink(fieldnum(Tab1, invoice), fieldnum(Tab2, invoice));

qbdsTab2.addLink(fieldnum(Tab1, purchid), fieldnum(Tab2, purchid));

qbdsTab2.joinMode(JoinMode::OuterJoin);

qbdsTab2.relations(false);

}

This code is not working as the query for Tab2 does not get assigned to the relative datasource.

How could I achieve the above scenario?

I am working on Ax 2009.

Regards,

Abhinay

2

I wonder if your code even compile, because QueryBuildDataSource class doesn’t seem to have any method called addDatasourceTable().

Check this example (using standard AX tables, so everybody can copy it and run):

Query q = new Query();
QueryBuildDataSource ds1, ds2;
ds1 = q.addDataSource(tablenum(VendInvoiceInfoTable));
    
ds2 = ds1.addDatasource(tablenum(VendInvoiceInfoLine));
ds2.relations(false);
ds2.addLink(fieldnum(VendInvoiceInfoTable, ParmId),
            fieldnum(VendInvoiceInfoLine, ParmId));
ds2.addLink(fieldnum(VendInvoiceInfoTable, TableRefId),
            fieldnum(VendInvoiceInfoLine, TableRefId));
ds2.joinMode(JoinMode::OuterJoin);
    
info(ds1.toString());
3

Hi Martin,

My bad, I should have specified that in the original post itself that it is a pseudo code as I did not have access to the Ax system.

the method must be addDatasource.

Thanks,

Abhinay