Share
ReportHello,
I'm working on a custom lookup in AX 2012 R2 and I'm seeing the strangest behavior in the order by clause that is generated by SQL. I'm wondering if anyone can point me to where I'm going wrong. The lookup is presenting a list of branches from native a AX table OMOperatingUnit. I want my lookup to be sorted by the branch number (OMOperatingUnitNumber field) and also include the branch name. With only the OMOperatingUnit table included in the lookup's data sources, this part is working just fine:
public static Common lookupBranchBudgetByUser(FormReferenceControl _formReferenceControl)
{
Query query = new Query();
QueryBuildDataSource qbdsOMOperatingUnitNumber, qbdsBranchBudgetBranchUserFilterView;
// create the lookup and add the lookup fields
SysReferenceTableLookup sysTableLookup = SysReferenceTableLookup::newParameters(tableNum(OMOperatingUnit), _formReferenceControl, true);
sysTableLookup.addLookupfield(fieldNum(OMOperatingUnit, OMOperatingUnitNumber));
sysTableLookup.addLookupfield(fieldNum(OMOperatingUnit, Name));
sysTableLookup.addSelectionField(fieldNum(OMOperatingUnit, RecId));
// add the OMOperatingUnit data source
qbdsOMOperatingUnitNumber = query.AddDataSource(tableNum(OMOperatingUnit));
// order lookup by branch number
qbdsOMOperatingUnitNumber.addOrderByField(fieldNum(OMOperatingUnit, OMOperatingUnitNumber));
// group by branch so we only present the distinct list of branches in the list
qbdsOMOperatingUnitNumber.addGroupByField(fieldNum(OMOperatingUnit, OMOperatingUnitNumber));
qbdsOMOperatingUnitNumber.addGroupByField(fieldNum(OMOperatingUnit, Name));
qbdsOMOperatingUnitNumber.addGroupByField(fieldNum(OMOperatingUnit, RecId));
sysTableLookup.parmQuery(query);
return sysTableLookup.performFormLookup();
}
The above section works just fine for presenting the lookup and sorting them by branch. However, for this use case, only certain users should have access to budget certain branches. I have a set of tables which specify which user has access to which branch and I've created a view for these tables so I can filter the branch list on my view. The filtering is working great but it somehow throws my ordering off. Here's the code that I've added prior to the return to do the filtering:
// join to the branch budget user filter view to filter only the branches that the user has access to budget
qbdsBranchBudgetBranchUserFilterView = qbdsOMOperatingUnitNumber.addDataSource(tableNum(BranchBudgetBranchUserFilterView));
qbdsBranchBudgetBranchUserFilterView.addLink(fieldNum(OMOperatingUnit, RecId), fieldNum(BranchBudgetBranchUserFilterView, OMOperatingUnitRecId));
// filter current user
qbdsBranchBudgetBranchUserFilterView.addRange(fieldNum(BranchBudgetBranchUserFilterView, User)).value(curUserId());
// filter valid attribute assignment for the current date
qbdsBranchBudgetBranchUserFilterView.addRange(fieldNum(BranchBudgetBranchUserFilterView, BranchAttributeRelTableValidFrom)).value(queryRange(null, today(), true));
qbdsBranchBudgetBranchUserFilterView.addRange(fieldNum(BranchBudgetBranchUserFilterView, BranchAttributeRelTableValidTo)).value(queryRange(today(), null, true));
Once I add this join to filter out the branches that a particular user shouldn't see in the lookup, if i click the lookup I see the list of branches are now sorted by RecId instead of the OMOperatingUnitNumber. I ran a trace and checked the query in the trace parser and found this query:
SELECT T1.NAME, T1.RECID, T1.OMOPERATINGUNITNUMBER, T1.RECID
FROM DIRPARTYTABLE T1
CROSS JOIN BRANCHBUDGETBRANCHUSERFILTERVIEW T2
WHERE (((T1.PARTITION=?)
AND ((T1.OMOPERATINGUNITTYPE=?)
AND (T1.RECID>=?)))
AND (T1.INSTANCERELATIONTYPE IN (2377) ))
AND ((((((T2.PARTITION=?)
AND (T2.PARTITION#2=?))
AND (T2.PARTITION#3=?))
AND (T2.PARTITION#4=?))
AND (T2.PARTITION#5=?))
AND ((((T2.USER_=?)
AND (T2.BRANCHATTRIBUTERELTABLEVALIDFROM<=?))
AND (T2.BRANCHATTRIBUTERELTABLEVALIDTO>=?))
AND (T1.RECID=T2.OMOPERATINGUNITRECID)))
GROUP BY T1.OMOPERATINGUNITNUMBER, T1.NAME, T1.RECID
ORDER BY T1.RECID
Now the strangest thing in this, if I type * into the field to open the lookup, the values are sorted properly and the query i see in the trace parser is this:
SELECT T1.NAME, T1.RECID, T1.OMOPERATINGUNITNUMBER, T1.RECID
FROM DIRPARTYTABLE T1
CROSS JOIN BRANCHBUDGETBRANCHUSERFILTERVIEW T2
WHERE (((T1.PARTITION=?)
AND ((T1.OMOPERATINGUNITNUMBER LIKE ? ESCAPE '\' )
AND (T1.OMOPERATINGUNITTYPE=?)))
AND (T1.INSTANCERELATIONTYPE IN (2377) ))
AND ((((((T2.PARTITION=?)
AND (T2.PARTITION#2=?))
AND (T2.PARTITION#3=?))
AND (T2.PARTITION#4=?))
AND (T2.PARTITION#5=?))
AND ((((T2.USER_=?)
AND (T2.BRANCHATTRIBUTERELTABLEVALIDFROM<=?))
AND (T2.BRANCHATTRIBUTERELTABLEVALIDTO>=?))
AND (T1.RECID=T2.OMOPERATINGUNITRECID)))
GROUP BY T1.OMOPERATINGUNITNUMBER, T1.NAME, T1.RECID
ORDER BY T1.OMOPERATINGUNITNUMBER
Does anyone have any idea why joining to my view would cause the lookup to sort by RecId when I click the lookup but it still sorts properly when i key * into the field? The large majority of users will use their mouse to open the lookup so asking them to key * isn't a great workaround for my users. Any suggestion is greatly appreciated!
*This post is locked for comments
All responses (
Answers (
1,657
