Sunday, April 26, 2009

Lookup methods tutorial + custom list lookup implementation

One of the great features available in Dynamics AX is the support of lookup forms that provide a user-friendly interface for selecting a value for a field from a list, and are highly customizable, allowing the developer great flexibility in meeting user needs.

Recently, I was posed a question of how to present a user with a list of custom-defined values in a lookup form.

The simple (and suggested) approach here is to create a new BaseEnum, containing all the specified values, and add a ComboBox control for this enumeration to the form. If the values are to be displayed conditionally (only a subset is shown each time), you can build a superset of all values in AOT, and use SysFormEnumComboBox class to limit the actual output at runtime.

But, this would not work in my case, because the values in the list were dependent on other data in the form plus the current date. Using a temporary table for this scenario seemed like an overkill (but possible). So i decided to investigate the system class SysLookup for hints on how this can be done with minimum effort. The implementation of this method is provided below.

As part of posting this implementation, I decided to also briefly describe some of the other lookup method options available to an AX developer, turning this post into a small tutorial on lookup methods.

Tutorial_LookupMethods_Screenshot

1. The approach used most in the application is the implicit lookup based on table fields. As you can see from the control properties, DataSource and DataField specify which table field the control is based on. In my example, it is based on SalesTable.CustAccount, which uses the ExtendedDataType CustAccount, which has a relation to CustTable.AccountNum. Therefore, any time you add this field to a form, it will automatically provide a lookup button to select one of the customers.

Relations being specified on EDTs is at times confusing to people, who are used to seeing the relations between tables on the tables themselves. Technically speaking, such definitions on EDTs are incorrect. But no worries, AX supports the “correct” scenario out of the box as well. The relation to CustTable could have been specified on the table just as well.

2. 2nd most used approach is to specify the ExtendedDataType on the control directly. This is used to allow the user to specify a value to filter the records on directly from the form, for example (without the need to go to Extended Query form). It is also indirectly used on all RunBase dialogs in the system (when creating a DialogField, we specify the EDT to be used, which is transfered to the ExtendedDataType property on the corresponding control). In the tutorial, it is again the CustAccount EDT that is specified in the properties.

3. The 2 above examples both used metadata to define what lookup is to be displayed. The 3rd most used approach is relying on SysTableLookup class and builds the lookup at runtime. The code is relatively straightforward, and is described in more detail on MSDN. Using this approach, the developer can specify the query to filter the data being displayed in the lookup form. Note, that SysTableLookup only allows to have data from one table in the lookup form (+ display methods on this table). SysMultiTableLookup is an extension I have created a while ago, that adds this and other useful functionality to SysTableLookup class.

4. The remaining 2 standard approaches are rarely used in the application. Lookup based on ExtendedDataType is very similar to approach described under #2, but is executed from code at runtime. This way, you can change the lookup data displayed dynamically, based on some conditions (For example, on Ledger Journal lines, the offset account lookup shows vendors, customers, banks, etc. based on the offset account type specified on the line).

5. BaseEnum values are usually represented as a ComboBox (and, in special cases, CheckBox or RadioButton) control on forms in Dynamics AX. The lookup is provided by the kernel automatically for this type of control. But, sometimes, it is required to show the values of an enumeration in a string control – most common scenario is providing filter capabilities based on enums, where multiple values can be specified at once, similar to extended query form filters. In fact, this approach is actually used on SysQueryForm for enum fields. As you can see from the code, the lookup call is also very simple in this case.

6. This approach does not currently exist in the application, and the goals for its implementation were already described above. The lookup method code in this case looks rather straightforward, here it is:

public void lookup()
{
Counter yearCount;
List valueList = new List(Types::String);

for (yearCount = 0; yearCount < 5; yearCount++)
{
valueList.addEnd(strFmt("Year %1", year(SystemDateGet()) - yearCount));
}

SysLookup::lookupList(this, valueList, "List of years");
}

As you can see, the data displayed in this lookup depends on current system state (date) and other data (yearCount). It uses the class List to hold the values to be displayed.

You can download the project (Compatible with Axapta 3.0 – Dynamics AX 2009) with the tutorial and custom list lookup implementation by following the link below:

Note: The xpo contains changes to SysLookup class/form (only usr layer has been exported, for your convenience). Be careful when importing those, and don’t import them into the production environment.

51 comments:

  1. Hi i have a query
    In Ax2009 the employee name from EmplTable is fetched using the dirParty().Name.

    My client has a customized lookup in AX40 , in which he is using SysLookup Class to create a lookup.The fields he added in it are EmplId and Empname. And we are upgrading the AX40 application to Ax2009.

    how do i write a lookup in which i can have both Id and Name of the employee in the lookup.

    Please help

    ReplyDelete
  2. Well, you have 3 approaches:
    - use the class I created here:
    http://kashperuk.blogspot.com/2008/09/sysmultitableloookup-dynamic-lookups.html
    - use a display method (one exists on EmplTable) displaying it in the lookup
    - create a separate form (one is actually created - EmplIdLookup)

    ReplyDelete
  3. Hi,
    Using SysTableLookup approach (#3), Is there a way to make "go to main table" open up different form (NOT to the form specified in the EDT relation)?

    Thanks,
    Charles

    ReplyDelete
  4. Well, SysTableLookup does not have anything to do with "Go to main table". It is only used for lookups.

    But, what you want to do is possible.
    Simply override the jumpref method on the control (or, preferably, datasource field).
    In there you can put whatever code you like instead of the call to super()

    Please browse the web for examples of jumpRef use.

    ReplyDelete
  5. how to achieve go to main table on dialog field.
    for example a dialog is having a field of lookup custAccount, now what i need to have a go to mai table functionality on the same.

    for ne suggestions do reply me on amolgupta1983@gmail.com

    ReplyDelete
  6. is anyway to filter records (dataset) based on display method?

    ReplyDelete
  7. No. Filtering on display and edit methods is not possible.

    ReplyDelete
  8. Hi Vanya,
    first, congratulations for your blog!
    I need to develop something like the fourth aproach you described. Basically, I have a StringEdit with EDT ProdId. But in the lookup of this, it´s necessary to show only the production orders with ProdStatus == StartedUp.
    How can I do that?

    Thanks in advance
    Lucas, Brazil

    ReplyDelete
  9. Well, the easiest thing to do would be to create a new EDT ProdIdStartedUp, which has a relation set up similar to ProdId, with an extra Related Field Fixed relation "4 == ProdStatus".

    Otherwise, you have to go with option 3, where you create the lookup through SysTableLookup. Browse the net for examples - there are plenty, and in the application itself as well.

    ReplyDelete
  10. How do you change the fixed relation value in code? I have two drop-down edt and i want the second edt to filter based on the selection of the first edt. So basically, the selection of the first drop-down will set the fixed relation value for the second drop-down.

    Thanks,
    Mike

    ReplyDelete
  11. Well, you can't dynamically change the underlying EDT properties, so it's not an option here.
    But it is really easy to achieve the desired result using approach #3 mentioned in the post above - use SysTableLookup class, building your query and adding a range based on the value in the first lookup.

    ReplyDelete
  12. good day Vanya.

    Pls.. to help me i am a junior programmer on a company and i need to research a information about ax2009, after i have done on editing in methods on ax the display edit would not open so i can not edit again my programm in methods, why is that happen and what should i do.

    ReplyDelete
  13. I am sorry. I don't think I understood what you mean there. I will try to answer the question the way I understood it.

    Just to recap - you edited some code in AX, but now you cannot do it anymore. Is that right?

    If so, it can be due to one of the following:
    - Source Control way disabled, but now it is enabled and you don't have the file checked-out.
    - The development license expired, and you need to update it
    - The configuration keys responsible for development were turned off (not sure that it is possible, though)

    ReplyDelete
  14. Hi Vanya,

    I have to get in the lookup of a form field the RecId of the register selected. How can I do that?

    Thanks
    Samuel

    ReplyDelete
  15. Well, just add that field to the lookup form field list. But I would advise against this, since RecId is a non-user friendly field, so the user should NEVER see it. Present the correspondig natural key instead.

    ReplyDelete
  16. Sorry Vanya, I did not express myself clearly. Think in a hipotetic case of a lookup field that shows two fields. The combination of both is a primary key, while a single one is not. As we are talking about a comboBox, when I select, one of the fields will be shown (put there). But I have to be able to catch this combination, because the other field will be put in another control in the form. That´s why I thought about catching the RecId, because it is unique. So the "getting" that I wrote was related to the field selected.
    Sorry about the bad question before.
    Thanks a lot
    Samuel

    ReplyDelete
  17. OK, now I understand. Yes, this is definitely possible, i have don't that a couple of times at least on various occasions. But they were specific solutions to specific forms.
    Let me try to think of a generic way to handle that. If I can't come up with one, I'll post a short entry about how to handle your scenario - it would require to create your own lookup form

    ReplyDelete
  18. in how many ways we can create report

    ReplyDelete
  19. Depends on what type of report, SSRS or X++.
    If it's the latter, consider using the report wizard - that's the easiest way.
    There is a large chapter about working with reports in MorphX IT book. Maybe you should invest into buying one.
    There is also a tutorial_RunBaseReport class in the application - do check it out.

    ReplyDelete
  20. Hi
    I was reading the comment on getting the recid from the selected lookup record. I have also checked your followup.
    I had the exact same problem, a table that has two fields that are unique when combined.

    One easy way to solve this was to declare the SysTableLookup in classDeclaration.

    In the modified event if the stringedit control the selected record could be located e.g. by using the following line:

    EmplTable.data(sysTableLookup.parmFormRun().dataSource().cursor());

    However, this approach also requires a check in the validate method if the user selects a record by typing the name. This check should return false if more than one record with this value exists.

    I don´t know if this was what "Anonymous" was looking for, but i think it was.

    /Jonas

    ReplyDelete
  21. Hi Vanya Kashperuk,

    Kinldy help for the following query. In Ax 2009, dimensions are made global. Still i am looking for some query or mapping where i can sort it company wise where ever the dimension exists in the form. Any workaround !!

    Regards,
    dipankar,
    diptouch@gmail.com

    ReplyDelete
  22. Hi,
    How we can have Inventory dimension in lookup,
    I have a scenario where in I require the itemid and inventory dimension in lookup from PurchLine Table
    Is it possible if yes then how

    Regards
    Abhishek Mehta
    abhishekmehta3@gmail.com

    ReplyDelete
  23. Hi,

    I'm fairly new to AX 2009 and i was wondering if there is any way to add a lookup field to a dialog form, based on the Dialog class.

    Thanks,
    Eric
    zedleppelin13@hotmail.com

    ReplyDelete
  24. OVERVIEW:-
    hi, we are intigrating .net application (our ERP->WM) & AX 2009. so in order to work we need some mapping as site mapping, customer group mapping.
    So we decided to make only one table for this all mappings with columns like
    1.For which Mapping data this row contain.
    2.AX data value.
    3.Our ERP data value.
    as eg.
    ROW1: site,4,WM4
    ROW2: customerGroup,cus1,WMCus1
    ROW3...
    now i have created one preference form on that we want to allow to user that can map values as needed.
    here on form we have created 2 grid one for sites and second grid for custGroup.

    STRUCTURE OF GRIDS:
    on both grid there is two column only as one for AX Data and second for WM Data third column value is handled internally.

    MY PROBLEM IS:
    1. how to bind this one table with two grid on same form? is it possible?
    2. on both grid i want to provide combobox so user can select values as per grid. here in site grid combobox must show site related value for both column.
    3.And after user select values, data stored in same table as mention with firstColumn values as site or CustMapping.
    4.This is good way to provide mapping or i impliment this using different TABLES for CustGroup & Site. if we go with this option we need to increase number of tables as mapping increase.
    Thank you for your time in advance.

    ReplyDelete
  25. HI, for Above case of intigration of AX & WM (our ERP)
    Main PROBLEM is while retriving data on grid.
    as we have single table-> preferanceTable and that we are using on two grids on same form preferanceForm.now while form loads at that time how we can display sites related all (selected values) on siteGrid & custGroup Related values on custGroupGrid. Thank you

    ReplyDelete
  26. 1. Just add 2 different form datasources for the same table. Use the 1st datasource for the first grid and the second datasource for the second grid (be careful about setting the properties on form group controls correctly there, to avoid unexpected navigation issues)
    2. You can override the lookup methods on the respective datasource fields. This way, you can display different lookups for different grids.
    3. If you apply a filter on "Site" or "CustomerGroup" in the init of the datasource (which you have to do anyway in order to only display the respective values), the field value for this column will automatically be assigned if you create a new row in that grid.
    4. It's an OK way. There is already a similar pattern that you might want to take a look at in AX - it's called External codes, and the sole purpose is to provide a way to do this kind of mapping of internal AX codes with external codes.

    ReplyDelete
  27. Hi Vanya,
    I have a dialog field with UserID type. The field automatically has the dropdown list with 3 columns: userId, name, and security identifier. I like to remove the 3rd column. What's your suggestion on best approach?

    Thank you for your help!

    ReplyDelete
  28. The UserID refers to a system table, so the easiest way to change the behavior is to just use the SysTableLookup approach described above, imho.

    ReplyDelete
  29. Hi Vanya,
    On SalesCreateOrder form - I have a requirement of filtering contact id lookup - currently we see only the fields which are mentioned on contactpersonlookup form are appearing..

    my requirement is to display addtional columns of Delivery address, city, country, zipcode only when there is a delivery type address is there for the customer..
    means the additional columns should appear only when there is address type delivery is there for the customer..if possible can you provide the code..
    Thanks in advance - jagx.69@gmail.com

    ReplyDelete
  30. Hi Vanya,
    I have a requirement for Year dropdown on a report as filter. I tried something like this on the dialog method of the report..

    public Object dialog(Object _dialog)
    {
    DialogRunbase dialog = _dialog;

    DialogField fieldYear;
    Yr yearCount;
    List valueList = new List(Types::Int64);
    ;

    dialog.addGroup("@SYS1046");

    fieldYear = dialog.addFieldValue(typeid(Yr), calYear, "@SYS23908");

    for (yearCount = 2005; yearCount < 2015; yearCount++)
    {
    valueList.addEnd(yearCount);
    }

    SysLookup::lookupList(fieldYear.control(), valueList, "List of years");

    fieldYear.lookupButton(2);

    return dialog;
    }

    ReplyDelete
  31. The SysLookup::lookupList() is not suited for using with dynamically added controls. You would need to either postpone calling this method until the lookup is actually invoked (see controlMethodOverload method help), or create a form that opens in the lookup, or modify the code so that it supports integer controls, as well as buildcontrols (not runtime controls)

    ReplyDelete
  32. Hi,
    I want to be able to get the lookup drop down to open up as you type. It should look like the way the google suggestions look. Do you have any ideas?

    Thanks

    ReplyDelete
  33. I don't think that will be possible to do in AX without major coding.
    The way lookup forms work in AX is that focus is set to the lookup form as soon as it is opened, and the lookup form is closed automatically as soon as the focus is removed. Now, you can code to avoid that by introducing some sort of a condition controlling that.
    But then you are still left with the problem of correctly setting the focus and position back to the original control.
    So, it is probably possible to get very close to the behavior of Google, but it would require a lot of time, which is a waste.

    Filter by Grid can be a much faster option.

    ReplyDelete
  34. Hello All,
    Just struggling with a look up form and wondering if someone could help me please. On my sales ledger form under sales Line Tab I have inserted a new field called Job id which is EDT. I am calling a lookup form on this field. The data source of this look up form is a table contains sales id, job id and name. I want to add filter on the sales id of this look up form.
    So if user creates a new sales line select item number and when he clicks on job ID he can only see the record which is linked with the current sales id of sales line.
    At the moment if user clicks on the field he see following values in look up form
    Sales Id - Job Id - Name
    SO7546 701 Futaba
    SO7546 702 Futaba
    SO7546 703 Futaba
    SO7547 704 Futaba
    SO7547 705 Futaba
    SO7548 706 Futaba
    As i am creating a sales line on Sales ID SO7547 so i only want to see this record in my look up form.
    SO7547 704 Futaba
    SO7547 705 Futaba
    Kind Regards,

    ReplyDelete
  35. Well, I don't have access to AX right now to check, but the first thing I would try is add a Relation to SalesLine on SalesId to your table. AX is usually pretty smart and might automatically add a dynalink for you.

    If not, use args.record inside your lookup form to manually apply the filter

    ReplyDelete
  36. Hi Vanya,

    I have an issue related to Dimension hierarchy that should be implemented in EP as well. So the Dimension Lookup in EP (Selective modules) should show dimension hierarchy pattern. How do i modify this Lookup?

    ReplyDelete
  37. Hey guys,

    1. Setting dimension hierarchy for Purchase requisition and Expense Report.

    2. This should be done for the EP related forms only.

    3. At the time of edit and create of these 2 forms dimensions should work according to the hierarchy.
    So what are the modifications that can be done in the Lookup of dataset?
    If anyone has any idea of how it is done please let me know.

    Regards,
    Saurabh.
    punawalasaurabh@yahoo.co.in

    ReplyDelete
  38. Hi,

    While relating two fields in a table(for eg., ID and NAME, When we select ID then in NAME field it should show the respective matched names for that particular ID), but in NAME column lookup two columns are showing(Name (which the matching one) and ID which we selected). But i need only matched NAME in lookup and not with ID which we have selected in NAME field. What can i do for getting only matching NAME lookup in NAME field? Can you please give me a solution?

    ReplyDelete
  39. Hi,

    I need two tabs in lookup field. In one tab apecific list of ItemId should display. In other tab all the ItemId should be displayed. So how can i create a lookup with two tabs? Kindle help me..

    I tried "form help" property in edt. But it doesnt look satisfactory.. Please suggest me some idea..

    Regards,
    _Kannan

    ReplyDelete
  40. Well, there are a bunch of existing examples in AOT for this.
    Forms\InventLocationIdLookup
    Forms\UnitOfMeasureLookup

    are just some of the examples.

    Typically, the Form Help property on EDT is the one used to select a lookup form

    You can see if that if you take a look at
    EDTs\InventLocationId
    EDTs\UnitOfMeasureSymbol

    I am not sure why it did not work for you - maybe in the specific form you are trying to open a lookup, it is overridden or something, and a different form is displayed instead?

    ReplyDelete
  41. Hi,
    If i change to companies automatically some form functionalities(like tabpages) will be enabled or disabled.how can i achieve this thing.try to help me

    ReplyDelete
  42. Can you explain what you mean by that? I am not sure I follow. And what does it have to do with lookups?

    ReplyDelete
  43. Hi Vanya,
    In my report Dialog method,
    I added a dialog field with EDT InventSiteId, which gives the InventSite Table dropdown.

    Now I want to add another field of the same Table side by side with the InventSiteId, but only as a lookup reference.

    Any idea how to do it????

    ReplyDelete
  44. Well, the simplest would be to use your own form and override the modified event on the first field (InventSiteId)
    Otherwise, you'll have to use the controlname_modified() approach, where you would populate the value of the other control.

    ReplyDelete
  45. Hi Vanya,

    I have a report dialog where the second dialog field needs to have a lookup based on the selected value of 1st dialog field.
    So how do i add a custom lookup to a dialog field in report?

    ReplyDelete
  46. Replies
    1. Hello Vanya
      How can i not allow the user enter a value that does not appear in the list of values of lookup .

      Thanks

      Delete
  47. Hello ,



    How to not allow the user to enter a value that does not exist in in the list of values of lookup .

    Kindly Help .


    ReplyDelete
  48. Hello ,

    How can I do to not allow the user to enter a value that does not appears in the list of values of a lookup .
    Thanks .

    ReplyDelete
  49. Besides the obviously of using a base enumeration, there's a solution, but it's ugly, so I would suggest that you simply add validation on validateField, which would throw a warning if the value violates the query used in the lookup.

    If you still want to investigate the ugly solution, you can try overriding the validate() method on the control, and returning false; This method should not be called when you select a value from the lookup, as far as I remember. so the user won't be able to type in, but will only be able to select from the lookup.

    The even uglier solution is to override the typechange or whatever that method is called, which is called whenever you type in a character, and try handling user input through that.

    ReplyDelete

Please don't forget to leave your contact details if you expect a reply from me. Thank you