AX / D365FO – Guide to Creating a Dynamic Class for Updating or Inserting Records in Microsoft Dynamics 365 – AX / Dynamics 365 For Finance and Operations blog

In the world of Microsoft Dynamics 365 development, there are often scenarios where you need flexible solutions that can adapt to various tables and data structures. In this article, we’ll walk through creating a class in X++ that allows you to dynamically update or insert records into a specified table using a map of values.

Introduction

The goal is to create a class that can:

Accept a generic table (Common).
Take a map of values with field names and associated values.
Update an existing record based on a key field or insert a new one if it doesn’t exist.

This class is particularly useful when working with data from external sources, such as web services or import files, where the data structure can vary.

The OTS_TableDataHandler Class

Below is the complete OTS_TableDataHandler class with detailed explanations for each part of the code.

public class OTS_TableDataHandler
{
    /// <summary>
    /// Updates or inserts a record in a specified table with the provided values.
    /// </summary>
    /// <param name="_table">Instance of the table to operate on.</param>
    /// <param name="_newValues">Map containing fieldName: fieldValue pairs.</param>
    /// <param name="_keyField">Name of the key field to uniquely identify the record.</param>
    public void updateOrInsertTableWithValues(Common _table, Map _newValues, str _keyField)
    {
        DictTable dictTable = new DictTable(_table.TableId);
        DictField dictField;
        FieldId fieldId;
        str fieldName;
        anytype newValue, convertedValue;
        ;

        // Verify if the key field is present in the new values
        if (!_newValues.exists(_keyField))
        {
            throw error(strFmt("The key field '%1' must be present in the new values map.", _keyField));
        }

        // Retrieve the key field value
        anytype keyValue = _newValues.lookup(_keyField);

        // Search for the existing record based on the key field
        select firstonly forupdate _table
            where _table.(fieldName2Id(_table.TableId, _keyField)) == keyValue;

        if (!_table.RecId)
        {
            // The record does not exist, create a new record
            _table = dictTable.makeRecord();
            // Set the key field
            _table.(fieldName2Id(_table.TableId, _keyField)) = keyValue;
        }

        // Iterate through the fields to update or insert
        MapEnumerator enumerator = _newValues.getEnumerator();
        while (enumerator.moveNext())
        {
            fieldName = enumerator.currentKey();
            newValue = enumerator.currentValue();

            // Avoid resetting the key field
            if (fieldName != _keyField)
            {
                // Retrieve the field ID
                fieldId = fieldName2Id(_table.TableId, fieldName);

                if (fieldId)
                {
                    // Retrieve the field metadata
                    dictField = dictTable.fieldObject(fieldId);

                    // Convert the value to the correct type
                    convertedValue = this.convertToFieldType(newValue, dictField);

                    // Update or insert the field value in the record
                    _table.(fieldId) = convertedValue;
                }
                else
                {
                    // The field does not exist in the table
                    warning(strFmt("The field '%1' does not exist in the table '%2'.", fieldName, dictTable.name()));
                }
            }
        }

        // Save the record (insert or update)
        ttsBegin;
        if (!_table.RecId)
        {
            _table.insert(); // Insert a new record
        }
        else
        {
            _table.update(); // Update the existing record
        }
        ttsCommit;
    }

    /// <summary>
    /// Converts a value to the correct field type.
    /// </summary>
    /// <param name="_value">Value as anytype.</param>
    /// <param name="_dictField">DictField object for the field.</param>
    /// <returns>Value converted to the appropriate type.</returns>
    private anytype convertToFieldType(anytype _value, DictField _dictField)
    {
        FieldType fieldType = _dictField.baseType();
        ;

        switch (fieldType)
        {
            case Types::Integer:
                return any2Int(_value);
            case Types::Int64:
                return any2Int64(_value);
            case Types::Real:
                return any2Real(_value);
            case Types::Date:
                if (typeOf(_value) == Types::Date)
                {
                    return _value;
                }
                else
                {
                    return str2Date(any2Str(_value), 321); // Replace with the appropriate date format
                }
            case Types::UtcDateTime:
                if (typeOf(_value) == Types::UtcDateTime)
                {
                    return _value;
                }
                else
                {
                    return DateTimeUtil::parse(any2Str(_value));
                }
            case Types::String:
                return any2Str(_value);
            case Types::Enum:
                // Since enum values are passed as integers, we convert them directly
                return any2Int(_value);
            default:
                return _value;
        }
    }
}

Explanation of the Code

1. Updating or Inserting Records

Key Field Verification:
- The method checks if the key field is present in the _newValues map.
- Throws an error if the key field is missing.
Record Retrieval:
- Attempts to select an existing record based on the key field value.
- If the record doesn’t exist, it creates a new one using dictTable.makeRecord().
Field Iteration and Assignment:
- Iterates through the _newValues map using a MapEnumerator.
- Skips the key field to avoid resetting it.
- Checks if the field exists in the table.
- Converts the value to the correct type using convertToFieldType.
- Assigns the converted value to the record.
Saving the Record:
- Uses transaction blocks (ttsBegin and ttsCommit) to ensure data integrity.
- Decides whether to insert or update based on the existence of RecId.

2. Converting Values to Field Types

The convertToFieldType method handles the conversion of values to their appropriate types based on the field’s base type.
Type Handling:
- Integer and Int64: Uses any2Int and any2Int64.
- Real Numbers: Uses any2Real.
- Date: Checks if the value is already a date; if not, converts from string.
- UtcDateTime: Similar to date but uses DateTimeUtil::parse.
- String: Converts the value to a string.
- Enum: Assumes the value is an integer representing the enum value.

Key Points

Dynamic Field Handling: The class can handle any table and fields dynamically, making it highly reusable.
Type Safety: Ensures that values are converted to the correct types before assignment.
Error Handling: Provides warnings for fields that do not exist in the target table.
Transactions: Uses transactions to maintain data integrity during database operations.

Usage Example

Here’s how you can use the OTS_TableDataHandler class in practice.

public static void main(Args _args)
{
    // Instantiate the handler class
    OTS_TableDataHandler handler = new OTS_TableDataHandler();

    // Create a map with the new values
    Map newValues = new Map(Types::String, Types::AnyType);
    newValues.insert("IMPORTREF", "Import123");
    newValues.insert("IMPORTSTATUS", 2); // Enum value as integer
    newValues.insert("OPRNUM", "Op123");
    newValues.insert("CREATEDDATE", mkDate(24, 10, 2023)); // Date value
    newValues.insert("CREATEDDATETIME", DateTimeUtil::newDateTime(mkDate(24, 10, 2023), timeNow())); // UtcDateTime value

    // Specify the key field
    str keyField = "IMPORTREF";

    // Instantiate the table to operate on
    Common myTable = new YourTableName(); // Replace 'YourTableName' with your actual table name

    // Call the method to update or insert
    handler.updateOrInsertTableWithValues(myTable, newValues, keyField);
}

Notes:

Map Declaration: The map is declared with Types::AnyType to accept values of any type.
Value Types: Ensure that the values you insert into the map match the expected types of the table fields.

Benefits of This Approach

Reusability: The class can be used with any table and adapted to various scenarios.
Flexibility: Handles different data types dynamically.
Efficiency: Simplifies the process of updating or inserting records without writing redundant code.

Conclusion

Creating a dynamic class for updating or inserting records in Microsoft Dynamics 365 enhances the flexibility and efficiency of your codebase. By handling different data types and tables dynamically, you can reduce code duplication and improve maintainability.

Remember:

Always ensure that the data types of your values match those expected by the table fields.
Test the class thoroughly with various data scenarios to ensure reliability.
Handle exceptions and errors gracefully to maintain data integrity.

AX / Dynamics 365 For Finance and Operations blog