Dynamics AX Custom Lookup

As a Dynamics AX developer, you'll often have to perform custom lookups. Meaning that the user may only select records in another table depending on some condition on the form, or elsewhere.


There are two ways of creating custom lookups. I'll show the easiest here, and maybe show the other way in the future.

So if you're thinking you'll have to create a new form and all that, forget it, you won't. All you have to do is write some pieces of code.



First let's create our lookup method directly on the table. You can check other tables for these special lookup methods. They appear in lots of tables. Take for example the table ContactPerson. It has the following custom lookup methods by default:

• lookupActionContactPerson
• lookupCustContactPerson
• lookupDirContactPerson
• lookupDirContactPersionId
• lookupVendContactPerson

Now, to create a custom lookup, we'll use the class SysTableLookup as it provides us a set of methods to do so.


Here's our method, to be created directly on the table DummyTable, with some dummy data and names.

public client static void lookupDummyTable(FormStringControl _ctrl,
                                           MyFilterEDT _filter)
{
  
    // Intantiantes a SysTableLookup object telling it which control activated the lookup, and
    // what table should be displayed inside the lookup form.
    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(DummyTable), _ctrl);

    Query query = new Query(); // This query will be used by the lookup form.
    QueryBuildDataSource qbds;

    ;

    // The "addLookupField" method adds the the fields that will be shown inside the lookup's grid. 
    // The second, boolean parameter indicates that this field will be returned from the lookup when a
    // record is selected. In this case, we'll return our DummyPK.
    sysTableLookup.addLookupField(fieldnum(DummyTable, DummyPK), true); 
    sysTableLookup.addLookupField(fieldnum(DummyTable, Field1));
    sysTableLookup.addLookupField(fieldnum(DummyTable, Field2));
    sysTableLookup.addLookupField(fieldnum(DummyTable, Field3));


    // Using our dummy table as a DataSource Table.
    qbds = query.addDataSource(tablenum(DummyTable));

    // This is the key part, what we want to filter to be displayed in the grid.
    qbds.addRange(fieldnum(DummyTable, MyFilterColumn)).value(_filter);


    // Passes the query to the sysTableLookup.
    sysTableLookup.parmQuery(query);

    // Activates the lookup.
    sysTableLookup.performFormLookup();
}

With that little piece of code, Dynamics AX will already display a lookup form with the table you've specified as a DataSource, displaying the fields you specified with the method addLookupField and following the results criteria of the Query you've passed to it.



Now all we have to do is actually call our lookup method, by overriding the lookup of the correct field in our Form's Data Source:

public void lookup(FormControl _formControl, str _filterStr)
{
    ;

    DummyTable::lookupDummyTable(_formControl, "SomeFilter");
}

And that's it! The lookup will be performed. But there's one additional step we'll have to take here...

When the user tries to select our DummyTable reference, Dynamics AX will display the results of the query, based on the filter we've created. So the user will only be able to see what we want him to see, and so he'll only be able to select what we want. The problem is that if he types in an existing record for the DummyTable in the control, even if does not get shown in our lookup, Dynamics AX will accept that. Remember: we're only limiting what the user may see and select for that field, but we're not limiting what he can actually type in the control. So if we don't validate it somehow, Dynamics AX will run the standard validation, which will only check if a record exists for that EDT and so on. To avoid that the user does in fact type in something we don't want, we'll have to override the validate method for our field in our Data Source. The simplest logic is to check if the DummyTable record the user typed in does attend the filter we've specified:

public boolean validate()
{
    DummyTable    dummyTable;

    ;

    dummyTable = DummyTable::find(currentRecord.DummyTableRefField);

    return dummyTable.MyFilterColumn == "SomeFilter";
    
}

So if the user simply types in for a record that does not attend our filter, Dynamics AX won't let the user save that value.



This is the easiest way to effectively implement a custom lookup and validate the user input in case he doesn't select the record from the lookup. For this example I used a literal filter, a string. But in most cases, you'll have to filter according to what the user selected for another field in your form. Things should usually be dynamic.

Hope this helps...

Filtering a DataGridView

Been reading a lot of people getting confused about how to develop a filter to a DataGridView.

The easiest way to do that is to apply the filter to a BindingSource object, which will then modify the DataGridView to display the filtered result in it.

So, here's my example form design:

The only property I've changed on the designer on the DataGridView and the BindingSource is their names.
That ComboBox will let us decide which column we want to filter, with the TextBox's value. That CheckBox will indicate whether the filter is active or not.

A very easy way to implement a Live Filter

We'll implement a live filter, meaning the data will be filtered as the user types, instantly. Now, this kind of filter is pretty good for small data, small collections. Imagine instantly filtering 30000 records...

For our live filter, we'll assign each key control's event to the same handler, this way we'll have to write less code, it'll be easier.

Our DataGridView will have two columns, so here we throw in some sample data, and configure the comboBoxColumns items using an anonymous type, all inside our form's Load event:

private void FormTestGrid_Load(object sender, EventArgs e)
{
    // This prepares our data source and our grid:

    DataTable dt = new DataTable();

    DataColumn colName = new DataColumn("Name");
    DataColumn colPrice = new DataColumn("Price");

    dt.Columns.Add(colName);
    dt.Columns.Add(colPrice);

    dt.Rows.Add("Bike", 150.00);
    dt.Rows.Add("Helmet", 59.99);
    dt.Rows.Add("Shoes", 90.99);
    dt.Rows.Add("Shirt", 30.00);
    dt.Rows.Add("Blouse", 75.00);
    dt.Rows.Add("Cap", 30.00);
    dt.Rows.Add("Pants", 50.00);
    dt.Rows.Add("Glasses", 15.00);

    bindingSource.DataSource = dt;

    dataGridView.DataSource = bindingSource;

    dataGridView.Columns.Add("colName", "Name");
    dataGridView.Columns.Add("colPrice", "Price");

    dataGridView.Columns["colName"].DataPropertyName = "Name";
    dataGridView.Columns["colPrice"].DataPropertyName = "Price";


    // And this prepares our ComboBox to enable us to select what column we want to filter:

    comboColumns.DataSource =
        new[]
        {
            new { Text = "Name", Value = "Name"},
            new { Text = "Price", Value = "Price"},
        };

    comboColumns.DisplayMember = "Text";
    comboColumns.ValueMember = "Value";
}

The above code fills a DataTable and tells the BindingSource that it's its DataSource, adds the Columns to our Grid and binds them to the correct data source columns.
It also adds the items that represent our columns to the ComboBox.


Now we declare the method that will receive every control's key events:

private void FilterAction(object sender, EventArgs e)
{
}

We set our events to that handler, inside our constructor (Could also be inside our Load event):

public FormTestGrid()
{
    InitializeComponent();

    comboColumns.SelectedValueChanged += new EventHandler(FilterAction);
    txtFilter.TextChanged += new EventHandler(FilterAction);
    chkFilter.CheckedChanged += new EventHandler(FilterAction);
}

The BindingSource component has a 'Filter' property which accepts SQL-Like Syntax constraints. For more information check the MSDN documentation on it.

And now we can write the method that will actually apply the filter, FilterAction.

private void FilterAction(object sender, EventArgs e)
{
    if (chkFilter.Checked)
    {
        bindingSource.Filter = string.Format("{0} like '%{1}%'", comboColumns.SelectedValue, txtFilter.Text);                
    }
    else
    {
        bindingSource.Filter = string.Empty;
    }
}

Notice that we use 'like' instead of '=' so that it doesn't have to be the exact same value to filter, but that's just for our example, you should decide what works better for you.


So this is it, just with those few lines of code, assigning three control's events to the same handler, we can implement our live filter to our Grid.