Brewsterware

July 15, 2022

10.0.28 Data Entities for Finance & Operations

Filed under: 365 for Finance and Operations,Data Entities — Joe Brewer @ 3:44 pm

Sorry it’s taken a while to publish an updated version of the spreadsheet containing data entities and related metadata. I fully intend to release the code that I have written that does this in the future, once it has been cleaned up.

In the meantime, you can download the spreadsheet here. Please let me know what you think of this project.

2022-07-22 Update: The spreadsheet is now available in the en-US language. This version can be downloaded here.

March 11, 2022

postTargetProcess not being called on child entities in a composite entity

Filed under: 365 for Finance and Operations,Data Entities,Software — Joe Brewer @ 2:37 pm

If you have ever done any testing and development with data entities you may have noticed that the static method postTargetProcess() does not get called for child entities in a composite data entity. For a recent project I needed this method to be called on the SalesOrderHeaderChargeV2Entity and SalesOrderLineV2Entity when data is imported using the SalesOrdersV3 composite data entity is used to import sales orders.

(more…)

February 13, 2022

10.0.24 Data Entities for Finance & Operations

Filed under: 365 for Finance and Operations — Joe Brewer @ 7:22 pm

This blog post represents a challenge that I set myself a while ago to generate a list of data entities with underlying tables. What I ended up coding was a way to generate a spreadsheet that contains all sorts of data that would be useful for easily finding appropriate data entities for specific tasks. Need to find a data entity for Arrival Groups (ITMArrivalGroupTable) that works with data management? No problem! Need a data entity for Retail Transaction Addresses (RetailTransactionAddressTrans) that can be accessed using ODATA? Piece of cake! How about a list of all data entities that can use multi-threading? You got it!

(more…)

January 8, 2022

A look at the SysDa query API

Filed under: 365 for Finance and Operations — Joe Brewer @ 12:31 pm

A new query API was introduced in PU22 which enables a database query to be generated and executed through a lightweight framework. This post is not meant to be a comprehensive analysis of all functionality that this framework provides, rather a look at some of the functionality in order to encourage you to look deeper into the code to help you understand what it can and cannot do.

The official Microsoft documentation can be found here: Access data by using the SysDa classes – Finance & Operations | Dynamics 365 | Microsoft Docs. Of course the best documentation is the code itself and I would recommend using the “find references” and metadata search functionality to find examples and check for any best practice violations when writing your own code as there have been subtle changes changes to the API since it has been released.

The API, which is nicknamed SysDa, is reportedly faster at generating TSQL than the current Query api and also supports set based operations. Set based deletes using multiple are possible with this API which is particularly useful since the new “in” keyword that was introduced a while ago only currently supports enums.  

Microsoft are looking to replace select statements with this new api since it will make queries easier to extend – macros defining field lists or joins to inventDim will become a thing of the past. 

(more…)

October 10, 2021

Sys Operation framework template classes

Filed under: 365 for Finance and Operations — Tags: — Joe Brewer @ 6:04 pm

I’ve always thought that the dynamics community was super generous with the helpful blog posts. For years I would go back to this post whenever I had to implement classes for a sys operation framework process, and this post to remind me how to use reread, refresh and research. There are many awesome blogs out there with solutions for difficult problems whether they are common or not. I honestly don’t know where my career would be without them.

I hope this collection of classes, which an be used as a starting point for any sys operation framework process, will be useful for the community.

The following contract class demonstrates how to group and order parameters, add a query and add validation.

[
    DataContract,
    SysOperationContractProcessing(classStr(SysOpExampleUIBuilder)),
    SysOperationGroupAttribute('Group1', "label for group 1", '1'),
    SysOperationGroupAttribute('Group2', "label for group 2", '2')
]
class SysOpExampleContract extends SysOperationDataContractBase implements SysOperationValidatable
{
    private NoYes yesOrNo;
    private CustAccount custAccount;
    private str packedQuery;

    [
        DataMember,
        SysOperationLabel(literalStr("@SYS4082047")),
        SysOperationDisplayOrderAttribute('1'),
        SysOperationGroupMemberAttribute('Group1')
    ]
    public NoYes parmYesOrNo(NoYes _yesOrNo = yesOrNo)
    {
        yesOrNo = _yesOrNo;
        return yesOrNo;
    }

    [
        DataMember,
        SysOperationDisplayOrderAttribute('1'),
        SysOperationGroupMemberAttribute('Group2')
    ]
    public CustAccount parmCustAccount(CustAccount _custAccount = custAccount)
    {
        custAccount = _custAccount;
        return custAccount;
    }

    // substitute the query below with another as needed. Add ranges as needed to the query
    // to expose the fields on the batch job parameters form
    [
        DataMember,
        AifQueryType('_packedQuery', queryStr(smmCustTable))
    ]
    public str parmPackedQuery(str _packedQuery = packedQuery)
    {
        packedQuery = _packedQuery;
        return packedQuery;
    }

    public Query getQuery()
    {
        return new Query(SysOperationHelper::base64Decode(packedQuery));
    }

    public void setQuery(Query _query)
    {
        packedQuery = SysOperationHelper::base64Encode(_query.pack());
    }

    public boolean validate()
    {
        boolean isValid;

        isValid = true;

        return isValid;
    }
}

The following controller class demonstrates how to modify contract parameters before the batch dialog loads, how to force a batch job to run in batch and not interactively and how to create a task with a time delay.

[SysOperationJournaledParametersAttribute(true)] // this attribute allows a user to create a batch task from the batch tasks form
class SysOpExampleController extends SysOperationServiceController
{
    private static ClassDescription description()
    {
        return "description of the class";
    }

    protected void new(IdentifierName _className = '', IdentifierName _methodName = '', SysOperationExecutionMode _executionMode = SysOperationExecutionMode::Asynchronous)
    {
        IdentifierName parmClassName = _className != '' ? _className : classStr(SysOpExampleService);
        IdentifierName parmMethodName = _methodName != '' ? _methodName : methodStr(SysOpExampleService, run);

        super(parmClassName, parmMethodName, _executionMode);

        this.parmDialogCaption(SysOpExampleController::description());
    }

    // adding the method below will set the batch processing slider to "Yes" and disable it so that the process cannot be run interactively
    public boolean mustGoBatch()
    {
        return true;
    }

    public static SysOpExampleController newFromArgs(Args _args)
    {
        SysOpExampleController controller;
        SysOpExampleContract contract;

        controller = new SysOpExampleController();
    
        controller.initializeFromArgs(_args);

        contract = controller.getDataContractObject('_contract');

        contract.parmYesOrNo(NoYes::Yes);

        return controller;
    }

    public static void main(Args _args)
    {
        SysOpExampleController controller;

        controller = SysOpExampleController::newFromArgs(_args);
        controller.startOperation();
    }

    // the method below will create a batch task for this controller that will execute in 30 seconds
    public static void startWithDelay(Args _args)
    {
        const int DelayInSeconds = 30;
        SysOpExampleController controller;
        BatchHeader batchHeader;
        utcDateTime delayedStartTime;
        utcDateTime systemDateTime;

        controller = SysOpExampleController::newFromArgs(_args);
        controller.parmShowDialog(false);
        controller.parmLoadFromSysLastValue(false);

        systemDateTime = DateTimeUtil::getSystemDateTime();
        delayedStartTime = DateTimeUtil::addSeconds(systemDateTime, DelayInSeconds);

        batchHeader = BatchHeader::construct();

        batchHeader.parmCaption("Add a unique caption here (based on values in _args) so that the batch job can be easily found in the batch jobs form");

        // note that the two lines below will force a notification to the user on the job status when it has finished
        batchHeader.parmStartDateTime(delayedStartTime);
        batchHeader.parmAlerts(NoYes::Yes, NoYes::Yes, NoYes::Yes, NoYes::Yes, NoYes::No);
        
        batchHeader.addTask(controller);

        batchHeader.save();
    }
}

The following service class demonstrates how to use the RunbaseProgress class to let the user know the task progress and exception handing (including transient sql errors)

class SysOpExampleService extends SysOperationServiceBase
{
    private static int defaultMaxRetryOfTransientSqlConnectionError = 5;

    public void run(SysOpExampleContract _contract)
    {
        #OCCRetryCount
        RunbaseProgress progress;
        CustTable custTable;
        Query query;
        QueryRun queryRun;
        int64 totalRecords;

        try
        {
            ttsbegin;

            query = _contract.getQuery();
            queryRun = new QueryRun(query);

            totalRecords = SysQuery::countTotal(queryRun);

            // this progress class will update the SysProgress table in a separate transaction which
            // will be shown on the "Batch tasks" form - very useful for long running processes in batch :)
            progress = this.getProgressController(_contract);
            progress.setTotal(totalRecords);

            while (queryRun.next())
            {
                custTable = queryRun.get(tableNum(CustTable));

                // perform some logic on CustTable here...

                progress.incCount();
            }

            ttscommit;
        }
        catch (Exception::Deadlock)
        {
            retry;
        }
        catch (Exception::UpdateConflict)
        {
            if (appl.ttsLevel() == 0)
            {
                if (xSession::currentRetryCount() >= #RetryNum)
                {
                    throw Exception::UpdateConflictNotRecovered;
                }
                else
                {
                    retry;
                }
            }
            else
            {
                throw Exception::UpdateConflict;
            }
        }
        catch (Exception::DuplicateKeyException)
        {
            if (appl.ttsLevel() == 0)
            {
                if (xSession::currentRetryCount() >= #RetryNum)
                {
                    throw Exception::DuplicateKeyExceptionNotRecovered;
                }
                else
                {
                    retry;
                }
            }
            else
            {
                throw Exception::DuplicateKeyException;
            }
        }
        catch (Exception::TransientSqlConnectionError)
        {
            if (SysOpExampleService::retryTransientSqlConnectionError(3))
            {
                retry;
            }
            else
            {
                throw;
            }
        }
        catch
        {
            // this line will catch any exceptions that are thrown and not caught by the catch blocks above
            // and will show the line of the code that threw the exception in the debugger if the debugger is attached
            // If the debugger is not attached, the stack trace will be written to the event log - very useful
            // for troubleshooting intermittent issues
            Debug::assert(false);
        }
        finally
        {
            // any cleanup logic should go here..
        }
    }

    // the methods below would be better suited in a separate helper class so that they can be used with other batch jobs
    static boolean retryTransientSqlConnectionError(int _maxRetryOfTransientSqlConnectionError)
    {
        var currentRetryCount = xSession::currentRetryCount();
        var shouldRetry = currentRetryCount < _maxRetryOfTransientSqlConnectionError;

        if (shouldRetry)
        {
            SysOpExampleService::delayRetry(currentRetryCount);
        }

        return shouldRetry;
    }

    static internal void delayRetry(int _retryCount)
    {
        var delay = 5000 * power(2, min(_retryCount, 5));
        sleep(min(60 * 1000, delay));
    }
}

The following User Interface Builder class demonstrates how to add custom lookup methods and modified events for methods in the contract class

class SysOpExampleUIBuilder extends SysOperationAutomaticUIBuilder
{
    private SysOpExampleContract contract;
    private DialogField dlgFldYesOrNo;
    private DialogField dlgFldCustAccount;

    public void postBuild()
    {
        super();

        contract = this.dataContractObject();

        dlgFldYesOrNo = this.bindInfo().getDialogField(contract, methodStr(SysOpExampleContract, parmYesOrNo));
        dlgFldCustAccount = this.bindInfo().getDialogField(contract, methodStr(SysOpExampleContract, parmCustAccount));

        dlgFldYesOrNo.registerOverrideMethod(
            methodStr(FormCheckBoxControl, modified), 
            methodStr(SysOpExampleUIBuilder, yesOrNoModified), 
            this);

        dlgFldCustAccount.registerOverrideMethod(
            methodStr(FormStringControl, lookup), 
            methodStr(SysOpExampleUIBuilder, lookupCustomer), 
            this);
    }

    public boolean yesOrNoModified(FormCheckBoxControl _control)
    {
        dlgFldCustAccount.enabled(_control.value());

        return true;
    }

    public void lookupCustomer(FormStringControl _control)
    {
        SysTableLookup sysTableLookup;

        sysTableLookup = SysTablelookup::newParameters(tableNum(CustTable), _control, true);
        sysTableLookup.addLookupfield(fieldNum(CustTable, AccountNum));
        sysTableLookup.addLookupMethod(tableMethodStr(CustTable, name));

        sysTableLookup.performFormLookup();
    }
}
Older Posts »

Powered by WordPress