Hi,
I have linked VendTrans with VendInvoiceInfoTable on VendTrans form based on fields invoice number and purchase id present in both the tables as follows which is called in init of VendTrans and VendInvoiceInfoTable:
public void joinVendTransVendInvoiceInfo()
{
queryJoinVendInfo = new query();
qbdVendTrans = queryJoinVendInfo.addDataSource(tablenum(VendTrans));
qbdVendInfoTab = qbdVendTrans.addDataSource(tablenum(VendInvoiceInfoTable));
qbdVendInfoTab.relations(false);
qbdVendInfoTab.clearLinks();
qbdVendInfoTab.addLink(fieldnum(VendTrans, Invoice), fieldnum(VendInvoiceInfoTable, num));
qbdVendInfoTab.addLink(fieldnum(VendTrans, MxmPurchID), fieldnum(VendInvoiceInfoTable, PurchId));
qbdVendInfoTab.joinMode(JoinMode::OuterJoin);
VendTrans_ds.query(queryJoinVendInfo);
}
Now I wish to add a filter on a column reason code present in VendInvoiceInfoTable, for that I wrote following code in filter combo’s selection change then VendTrans_executeQuery
int selectionChange()
{
int ret;
ret = super();
VendTrans_ds.executeQuery();
return ret;
}
switch (ReasonCodeGroup.valueStr())
{
case enum2str(ReasonCodeGroup::All):
if(this.queryRun())
this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)).
clearRange(fieldnum(VendInvoiceInfoTable, FailReasonCode));
else
this.query().dataSourceTable(tableNum(VendInvoiceInfoTable)).
clearRange(fieldnum(VendInvoiceInfoTable, FailReasonCode));
SysQuery::updateJoinMode(this.queryRun() ?
this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)) :
this.query().dataSourceTable(tableNum(VendInvoiceInfoTable)));
break;
case enum2str(ReasonCodeGroup::Failed):
this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)).
clearRange(fieldnum(VendInvoiceInfoTable, FailReasonCode));
this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)).
addRange(fieldnum(VendInvoiceInfoTable, FailReasonCode)).value(SysQuery::valueNotEmptyString());
SysQuery::updateJoinMode(this.queryRun() ?
this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)) :
this.query().dataSourceTable(tableNum(VendInvoiceInfoTable)));
break;
case enum2str(ReasonCodeGroup::Passed):
this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)).
clearRange(fieldnum(VendInvoiceInfoTable, FailReasonCode));
this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)).
addRange(fieldnum(VendInvoiceInfoTable, FailReasonCode)).value(SysQuery::valueEmptyString());
SysQuery::updateJoinMode(this.queryRun() ?
this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)) :
this.query().dataSourceTable(tableNum(VendInvoiceInfoTable)));
break;
}
and following in SysQuery class method
static void updateJoinMode(QueryBuildDataSource qds)
{
Counter r;
if (qds)
{
qds.joinMode(JoinMode::OuterJoin);
for (r = 1; r <= qds.rangeCount(); r++)
{
if (qds.range(r).value() && qds.range(r).status() == RangeStatus::Open)
{
qds.joinMode(JoinMode::InnerJoin);
break;
}
}
}
}
I have been trying to filter the column based on this code, but for some reason it is just not working, what could be the reason?
Regards,
Abhinay
Your code handles several different cases. Which one do you test? And can you use
tag to format code to make it more readable?
By the way, your code is by much more complex then it needs to be. For example, save result of this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable)) to a variable instead of copying it to many places. It’s not bad just for readability, but you will also have to update all occurrences if you want to change anything. Instead of clearing and recreating ranges, use SysQuery::findOrCreateRange(). And I would find a few more other things. Trust me, simpler code is easier to debug.
Thanks Martin.
Yes, I was searching for the tag to format code to make it more readable here. I will make the changes suggested by you to the code and post it here with it’s sequence, thank you once again!
public void joinVendTransVendInvoiceInfo()
{
queryJoinVendInfo = new query();
qbdVendTrans = queryJoinVendInfo.addDataSource(tablenum(VendTrans));
qbdVendInfoTab = qbdVendTrans.addDataSource(tablenum(VendInvoiceInfoTable));
qbdVendInfoTab.relations(false);
qbdVendInfoTab.clearLinks();
qbdVendInfoTab.addLink(fieldnum(VendTrans, Invoice), fieldnum(VendInvoiceInfoTable, num));
qbdVendInfoTab.addLink(fieldnum(VendTrans, PurchID), fieldnum(VendInvoiceInfoTable, PurchId));
qbdVendInfoTab.joinMode(JoinMode::OuterJoin);
VendTrans_ds.query(queryJoinVendInfo);
}
this code is called in the init methods of VendTrans and VendInvoiceInfoTable datasources
if(this.queryRun())
qbds = this.queryRun().query().dataSourceTable(tableNum(VendInvoiceInfoTable));
else
qbds = this.query().dataSourceTable(tableNum(VendInvoiceInfoTable));
qbr = SysQuery::findOrCreateRange(qbds, fieldnum(VendInvoiceInfoTable, FailReasonCode));
switch (ReasonCodeGroup.valueStr())
{
case enum2str(ReasonCodeGroup::All):
qbds.clearRange(fieldnum(VendInvoiceInfoTable, FailReasonCode));
SysQuery::updateJoinMode(qbds);
break;
case enum2str(ReasonCodeGroup::Failed):
qbr.value(SysQuery::valueNotEmptyString());
SysQuery::updateJoinMode(qbds);
break;
case enum2str(ReasonCodeGroup::Passed):
qbr.value(SysQuery::valueEmptyString());
SysQuery::updateJoinMode(qbds);
break;
}
this code is in the executeQuery of the VendTrans datasource and is expected to change the JoinMode at run time to inner and filter
data in VendInvoiceInfoTable if the filter enum value selected is failed or passed, else keep the JoinMode as Outer and remove
filter from the FailReasonCode column.
static void updateJoinMode(QueryBuildDataSource qds)
{
Counter r;
if (qds)
{
qds.joinMode(JoinMode::OuterJoin);
for (r = 1; r <= qds.rangeCount(); r++)
{
if (qds.range(r).value() && qds.range(r).status() == RangeStatus::Open)
{
qds.joinMode(JoinMode::InnerJoin);
break;
}
}
}
}
Thanks. Nevertheless you merely described what it should do, now tell us what’s the problem. What’s wrong with the SQL query generated by your definition? And again, which one of those three case are you talking about?
By the way, what’s the purpose of changing join mode?
Hi,
1452.TestData.xlsx (8.48 KB)
in the above given link, which has sample data, the invoice number and purch id are common in both the datasources. Initially all the records from VendTrans will be displayed on form run, irrespective of whether the invoice number and purch id exist in VendInvoiceInfoTable, but value in reason code will be fetched based on the outerjoin.
Now, I have to add a filter on ReasonCode as All, Failed (non empty) and Passed (Empty). I have created an enum and added that as a filter to the form. If the selection changes to All, join mode will be outer and both non-empty and empty records will fetch, if selection is Failed only those VendTrans will be selected whose invoice and purch id exists in the joined table and the join table has non-empty reasoncode column, vice-versa for Passed selection.
To achieve this I have to keep changing the joinmodes and add/ remove ranges
And what the problem is? So far you told us that “it is just not working”, which is really vague.
It does not filter the records, but fetches all the VendTrans records irrespective of the value selected in the filter. No error message, but code does not filter. The grid shows all the records.
I still don’t know even which of those three cases you’re testing. You know what? Look at the result SQL query (qbdVendTrans.toString()) and if you’re unable to analyze it by yourself, post it here and tell us what do you expect to see.
Trying to debug a SQL query just by looking only at X++ code and returned data is arguably difficult. Don’t make your life harder than it needs to be.
Also tell us which version of AX you’re using.
Form init:
SELECT FIRSTFAST * FROM VendTrans OUTER JOIN FIRSTFAST * FROM VendInvoiceInfoTable WHERE VendTrans.Invoice = VendInvoiceInfoTable.Num AND VendTrans.MxmPurchId = VendInvoiceInfoTable.PurchId
Filter: Failed:
SELECT FIRSTFAST * FROM VendTrans JOIN FIRSTFAST * FROM VendInvoiceInfoTable WHERE VendTrans.Invoice = VendInvoiceInfoTable.Num AND VendTrans.MxmPurchId = VendInvoiceInfoTable.PurchId AND ((NOT (MxmFailReasonCode = ' ')))
Filter: Passed:
SELECT FIRSTFAST * FROM VendTrans JOIN FIRSTFAST * FROM VendInvoiceInfoTable WHERE VendTrans.Invoice = VendInvoiceInfoTable.Num AND VendTrans.MxmPurchId = VendInvoiceInfoTable.PurchId AND ((MxmFailReasonCode = ' '))
And what do you expect to see? It looks all right to me. If it’s correct, maybe your problem doesn’t lie in the query at all. Test the query alone, without the form, to see whether it’s the case or not.
I have tested the query, which is absolutely fine, but the resultset on the grid, for some reason, does not change at all. That has been the point of my confusion.
In that case, the question of this thread, “Add range at runtime to linked datasources”, is resolved. You know how to add ranges and you get a query which is, in your own words, absolutely fine.
Unfortunately this thread, including the code you showed and the whole discussion, is not irrelevant to your real problem, which lies somewhere in how you use the query. For instance, it’s not clear where you execute the query after changing it.
Hi,
I added the following code and it worked!!!
qr = new QueryRun(query);
VendTrans_DS.query(qr.query());
VendTrans_DS.executeQuery();
Thanks you so much!!!
Hope this helps the others who are seeking help.
Take care