Wow! MSDN for Microsoft Dynamics AX is getting better every day. This is terrific news!! I still remember the days, when all the information was extremely hard to find. It had its own charm though :)
Anyway, this topic is really not about MSDN. It is about lookup forms.
First of all, for the record: I (and the Best Practices document as well) recommend creating custom lookup forms in AOT instead of dynamically coding them in the overridden lookup methods on controls/datasource fields.
But, in reality, this is true only for lookup forms with very large complexity. I won't go into a discussion of why that is the way it is here. :)
Now, back to what I was planning to write about:
In order to build a lookup form from code, developers use the SysTableLookup class.
You can go to MSDN (mentioned above) to read a How-to article on creating a run-time lookup form, as well as take a quick look at the SysTableLookup method descriptions.
SysTableLookup class has evolved over the multiple releases, providing more and more flexibility and control to the application developers.
I would like to publish another extension to this class, SysMultiTableLookup, which I hope will prove useful to members of the AX community.
Short list of features:
- Backward compatible, should cover everything that is present in AX 2009 version of SysTableLookup class
- Allows including multiple tables into lookups with different join types
- Completely based on the Query that you build, no extra parameters (except for the control) are needed to initialize the class
- Allows adding aggregated fields to the lookup
- Displays fields based on Boolean Enum as check boxes
- Allows to specify alternative labels when adding fields to the lookup
New download link, as Axaptapedia seemed to mess up the file
You can download the project from axaptapedia.com.
It has (to some extent) been tested on Axapta 3.0 SP3, AX 4.0 and AX 2009.
Also included in the project is a tutorial form, showing 4 examples of dynamic lookups using the new class. After importing the project, make sure to try out the form, and use it for future reference for code examples and other inspiration.
Great thanks. Good solution for not so complex look-ups :)
ReplyDeletecan u please tell me how to close all the forms and reports when changing the companies.
ReplyDeleteif i have opened one company forms and reports and want to change the company then all the forms and reports opened in the previous company should close
Well, the only way I see here is using WinAPI to find all windows child compared to the main AX window, and sending them a WM_Close message.
ReplyDeleteBut I am not sure it will work and it is the correct solution.
And I don't have a sample xpo for this.
You can check out http://axaptapedia.com/Tabax for an example of how to use WinAPI to enumerate open windows in AX
I have created a table Acc where i have fields id,name , ActiveInactive(Enum),and reason Enum
ReplyDeleteif i select inactive and give the reason 1 or 2 or 2.
in ax2009 crm i have added new field id from my Acc table and made a look up and it has look up on all fields but i want all the fileds that are active, and inactive having only reason 2 how to achieve it
luvchoc@rediffmail.com
This is a complex criteria with combined AND and OR clauses.
ReplyDeleteThis can be achieved by using extended range.
See a detailed example on axaptapedia:
http://www.axaptapedia.com/Expressions_in_query_ranges
Thanks a LOT! Very nice and useful class. Definitely, it should be part of standard Ax since long ago :)
ReplyDeleteJust what I needed!
ReplyDeleteThankyou!!!
Hi Vanya,
ReplyDeleteI am not able to get values in the lookup - . Kinldy need your advice.
Regards,
diptouch@gmail.com
public static void projActivityLookup1(ProjId _projId,FormControl _callingControl)
{
Query q;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildRange qbr1,qbr2;
QueryRun qr;
HierarchyIdBase hierarchyId;
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SMMActivities),_callingControl);
;
hierarchyId = HierarchyLinkTable::findRefTableRecId(tableNum(ProjTable),ProjTable::find(_projId).RecId).HierarchyId;
q = new Query();
qbds1 = q.addDataSource(tableNum(HierarchyTreeTable));
qbds1.orderMode(OrderMode::OrderBy);
qbds1.addSortField(fieldNum(HierarchyTreeTable,HierarchyId));
qbr1 = qbds1.addRange(FieldNum(HierarchyTreeTable,HierarchyId));
qbr1.value(QueryValue(hierarchyId));
qbr2 = qbds1.addRange(FieldNum(HierarchyTreeTable,ElementNodeType));
qbr2.value(QueryValue(ElementNodeType::Activity));
qbds2 = qbds1.addDataSource(tableNum(smmActivities));
qbds2.relations(false);
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.addLink(fieldNum(HierarchyTreeTable,RefRecId),fieldNum(smmActivities,RecId));
sysTableLookup.parmQuery(q);
sysTableLookup.addLookupfield(fieldNum(SMMActivities,ActivityNumber));
sysTableLookup.addLookupfield(fieldNum(SMMActivities,Purpose));
sysTableLookup.performFormLookup();
}
Well, it looks OK from here. Are you sure you have data in these 2 tables?
ReplyDeletehello,
ReplyDeleteHow can i select more than one record in a lookup?
Example: the value of the lookup control should look like this: 001,002,0003
Three accounts as lookup values.
You have plenty of options.
ReplyDeleteYou can create a custom form, that will close when you have selected all the records you want and clicked OK or something.
Another option is to just use an approach similar to the Range EDT, where you select value after value (like in the Extended Range form (SysQueryForm)).
Another option is to use an approach like in the classes SysLookupMultiSelect* (used for selecting Addresses in AX 2012)
Good work, Vanya!!
ReplyDeleteI tried it, and it worked me very well...
hi Vanya,
ReplyDeletei am want to show the position heirarchy tree structure in dialog lookup Axapta 2012. is it possible to develop in the dialog box lookup. Please give the solution how can we develop the code
Take a look at the lookup for Sales category field on Sales order lines - it displays a tree structure of categories that you can pick from. You can see the implementation behind this, and re-use it.
ReplyDeleteHey Vanya,
ReplyDeleteI build a lookup successful, but the order by column does not work for joined tables. Can help me?
Found and helped even in 2020. Thanks a lot!
ReplyDeletehttp://alexvoy.blogspot.com/2022/06/d365fo-sysmultitablelookup-dynamic.html D365FO: SysMultiTableLookup - dynamic lookups based on multiple tables
ReplyDelete