Creating a Table Group All Pattern in Dynamics F&O

There is a recurring pattern across various parts of the ERP system that allows users to select a specific record, a group, or all. This pattern is commonly applied against Customer, Vendor, and Product Masters. The granularity it offers is highly useful, as it simplifies the user’s parameterization process.

For example, with Customers, we can associate a group (Group), which consists of multiple customers, with a single record. Additionally, we can create exceptions for individual customers (Table), and we can also set a default value (All). From a technical perspective, when we request a record associated with a specific Customer, the system first searches for a record tied to that customer. If none is found, it looks for a record associated with the customer’s group, and if that still doesn’t exist, it defaults to the record for All.

In this picture of journal lines for trade agreements, we find this pattern applied three times: for customers (red), for suppliers (blue), and for products (green).

Table

Let’s create this pattern for a customer, customer group, and All.

First, we need an Enum with the options ‘Table’, ‘Group’, and ‘All’. We can either create this in our project or reuse a standard Enum or EDT. I’ll directly use the Enum ‘TableGroupAll’. Additionally, we need a primary key (Id) and a field for customers. For the customer field, I’ll create a simple string field to demonstrate that this pattern works well even when there is no default Microsoft EDT.

Let’s create the indexes, ensuring that the same Customer and Enum combination cannot be repeated.

Next, we create two relations of type ‘Relation’: one for the Customer, table ‘CustTable’, and another for the Customer Group, table ‘CustGroup’. Within each, we define a ‘Normal’ relation for the customer/group and a ‘Field Fixed’ relation, specifying the Enum value required for that relation to be active.

Finally, we’ll add a static class that returns the record based on the customer.

public class TST_CustGroupAll extends common
{
    public static TST_CustGroupAll findRecordByCustomer(CustAccount _cust)
    {
        TST_CustGroupAll custGroupAll;

        // Find the customer

        custGroupAll = TST_CustGroupAll::findByCustType(_cust, TableGroupAll::Table);

        // If a record was found return it

        if (custGroupAll)
        {
            return custGroupAll;
        }

        // Find the Group

        custGroupAll = TST_CustGroupAll::findByCustType(CustTable::find(_cust).CustGroup, TableGroupAll::GroupId);

        // If a record was found return it
        
        if (custGroupAll)
        {
            return custGroupAll;
        }

        // Finally find for all and return it, both if exist or doesn't exist

        custGroupAll = TST_CustGroupAll::findByCustType("", TableGroupAll::All);

        return custGroupAll;
    }

    public static TST_CustGroupAll findByCustType(str _custorGroupCode, TableGroupAll _type, boolean _forUpdate = false)
    {
        TST_CustGroupAll custGroupAll;

        if(_type == TableGroupAll::All)
        {
            _custorGroupCode = "";
        }
        
        if (_forUpdate)
        {
            custGroupAll.selectForUpdate(_forUpdate);
        }

        select firstonly custGroupAll            
            where custGroupAll.Customer == _custorGroupCode 
            && custGroupAll.TableGroupAll == _type;        

        return custGroupAll;
    }
}

Form

Let’s create a simple list form. In this form, we’ll need to make a few adjustments. First, make the Customer field mandatory. When the Enum is set to ‘All’, the Customer field should no longer be mandatory and should also become non-editable. Additionally, the Customer field’s value should be cleared when the Enum value changes.

To achieve this, we’ll first set the Customer field’s property to ‘Auto Declaration: YES’ so that we can reference it in the form’s code.

Next, we add the logic in the datasource method active() and in the modified() method of the TableGroupAll field in the datasource.

[Form]
public class TST_CustGroupAll extends FormRun
{

    protected void custGroupAllUpdate()
    {
        if(TST_CustGroupAll.TableGroupAll == TableGroupAll::All)
        {
            TST_CustGroupAll_Customer.enabled(false);
            TST_CustGroupAll_Customer.mandatory(false);
        }
        else
        {
            TST_CustGroupAll_Customer.enabled(true);
            TST_CustGroupAll_Customer.mandatory(true);
        }
    }
   
    [DataSource]
    class TST_CustGroupAll
    {
        [DataField]
        class TableGroupAll 
        {
            
            public void modified()
            {                
                super();
                TST_CustGroupAll.Customer = "";

                // Updates the customer field display
                element.custGroupAllUpdate();
            }
        }
        
        public int active()
        {
            int ret;        
            ret = super();

            // Updates the customer field display
            element.custGroupAllUpdate();
            
            return ret;
        }
    }
}

After compilation, our form should be working.

Test

We can test our development, for example, using an executable class directly in the browser:

internal final class TST_TstCustGroupAll
{    
    public static void main(Args _args)
    {
        Info("US-001 " + TST_CustGroupAll::findRecordByCustomer("US-001").Id);
        Info("US-002 " + TST_CustGroupAll::findRecordByCustomer("US-002").Id);
        Info("US-003 " + TST_CustGroupAll::findRecordByCustomer("US-003").Id);
        Info("US-004 " + TST_CustGroupAll::findRecordByCustomer("US-004").Id);
    }
}

[url]/?cmp=[DataArea]&mi=SysClassRunner&cls=TST_TstCustGroupAll


Posted

in

,

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *