How to: Update the data in D365FO table in production environment

 Did you ever wish that you could just update or delete some data in D365FO production with out custom script route. The way we had access to AX 2012 AOT tables. Of course it is not possible to do it in D 365 FO directly but I developed a small job which accepts some parameters to update/ delete the data in production environment directly. 

THIS IS ONLY FOR SENIOR DEVELOPER who understands the D365FO table structure properly and understand the consequences of updating the data in prod. So Please use it with caution and check the result in an environment where Production copy exists.

internal final class XXXDataOprerations
{
    /// <summary>
    /// Class entry point. The system will call this method when a designated menu 
    /// is selected or when execution starts and this class is set as the startup class.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        Dialog          dialog = new Dialog('Data operations');
        DialogField     dlgTableName, dlgFieldName,dlgUpdateFieldName, dlgUpdateValue, dlgForce, dlgOperation, dlgFilter;
        TableId         tableId;
        FieldId         fieldId,updateFieldId;
        Common          common;

        // Add dialog fields
        dlgTableName        = dialog.addField(extendedTypeStr(TableName), 'AOT Table Name');
        dlgFieldName        = dialog.addField(extendedTypeStr(FieldName), 'AOT Field Name');
        dlgUpdateFieldName  = dialog.addField(extendedTypeStr(FieldName), 'AOT Field name that needs to be updated');
        dlgFilter           = dialog.addField(extendedTypeStr(String255), 'Value for filter criteria');
        dlgUpdateValue      = dialog.addField(extendedTypeStr(String255), 'New Value (only if Update)');
        dlgOperation        = dialog.addField(enumStr(BICDataOperations), 'Operation');
        dlgForce            = dialog.addField(extendedTypeStr(NoYesId), 'Force (Skip Validation)');

 

        if (dialog.run())
        {
            // Convert input strings to IDs
            tableId = tableName2Id(dlgTableName.value());
            fieldId = fieldName2Id(tableId, dlgFieldName.value());

 

            if (!tableId || !fieldId)
            {
                throw error('Invalid Table or Column Name.');
            }

 

            // Create a dynamic table buffer
            DictTable dictTable = new DictTable(tableId);
            common = dictTable.makeRecord();

 

            ttsbegin;
            while select forUpdate common
                where common.(fieldId) == dlgFilter.value()
            {
                if (dlgOperation.value() == XXXDataOperations::Select)
                {
                    info(strFmt('select record found with Recid %1',common.RecId));
                }
                else if (dlgOperation.value() == XXXDataOperations::Delete)
                {
                    if (dlgForce.value())
                    {
                        common.doDelete();
                        info(strFmt('%1 record FORCE deleted.',common.RecId));
                    }
                    else
                    {
                        common.delete();
                        info(strFmt('%1 record deleted.',common.RecId));
                    }
                }
                else if (dlgOperation.value() == XXXDataOperations::Update)
                {
                    updateFieldId = fieldName2Id(tableId, dlgUpdateFieldName.value());
                    if(updateFieldId)
                    {
                        common.(updateFieldId) = dlgUpdateValue.value();

 

                        if (dlgForce.value())
                        {
                            common.doUpdate();
                            info(strFmt('%1 record FORCE updated.',common.RecId));
                        }
                        else
                        {
                            common.update();
                            info(strFmt('%1 record updated.',common.RecId));
                        }
                    }
                    else
                    {
                        throw error('Invalid Table or Column Name.');
                    }
                }
            }
            ttscommit;

             info("Operation completed successfully.");

        }
    }

 } 

Create new Enum: XXXDataOperations with 2 Enum values: Select, Delete, Update


When you execute the class, you will get dialog like below:




Fill in the values as required and data will be updated/deleted/Viewed based on criteria given.

P.S. This is very basic job which can be enhanced as needed but gives a very quick fix for data correction. This is not good when you have a lot of data to be manipulated in that case job should be created and standard feature of custom script should be used. You can read about it here


Comments

Post a Comment

Popular posts from this blog

How to: Use enum values instead of integers in BI reports for Dynamics 365 finance and operations

Image
For most of the BI projects related to Dynamics 365 finance and operations, We need to use BYODW to use Power BI or any other BI tools. As we know, enum fields are stored as integers in SQL, so data populated in BYODW comes as integers instead of the enum value. To display this data either you can hardcode values in SQL statements or in BI tools. Instead, we can use SRSAnalysisEnumsEntity in Dynamics 365 F&O. It exports the data from the SRSAnalysisEnums table which holds all the enum and respective enum elements used in perspective. If the enum is missing from the list then below job can be used to insert the data in SRSAnalysisEnums table. class SRSAnalysisEnumsGenerator {     public static void main(Args _args)     {         SRSAnalysisEnums analysisEnums;         DictEnum currentEnum;         str currentEnumName;         int valueCount;       ...
Read more

How to: Import Bank statements from Azure file storage to Dynamics 365 finance and operation using Power automate (formally flow)

Image
We had a requirement for importing of bank statements in Dynamics 365 Finance and Operations using integration. We achieved this using the recurring data jobs in D 365 and Microsoft Power automate. There are basically 3 parts to all the configuration 1. Azure app for authentication. 2. Data processing group and recurring data job in D 365 3. Power automate app for the integration 1. Azure app for authentication Create an Azure app for authentication as follows:     Search for App registration on  https://portal.azure.com/ Create a new app: Copy the app id, we will need this for configuration in D 365 and flow. Create a new secret and copy it. Copy the value of the secret. Now we have all the required information from the Azure portal. Let us switch to Dynamics 365 F&O. 2. Data processing group and recurring data job in D 365 F&O Configure the Bank statement import setup using the steps mentioned in  https://docs.micros...
Read more

How to : get the Deep URL for a particular form with filter in Dynamics 365 Finance and Operations

Image
 If you are a developer and need to generate the Deep-link then you can follow the code mentioned in MS docs at https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/user-interface/create-deep-links But if you want to share a few URLs with filters already applied to other users then you can use the feature of  'Get a link' in D365FO. Suppose I want to share the list of all sales orders for customer 'US-001'. Go to Account Receivable -> Orders -> All sales orders  Filter by customer account 'US-001' Then in the navigation ribbon, go to Options and click on 'Get a link'   Copy all and paste the URL into new browser window. You will only see the sales orders for US-001. If you want to generate lot of deep URLs then you can also enable  'Automatically update query parameter' Once you enable this, as soon as you apply a filter on forms URL will be updated with the query parameters like below: You can copy and share the link directl...
Read more