Joining AX form datasource with no direct relation through query x++

(0) Share

Report

Posted on by Community Member

Hello,

I am trying to join 2 data sources on a form my modifying the query in the executeQuery() of the joined datasource, but after executing the addLink method, I am recieving an error , "Invalid Field/Related field combination".

I am trying to join WMSBillOFLadingOrder onto CustPackingSlipJourusing with SalesId as the relation.

Here is the code:

public void executeQuery()

{

QueryBuildDataSource cpj;

QueryBuildDataSource bol;

query query;

query = CustPackingSlipJour_ds.query();

cpj = query.dataSourceTable(tableNum(CustPackingSlipJour));

bol = cpj.addDataSource(tableNum(WMSBillOfLadingOrder));

bol.joinMoIde(JoinMode::ExistsJoin);

bol.fetchMode(QueryFetchMode::One2One);

bol.relations(false);

bol.addLink(fieldNum(CustPackingSlipJour, SalesId), fieldNum(WMSBillOfLading, InventTransRefId));

super();

}

I tried the same in a job and it worked:

static void Job21(Args _args)

{

Query query;

QueryRun queryrun;

QueryBuildDataSource cpj;

QueryBuildDataSource bol;

QueryBuildRange qbr1;

CustPackingSlipJour custPackingslipJour;

WMSBillOfLadingOrder wmsBillOfLadingOrder;

;

query = new query();

cpj = query.addDataSource(tablenum(CustPackingSlipJour));

qbr1 = cpj.addRange(fieldnum(CustPackingSlipJour,orderAccount));

qbr1.value(queryvalue('CUST001'));

bol = cpj.addDataSource(tablenum(WMSBillOfLadingOrder));

bol.relations(false);

bol.joinMode(joinmode::InnerJoin);

bol.addLink(fieldnum(CustPackingSlipJour,SalesId),fieldnum(WMSBillOfLadingOrder,InventTransRefId));

queryrun = new queryrun(query);

while(queryrun.next())

{

custPackingslipJour = queryrun.get(tablenum(CustPackingSlipJour));

wmsBillOfLadingOrder = queryrun.get(tablenum(WMSBillOfLadingOrder));

info(strfmt("%1 - %2",custPackingslipJour.salesid, wmsBillOfLadingOrder.billOfLadingId));

}

I'm not sure if I'm missing something. Does anyone have any insight?

Any help would be appreciated.

Thanks,

Charu

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

If the query itself works, the problem must be with something that differs in your code samples - in the query you're modifying in the first example (instead of creating a new one). How does the form query look like?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Martin,

This is what the query looks like just before executing addLink method:

SELECT FIRSTFAST * FROM CustPackingSlipJour(CustPackingSlipJour) WHERE ((OrderAccount = N'CUST20497'))

OUTER JOIN * FROM ShipCarrierTracking(ShipCarrierTracking)

ON CustPackingSlipJour.SalesId = ShipCarrierTracking.SalesId

AND CustPackingSlipJour.PackingSlipId = ShipCarrierTracking.PackingSlipId

JOIN * FROM CustPackingSlipSalesLink(CustPackingSlipSalesLink)

ON CustPackingSlipJour.SalesId = CustPackingSlipSalesLink.salesId

AND CustPackingSlipJour.PackingSlipId = CustPackingSlipSalesLink.packingSlipId

AND CustPackingSlipJour.DeliveryDate = CustPackingSlipSalesLink.deliveryDate

JOIN * FROM WMSBillOfLadingOrder(WMSBillOfLadingOrder_1)

Thanks,

Charu

Was this reply helpful? Yes No
Dick Wenning 8,705 Moderator on at

Like (0)

Report

not Shure, but dynamicsfields = true?

incase you build the query in the AOT, do you get the same?

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Sorry guys, I reallized I had a typo on this line:

bol.addLink(fieldNum(CustPackingSlipJour, SalesId), fieldNum(WMSBillOfLading, InventTransRefId));

I typed WMSBillOfLading instead of WMSBillOfLadingOrder. It works now. But I realized, what I am doing will not help me becasue the WMSBillOfLadingOrder datasource is not getting afftected by the query.

I changed the way the query needs to execute and its all good now.

Thanks!

Was this reply helpful? Yes No
Martin Winkler 119 on at

Like (0)

Report

Same with me, I was pretty desparate until I noticed I was joining fieldNum(ProdTable, ProjId) when actually wanting to join fieldNum(ProjTable, ProjId). And I only noticed after re-checking, having read your last post :-)

So my recommendation, check again for typos.

Was this reply helpful? Yes No