Get Infolog as a string

/// <summary>
/// Gets infolog content
/// </summary>
/// <returns>infolog string</returns>
public static str getErrorStr(container _cont)
{
	SysInfologEnumerator        enumerator;
	SysInfologMessageStruct     myStruct;
	Exception                   exception;
	str                         error;

	enumerator = SysInfologEnumerator::newData(_cont);

	while (enumerator.moveNext())
	{
		myStruct = new SysInfologMessageStruct(enumerator.currentMessage());
		exception = enumerator.currentException();

		error = strfmt('%1 %2', error, myStruct.message());
	}

	return error;
}

Code Review

Disclaimer: this is just a short recap of what Martin Dráb explained in his article https://dev.goshoom.net/2012/08/purpose-of-code-reviews/.

Purpose of code reviews

• Internal quality:

1. Overall approach – does the code do the right thing by proper means? 
2. Maintainability/extensibility – are we able to easily extend the solution, fix bugs etc.? 
3. Testability – are we able to test the code?
4. Documentation – are we able to understand why the code was made and what it does?
5. Performance – is the design correct from performance perspective? 

• Functional testing: Some bugs are much more visible when looking into code
• Knowledge sharing: Discussions between the developer and the reviewer

Tools

• BP check 
• xppbp.exe
• DevOps code review request

Top 10 issues discovered from Dynamics AX Code Review

https://www.linkedin.com/pulse/top-10-issues-discovered-from-dynamics-ax-code-review-caillet/

Union query for Project transactions

Union query may be a very efficient and useful option when you need to fetch similar data fields from different tables. A good example of this may be the case when you to gather information about project related transactions as they can be of different types, like, expenses, fees, items, etc. 

Say, we need to render some report collecting all these different types of transaction that can be posted against a given project and group them by a given financial dimension value in Project group. Let's see how Union query can help us.

In order to better understand the goal, you can take a look to the standard form ProjInvoiceJournal which perfectly explains how all these table relate to each other. 

Most of them contain the same set of fields, and we simply need to get some of them into one view to populate by the report data provider.

So, at the first step, let's create all necessary queries for the source Project transaction related tables. Pick up the first and create a simple query as depicted. 

Then create a view based on this query. 

Then pick up the next and do the same.

Complete these two steps for all necessary transaction types.

Now you can create a Union query and add all your views together as they have the same set of fields.

If you need to distinguish them later who is who, say, in computed column methods, you can use  unionAllBranchId field, but this is out of the current focus.

Finally you can elaborate it by adding a new view based on the latter with a simple SUM aggregation for Amount field.

Basically, you achieve your goal with no coding.

I am not going into details about the whole project, which you can get by this URL https://github.com/wojzeh/tmxProjectSalesPerSegment. Ping me, if you have any questions.

Related topics: 

Computed column for union values from multiple outer joined data sources in view

D365FO: SysMultiTableLookup - dynamic lookups based on multiple tables

Yes, guys, sometimes they come back! 

Fourteen years ago Vania Kashperuk created a class which became classics: SysMultiTableLookup. It made possible to create lookups based on multiple tables. Some people are still looking for it in vain among the standard classes. So I decided to create a new version of it for D365FO.

It combines supporting for both lookup and lookupReference methods: performFormLookup and performFormReferenceLookup.

Vania added a tutorial form to his class, so did I. There is a form in the project, which shows how unbounded form controls can be looked up with fields and display methods from joined tables as well as the case with aggregation and a CustTable data source to demonstrate a lookup for a reference group.

Take note that I did not implement resolving and have not tested it thoroughly, neither.

Feel free to use it and elaborate at your own risk. 

The whole project tmxSysMultiTableLookup is here in https://github.com/wojzeh/tmxSystemTools

The only class is here

/// <summary>
/// Multi table lookup. D365FO version based on the original by Vanya Kashperuk
/// http://kashperuk.blogspot.com/2008/09/sysmultitableloookup-dynamic-lookups.html
/// </summary>
public class tmxSysMultiTableLookup extends SysTableLookup
{
    FormBuildInt64Control   recIdSelectModeControl;
    boolean                 isReference;
    /// <summary>
    /// Is reference lookup
    /// </summary>
    /// <param name = "_parm">boolean</param>
    /// <returns>True if Reference</returns>
    protected boolean parmIsReference(boolean _parm = isReference)
    {
        isReference = _parm;
        return isReference;
    }

    /// <summary>
    /// Places the specified <paramref name="formRun" /> form into the correct lookup selection mode.
    /// </summary>
    /// <param name="formRun">
    /// The form to be put into lookup selection mode.
    /// </param>
    protected void setSelectMode(FormRun _formRun)
    {
        if(isReference)
        {
            if (!recIdSelectModeControl)
            {
                throw(error(Error::wrongUseOfFunction(funcname())));
            }

            _formRun.selectRecordMode(_formRun.control(recIdSelectModeControl.id()));
        }
        else
        {
            super(_formRun);
        }
    }

    /// <summary>
    /// Constructs <C>tmxSysMultiTableLookup</C> for a formcontrol with a given query
    /// </summary>
    /// <param name = "_callingControl">Target form Reference group control</param>
    /// <param name = "_query">Query</param>
    /// <param name = "_useLookupValue">Use input value</param>
    /// <param name = "_referenceResolver">AmbiguousReferenceResolver</param>
    /// <returns>Instance of <C>tmxSysMultiTableLookup</C></returns>
    static tmxSysMultiTableLookup newReferenceParameters(FormReferenceControl _callingControl, Query _query, boolean _useLookupValue = true)
    {
        tmxSysMultiTableLookup sysTableLookup = tmxSysMultiTableLookup::construct();

        if(_query.dataSourceCount() < 1)
        {
            throw error(@"Query needs to be defined before building the lookup");
        }
        sysTableLookup.parmIsReference(true);
        sysTableLookup.parmQuery(_query);
        sysTableLookup.parmCallingControl(_callingControl);
        sysTableLookup.parmUseLookupValue(_useLookupValue);
        
        return sysTableLookup;
    }

    /// <summary>
    /// Constructs <C>tmxSysMultiTableLookup</C> for a formcontrol with a given query
    /// </summary>
    /// <param name = "_callingControl">Target form control</param>
    /// <param name = "_query">Query</param>
    /// <param name = "_useLookupValue">Use input value</param>
    /// <param name = "_referenceResolver">AmbiguousReferenceResolver</param>
    /// <returns>Instance of <C>tmxSysMultiTableLookup</C></returns>
    static tmxSysMultiTableLookup newParameters(FormControl _callingControl, Query _query, boolean _useLookupValue = true)
    {
        tmxSysMultiTableLookup sysTableLookup = tmxSysMultiTableLookup::construct();

        if(_query.dataSourceCount() < 1)
        {
            throw error(@"Query needs to be defined before building the lookup");
        }
        sysTableLookup.parmQuery(_query);
        sysTableLookup.parmCallingControl(_callingControl);
        sysTableLookup.parmUseLookupValue(_useLookupValue);
        
        return sysTableLookup;
    }

    /// <summary>
    /// Instantiates <C>tmxSysMultiTableLookup</C>
    /// </summary>
    /// <returns>tmxSysMultiTableLookup</returns>
    static tmxSysMultiTableLookup construct()
    {
        return new tmxSysMultiTableLookup();
    }

    /// <summary>
    /// Adds form control
    /// </summary>
    /// <param name = "_dataSourceNo">Datasource number in the query</param>
    /// <param name = "_fieldId">Field num</param>
    /// <param name = "_returnItem">Return item</param>
    /// <param name = "_methodName">Method name</param>
    /// <param name = "_label">Label</param>
    void addLookupControlMulti(int _dataSourceNo, fieldId _fieldId, boolean _returnItem, str _methodName, str _label)
    {
        lookupItems += [[_dataSourceNo, _fieldId, _returnItem, _methodName]];
        if (_label)
        {
            this.setLabel(_label);
        }
    }

    /// <summary>
    /// Adds field as form control
    /// </summary>
    /// <param name = "_methodName">Method name</param>
    /// <param name = "_dataSourceNo">Datasource number in the query</param>
    /// <param name = "_label">Label</param>
    void addLookupMethodMulti(str _methodName, int _dataSourceNo = 1, str _label ="")
    {
        this.addLookupControlMulti(_dataSourceNo, 0, false, _methodName, _label);
    }

    /// <summary>
    /// Adds field as form control
    /// </summary>
    /// <param name = "_dataSourceNo">Datasource number in the query</param>
    /// <param name = "_fieldId">Field num</param>
    /// <param name = "_returnItem">Return item</param>
    /// <param name = "_label">Label</param>
    void addLookupFieldMulti(fieldId _fieldId, int _dataSourceNo = 1, boolean _returnItem = false, str _label = '')
    {
        if (_dataSourceNo > query.dataSourceCount())
        {
            throw error(Error::wrongUseOfFunction(funcName()));
        }

        this.addLookupControlMulti(_dataSourceNo, _fieldId, _returnItem, '', _label);
    }

    /// <summary>
    /// Chooses LinkType appropriate to JoinMode
    /// </summary>
    /// <param name = "_joinMode">JoinMode</param>
    /// <returns>FormLinkType</returns>
    protected FormLinkType joinMode2LinkType(JoinMode _joinMode)
    {
        switch (_joinMode)
        {
            case JoinMode::InnerJoin:
                return FormLinkType::InnerJoin;
            case JoinMode::OuterJoin:
                return FormLinkType::OuterJoin;
            case JoinMode::ExistsJoin:
                return FormLinkType::ExistJoin;
            case JoinMode::NoExistsJoin:
                return FormLinkType::NotExistJoin;
        }

        throw error(Error::wrongUseOfFunction(funcName()));
    }

    /// <summary>
    /// Configures the data source of the lookup form.
    /// </summary>
    /// <param name = "_formBuildDataSource">FormBuildDataSource</param>
    /// <param name = "_queryBuildDataSource">QueryBuildDataSource</param>
    protected void configureLookupDataSourceMulti(FormBuildDataSource _formBuildDataSource, QueryBuildDataSource _queryBuildDataSource)
    {
        this.configureLookupDataSource(_formBuildDataSource);
        if(_queryBuildDataSource.embedded()) //joined() or level() > 1
        {
            _formBuildDataSource.linkType(this.joinMode2LinkType(_queryBuildDataSource.joinMode()));
            _formBuildDataSource.joinSource(_queryBuildDataSource.parentDataSource().name());
        }
    }

    /// <summary>
    /// Builds grid form control
    /// </summary>
    /// <param name = "_formBuildGridControl">FormBuildGridControl</param>
    /// <param name = "_form">Form</param>
    /// <returns>FormBuildGridControl</returns>
    protected FormBuildGridControl buildGridMulti(FormBuildGridControl _formBuildGridControl, Form _form)
    {
        FormBuildDataSource             formBuildDataSource;
        FormBuildControl                formBuildControl;
        Object                          obj;
        boolean                         returnItem;
        str                             method;
        int                             fieldId;
        int                             i;
        boolean                         lookupFieldSet = false;
        int                             dataSourceNo;
        fieldId                         recIdFieldId;
        boolean                         firstDS = true;
        // add fields and display methods for all given data sources
        for (i = 1; i <= conlen(lookupItems); i++)
        {
            [dataSourceNo, fieldId, returnItem, method] = conpeek(lookupItems, i);

            if(firstDS)
            {
                firstDS = false;
                // for the reference case
                if(isReference)
                {
                    recIdFieldId = fieldnum(Common, RecId);
                    if (recIdFieldId)
                    {
                        // Always add a hidden RecId control to add as the selectMode field.
                        recIdSelectModeControl = _formBuildGridControl.addDataField(_form.dataSource(dataSourceNo).id(), recIdFieldId);
                        recIdSelectModeControl.visible(false);
                    }
                }
            }

            if (fieldId)
            {
                // Add control for a field.
                formBuildControl = _formBuildGridControl.addDataField(_form.dataSource(dataSourceNo).id(), fieldId);
            }
            else
            {
                // Add control for a display method.
                formBuildControl = this.addMethodControl(_formBuildGridControl, _form.dataSource(dataSourceNo), method, i);
            }

            if(!formBuildControl)
            {
                throw error("@SYS72176");
            }
            //
            // Labels could be specified for both fields and display methods, therefore
            // they must be checked for every element in lookupItems collection.
            //
            if (labels[i])
            {
                obj = formBuildControl;
                obj.label(labels[i]);
            }
            //
            // An index of return field and control must be set according to these rules:
            // - If return item is specified for a field or a display method, then this field or display method
            // is considered as the return item.
            // - If return item is not specified at all, then the first field (but not method) is considered
            // as the return item.
            // Currenly too much code is relying on the second rule; therefore it cannot be changed.
            //
            if (!lookupFieldSet && fieldId      // If lookup field is not set yet AND current item is a field (not a display method)
                || returnItem)                  // Or if returnItem is explicitly specified for the field or for the display method
            {
                controlIdx      = formBuildControl.id();
                lookupField     = fieldId;
                lookupFieldSet  = true;
            }
        }

        return _formBuildGridControl;
    }

    /// <summary>
    ///    Constructs the form to use as the lookup form.
    /// </summary>
    /// <returns>
    ///    The form to use as the lookup form.
    /// </returns>
    protected FormRun formRun()
    {
        DictTable                       dictTable;
        DictField                       dictField;
        Form                            form = new Form();
        FormRun                         formRun;
        FormBuildDesign                 formBuildDesign;
        FormBuildDataSource             formBuildDataSource;
        FormDataSource                  formDataSource;
        FormBuildGridControl            formBuildGridControl;
        Args                            args;
        int                             idx;
        int                             i;
        FormStringControl               callingStringControl;
        QueryBuildDataSource            queryBuildDataSource;
        boolean                         firstDS = true;

        form.name(identifierstr(TemporaryFormName));

        for (i = 1; i <= query.dataSourceCount(); i++)
        {
            queryBuildDataSource = query.dataSourceNo(i);
            formBuildDataSource = form.addDataSource(queryBuildDataSource.name());
            formBuildDataSource.table(queryBuildDataSource.table());
            this.configureLookupDataSourceMulti(formBuildDataSource, queryBuildDataSource);

            if(firstDS)
            {
                firstDS = false;
                if(isReference)
                {
                    // Require that all fields be selected so that Reference Data sources are updated
                    // correctly.
                    formBuildDataSource.onlyFetchActive(false);
                }
            }
        }

        formBuildDesign = form.addDesign('Design');
        this.buildFormDesign(formBuildDesign);

        formBuildGridControl = formBuildDesign.addControl(FormControlType::Grid,'Grid');
        formBuildGridControl.dataSource(query.dataSourceNo(1).name());
        this.buildFormGridDesign(formBuildGridControl);

        idx = formBuildGridControl.id();

        this.buildGridMulti(formBuildGridControl, form);
        this.buildControlsFinal(formBuildDesign);

        if (lookupField)
        {
            dictField = new dictField(tableId, lookupField);
        }

        args = new Args();
        args.object(form);
        args.callerFormControl(callingControl);
        this.buildArgs(args);
        //highlighting existing value doesn't work with temp tables
        if(useLookupValue && !tmpBuffer)
        {
            // Let the full kernel-generated lookup process take over.
            if (callingControl is FormReferenceControl)
            {
                if (query != null)
                {
                    formRun = FormAutoLookupFactory::buildReferenceLookupFromCustomForm(callingControl as FormReferenceControl, form, args, query);
                }
                else
                {
                    formRun = FormAutoLookupFactory::buildReferenceLookupFromCustomForm(callingControl as FormReferenceControl, form, args);
                }
            }
            else if (dictField != null)
            {
                if (query != null)
                {
                    formRun = FormAutoLookupFactory::buildLookupFromCustomForm(callingControl, form, AbsoluteFieldBinding::construct(dictField.name(), dictTable.name()), args, query);
                }
                else
                {
                    formRun = FormAutoLookupFactory::buildLookupFromCustomForm(callingControl, form, AbsoluteFieldBinding::construct(dictField.name(), dictTable.name()), args);
                }
            }

            // The kernel will apply the necessary filter so don't allow this instance to apply a filter
            // for the lookup control's current value.
            skipApplicationOfFilter = true;

            if (formRun)
            {
                formDataSource  = formRun.dataSource(1);
            }
        }

        if (formRun == null)
        {
            // Use a watered down lookup that doesn't provide initial positioning.
            formRun = classfactory.formRunClass(args);
            formRun.init();

            formDataSource  = formRun.dataSource(1);

            // Set the custom Query before setting the selectTarget or selectMode
            // as those methods cause Query normalization logic to be run in the kernel.
            // (e.g., joins are added as required by the target control).
            if (query)
            {
                formDataSource.query(query);
            }

            formRun.selectTarget(this.parmCallingControl());
        }

        form = formRun.form();
        formGridControl = formRun.control(idx);
        formGridControl.setFocus();

        if (tmpBuffer)
        {
            if (query.dataSourceCount() > 1)
            {
                throw error("Multiple temporary datasource lookups are not supported");
            }

            formDataSource.init();
            //BP deviation documented
            formDataSource.cursor().setTmp();
            formDataSource.cursor().setTmpData(tmpBuffer);
        }

        this.setSelectMode(formRun);

        this.buildSelectionListMulti(formDataSource.query());

        this.addFilter(formRun);

        return formRun;
    }

    /// <summary>
    /// Creates selection list from a given query
    /// </summary>
    /// <param name = "_query">Query</param>
    protected void buildSelectionListMulti(Query _query)
    {
        boolean                 returnItem;
        str                     method;
        int                     fieldId;
        int                     i;
        int                     dataSourceNo;

        SelectionField  fieldKind()
        {
            int                 j;
            QueryBuildFieldList qbfl;

            qbfl = _query.dataSourceNo(dataSourceNo).fields();
            for (j = 1; j <= qbfl.fieldCount(); j++)
            {
                if (qbfl.field(j) == fieldId)
                {
                    return qbfl.fieldKind(j);
                }
            }
            return SelectionField::Database;
        }

        
        for (i = 1; i <= conlen(lookupItems); i++)
        {
            [dataSourceNo, fieldId, returnItem, method] = conpeek(lookupItems, i);
            // FieldId could be 0 if current element represents a display method used
            // to return values for a lookup column.
            // If fieldId is 0 then there is no sense to add it to the selection list of the query.
            if (fieldId)
            {
                _query.dataSourceNo(dataSourceNo).addSelectionField(fieldId, fieldKind());
            }
        }
    }

    /// <summary>
    /// Obsolete
    /// </summary>
    /// <param name = "_fieldId"></param>
    /// <param name = "_returnItem"></param>
    [SysObsolete('This method is not used for multi table lookup. Use addLookupfieldMulti instead.', true)]
    public void addLookupfield(FieldId _fieldId, boolean _returnItem = false)
    {
    }

    /// <summary>
    /// Obsolete
    /// </summary>
    /// <param name = "_method"></param>
    /// <param name = "_returnItem"></param>
    [SysObsolete('This method is not used for multi table lookup. Use addLookupMethodMulti instead.', true)]
    public void addLookupMethod(str _method, boolean _returnItem = false)
    {
    }

    /// <summary>
    /// Obsolete
    /// </summary>
    /// <param name = "_fieldId"></param>
    /// <param name = "_returnItem"></param>
    /// <param name = "_methodName"></param>
    [SysObsolete('This method is not used for multi table lookup. Use addLookupControlMulti instead.', true)]
    public void addLookupControl(FieldId _fieldId, boolean _returnItem, str _methodName)
    {
    }

    /// <summary>
    /// Obsolete
    /// </summary>
    /// <param name = "_formBuildGridControl"></param>
    /// <param name = "_formBuildDataSource"></param>
    /// <returns></returns>
    [SysObsolete('This method is not used for multi table lookup. Use buildGridMulti instead.', true)]
    protected FormBuildGridControl buildGrid(FormBuildGridControl _formBuildGridControl, FormBuildDataSource _formBuildDataSource)
    {
        return null;
    }

    /// <summary>
    /// Obsolete
    /// </summary>
    /// <param name = "_queryBuildDataSource"></param>
    [SysObsolete('This method is not used for multi table lookup. Use buildSelectionListMulti instead.', true)]
    protected void buildSelectionList(QueryBuildDataSource _queryBuildDataSource)
    {
    }

    /// <summary>
    /// Presents the lookup form to the user.
    /// </summary>
    /// <returns>
    /// The selected record.
    /// </returns>
    public Common performFormReferenceLookup()
    {
        FormReferenceControl    referenceControl;
        FormRun                 formRun;
        Common                  selectedRecord;

        referenceControl = callingControl as FormReferenceControl;

        if (!referenceControl)
        {
            throw(error(Error::wrongUseOfFunction(funcname())));
        }

        formRun = this.formRun();

        referenceControl.performFormLookup(formRun);

        selectedRecord = formRun.selectRecordModeSelectedRecord();

        return selectedRecord;
    }

}

How to write to an event log from inside of a transaction

 

// to write your log from inside of another transaction
	static public void insertExtEventLogInSeparateConnection(RefRecId _lineRecId, str _guid, str _logSource, str _logStr)
    {
        ExtEventLog         log;
        UserConnection      connection;
        int                 ttsLevel    = appl.ttsLevel(); //here you can check if you are inside of a transaction
        str                 errMsg      = strFmt("Cannot add event log '%1:%2:%3'", _guid, _logSource, _logStr);
		// let's create a separate connection
        try
        {
            connection = new UserConnection();
            connection.ttsbegin();
            log.setConnection(connection);
            log.InstructionDocLineRecId = _lineRecId;
            log.TaskGUID                = _guid;
            log.LogStr                  = _logStr;
            log.LogSource               = _logSource;

            log.doInsert();
            connection.ttscommit();
        }
        catch
        {
            throw error(errMsg);
        }
        finally
        {
            if(connection)
            {
                connection.finalize();
            }
        }
    }

How to move AX2012 attachment files to SharePoint while upgrading database to D365FO

Problem

When it comes to upgrading attachments from AX2012 to D365FO, only URLs, notes, and files saved in the former's database may be transferred to the Azure Blob Storage (on-cloud). 

Therefore, all files from attachments in AX2012 should be moved to the database first in order to be successfully migrated to D365FO.

What if there are millions of them? Technically speaking it might be a good option to save them on SharePoint (on-cloud); however, unfortunately, such attachments links will be deleted, too.

You can find more detail on Document management in D365FO in https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/organization-administration/configure-document-management

Technical details

During the Ax2012-D365FO database upgrade process, among other standard classes ('scripts') ReleaseUpdateDB72_Docu is triggered, which actually deletes records in DocuValue and related tables for all files not saved in the data base including even those referenced on a local SharePoint server.

Solution

As a solution we can move all external files referenced in AX2012 attachments to a on-cloud SharePoint server first

Then an extension to the aforementioned class must be triggered during the standard DB upgrade procedure; so that it would keep existing links and update them accordingly to a new SharePoint folder structure.

For example, we can agree that existing local folders will be reproduced on the SharePoint Server instance.

The following code must be adapted accordingly to your landscape and tested first on a small set of files in a dev environment.

Please, use it at your own risk.

[ExtensionOf(classStr(ReleaseUpdateDB72_Docu))
final class myReleaseUpdateDB72_Docu_Extension
{
    public const str myLegacy          = 'Legacy';
    public const int myActionClassId   = 118; //DocuActionURLClassId
    public const str myName            = 'Legacy attachments for ';
    public const str myHost            = 'myCompany.sharepoint.com';
    public const str mySite            = '/sites/D365FOFileShare';

    public const str myUpgradeModifiedUser = 'myAxDocUpgradeUser'; // fake user for marking records
    public const int myMaxRowsToUpdatePerStatement = 10000;

    public const str myPart1 = "https://myCompany.sharepoint.com/sites/D365FOFileShare/Legacy/";
    public const str myPart2 = "https://myCompany.sharepoint.com/sites/D365FOFileShare/_api/Web/GetFileByServerRelativePath(decodedurl=''/sites/D365FOFileShare/Legacy/";
    public const str myPart3 = "'')";
    public const str myPart4 = "/";
    public const str myPart5 = ".";
    public const str myPart6 = "''";

    /// <summary>
    /// Sets a special value to File field to avoid dropping these records by standard script
    /// To be run BEFORE the script
    /// </summary>
    private void myPreUpdateDocuValue_CorrectFileLocations()
    {
        SysDictTable    docuValueTable = new SysDictTable(tableNum(DocuValue));
        SysDictTable    docuRefTable = new SysDictTable(tableNum(DocuRef));
        SysDictTable    docuTypeTable = new SysDictTable(tableNum(DocuType));
        str             sqlQuery;

        Connection connection = new Connection();
        try
        {
            int impactedRows;

            // First update all DocuValues with null files and empty path
            // these files are placed in network shared folders and must be retargeted to SharePoint server
            // with setting FILE to a dummy values so that the standard next() won't delete them
            do
            {
                    sqlQuery =
                    strFmt(@"
                    UPDATE TOP (%8) docValue
                    SET docValue.%6 = CAST('%1' AS VARBINARY)
                    FROM %2 docValue
                    JOIN %3 docRef ON docRef.%7 = docValue.%5 
                    JOIN %11 docType ON (docRef.%12 = docType.%13 and  docRef.%14 = docType.%15)
                    AND docValue.%6 IS NULL AND docValue.%9 ='' AND docValue.%10 = 0 AND docType.%16 = 0",
                    myUpgradeModifiedUser,                                                     // %1 - upgrade modified user 
                    docuValueTable.name(DbBackend::Sql),                                        // %2 - DocuValue
                    docuRefTable.name(DbBackend::Sql),                                          // %3 - DocuRef
                    docuValueTable.fieldName(fieldNum(DocuValue, ModifiedBy), DbBackend::Sql),  // %4 - DocuValue.ModifiedBy
                    docuValueTable.fieldName(fieldNum(DocuValue, RecId), DbBackend::Sql),       // %5 - DocuValue.RecId
                    docuValueTable.fieldName(fieldNum(DocuValue, File), DbBackend::Sql),        // %6 - DocuValue.File
                    docuRefTable.fieldName(fieldNum(DocuRef, ValueRecId), DbBackend::Sql),      // %7 - DocuRef.ValueRecId
                    myMaxRowsToUpdatePerStatement,                                             // %8 - Max rows to update per statement
                    docuValueTable.fieldName(fieldNum(DocuValue, PATH), DbBackend::Sql),        // %9 - DocuRef.PATH
                    docuValueTable.fieldName(fieldNum(DocuValue, Type), DbBackend::Sql),        // %10 - DocuValue.Type
                    docuTypeTable.name(DbBackend::Sql),                                         // %11 - docuTypeTable
                    docuRefTable.fieldName(fieldNum(DocuRef, TypeId), DbBackend::Sql),          // %12 - DocuRef.TypeId
                    docuTypeTable.fieldName(fieldNum(DocuType, TypeId), DbBackend::Sql),        // %13 - DocuType.TypeId
                    docuRefTable.fieldName(fieldNum(DocuRef,ACTUALCOMPANYID), DbBackend::Sql),  // %14 - DocuType.ACTUALCOMPANYID
                    docuTypeTable.fieldName(fieldNum(DocuType, DATAAREAID), DbBackend::Sql),    // %15 - DocuType.DATAAREAID
                    docuTypeTable.fieldName(fieldNum(DocuType, FILEPLACE), DbBackend::Sql)      // %16 - DocuType.FILEPLACE
                    );       

                impactedRows = this.myExecuteSQL(sqlQuery, connection);
            }
            while (impactedRows == myMaxRowsToUpdatePerStatement);

        }
        finally
        {
            connection.finalize();
        }
    }

    /// <summary>
    /// Nulls FILE field back and updates other field to keep SharePoint links correctly
    /// To be run AFTER the script
    /// </summary>
    private void myPostUpdateDocuValue_CorrectFileLocations()
    {
        SysDictTable    docuValueTable = new SysDictTable(tableNum(DocuValue));
        SysDictTable    docuRefTable = new SysDictTable(tableNum(DocuRef));
        SysDictTable    docuTypeTable = new SysDictTable(tableNum(DocuType));
        str             sqlQuery;

        Connection connection = new Connection();
        try
        {
            int impactedRows;

            // First update all premarked DocuRef with the new SharePoint docuType
            do
            {
                    sqlQuery =
                    strFmt(@"
                    UPDATE TOP (%8) docRef
                    SET 
                    docRef.%17 = '%21' + '_' + docRef.%18
                    FROM %3  docRef
                    JOIN %2 docValue ON docRef.%7 = docValue.%5 AND docValue.%6 = CAST('%1' AS VARBINARY) and docRef.%17 <> '%21' + '_' + docRef.%18",
                    myUpgradeModifiedUser,                                                      // %1 - upgrade modified user 
                    docuValueTable.name(DbBackend::Sql),                                        // %2 - DocuValue
                    docuRefTable.name(DbBackend::Sql),                                          // %3 - DocuRef
                    docuValueTable.fieldName(fieldNum(DocuValue, Type), DbBackend::Sql),        // %4 - DocuValue.Type
                    docuValueTable.fieldName(fieldNum(DocuValue, RecId), DbBackend::Sql),       // %5 - DocuValue.RecId
                    docuValueTable.fieldName(fieldNum(DocuValue, File), DbBackend::Sql),        // %6 - DocuValue.File
                    docuRefTable.fieldName(fieldNum(DocuRef, ValueRecId), DbBackend::Sql),      // %7 - DocuRef.ValueRecId
                    myMaxRowsToUpdatePerStatement,                                             // %8 - Max rows to update per statement
                    docuValueTable.fieldName(fieldNum(DocuValue, PATH), DbBackend::Sql),        // %9 - DocuRef.PATH
                    docuValueTable.fieldName(fieldNum(DocuValue, StorageProviderId), DbBackend::Sql),       // %10 - DocuRef.StorageProviderId
                    docuValueTable.fieldName(fieldNum(DocuValue, AccessInformation), DbBackend::Sql),      // %11 - DocuRef.AccessInformation
                    myPart1,                                                                       // %12 - https://myCompany.sharepoint.com/sites/D365FOFileShare/
                    myPart2,                                                                       // %13 - https://myCompany.sharepoint.com/sites/D365FOFileShare/api/Web/GetFileByServerRelativePath(decodedurl='/sites/D365FOFileShare/
                    myPart3 ,                                                                      // %14 - ')
                    myPart4 ,                                                                      // %15 - /
                    myPart5 ,                                                                      // %16 - .
                    docuRefTable.fieldName(fieldNum(DocuRef, TYPEID), DbBackend::Sql),              //%17 - DocuRef.TypeId
                    docuRefTable.fieldName(fieldNum(DocuRef, ACTUALCOMPANYID), DbBackend::Sql),     //%18 - 'SPND'
                    docuValueTable.fieldName(fieldNum(DocuValue, FILENAME), DbBackend::Sql),        // %19 - DocuValue.FILENAME
                    docuValueTable.fieldName(fieldNum(DocuValue, FILETYPE), DbBackend::Sql),        // %20 - DocuValue.FILETYPE
                    myLegacy                                                                       // %21 - 'Legacy'
                    );       
                impactedRows = this.myExecuteSQL(sqlQuery, connection);
            }
            while (impactedRows == myMaxRowsToUpdatePerStatement);

            impactedRows = 0;

            // Now update all premarked DocuValues with new paths and unmark them
            do
            {
                sqlQuery =
                    strFmt(@"
                    UPDATE TOP (%8) docValue
                    SET
                    docValue.%6 = NULL,
                    docValue.%4 = 0,
                    docValue.%10 = 2,
                    docValue.%9  = '%12'+ docRef.%18 + '%15'+ docValue.%19+'%16' + docValue.%20,
                    docValue.%11 = '%13'+ + docRef.%18 + '%15'+ docValue.%19+'%16' + docValue.%20 + '%14'
                    FROM %2 docValue
                    JOIN %3 docRef ON docRef.%7 = docValue.%5 AND docValue.%6 = CAST('%1' AS VARBINARY)",
                    //@myPart1 + dr.ACTUALCOMPANYID + @myPart4 + dv.FILENAME+ @myPart5 + dv.FILETYPE
                    myUpgradeModifiedUser,                                                      // %1 - upgrade modified user
                    docuValueTable.name(DbBackend::Sql),                                        // %2 - DocuValue
                    docuRefTable.name(DbBackend::Sql),                                          // %3 - DocuRef
                    docuValueTable.fieldName(fieldNum(DocuValue, Type), DbBackend::Sql),        // %4 - DocuValue.Type
                    docuValueTable.fieldName(fieldNum(DocuValue, RecId), DbBackend::Sql),       // %5 - DocuValue.RecId
                    docuValueTable.fieldName(fieldNum(DocuValue, File), DbBackend::Sql),        // %6 - DocuValue.File
                    docuRefTable.fieldName(fieldNum(DocuRef, ValueRecId), DbBackend::Sql),      // %7 - DocuRef.ValueRecId
                    myMaxRowsToUpdatePerStatement,                                             // %8 - Max rows to update per statement
                    docuValueTable.fieldName(fieldNum(DocuValue, PATH), DbBackend::Sql),        // %9 - DocuRef.PATH
                    docuValueTable.fieldName(fieldNum(DocuValue, StorageProviderId), DbBackend::Sql),       // %10 - DocuRef.StorageProviderId
                    docuValueTable.fieldName(fieldNum(DocuValue, AccessInformation), DbBackend::Sql),      // %11 - DocuRef.AccessInformation
                    myPart1,                                                                       // %12 - https://myCompany.sharepoint.com/sites/D365FOFileShare/
                    myPart2,                                                                       // %13 - https://myCompany.sharepoint.com/sites/D365FOFileShare/api/Web/GetFileByServerRelativePath(decodedurl='/sites/D365FOFileShare/
                    myPart3 ,                                                                      // %14 - ')
                    myPart4 ,                                                                      // %15 - /
                    myPart5 ,                                                                      // %16 - .
                    docuRefTable.fieldName(fieldNum(DocuRef, TYPEID), DbBackend::Sql),              //%17 - DocuRef.TypeId
                    docuRefTable.fieldName(fieldNum(DocuRef, ACTUALCOMPANYID), DbBackend::Sql),     //%18 - 'SPND'
                    docuValueTable.fieldName(fieldNum(DocuValue, FILENAME), DbBackend::Sql),        // %19 - DocuValue.FILENAME
                    docuValueTable.fieldName(fieldNum(DocuValue, FILETYPE), DbBackend::Sql),        // %20 - DocuValue.FILETYPE
                    myLegacy                                                                       // %21 - 'Legacy'
                    );
                impactedRows = this.myExecuteSQL(sqlQuery, connection);
            }
            while (impactedRows == myMaxRowsToUpdatePerStatement);

        }
        finally
        {
            connection.finalize();
        }
    }

    /// <summary>
    /// Updates document reference and value records to handle file storage in the cloud.
    /// </summary>
    [
        UpgradeScriptDescription("Updates document value records to handle file storage in the cloud"),
        UpgradeScriptStage(ReleaseUpdateScriptStage::PostSync),
        UpgradeScriptType(ReleaseUpdateScriptType::PartitionScript),
        UpgradeScriptTable(tableStr(DocuRef), false, true, true, false),
        UpgradeScriptTable(tableStr(DocuValue), false, true, true, true)
    ]
    public void updateDocuValue_CorrectFileLocations()
    {
        this.myPreUpdateDocuValue_CorrectFileLocations();
        next updateDocuValue_CorrectFileLocations();
        this.myPostUpdateDocuValue_CorrectFileLocations();
    }

    /// <summary>
    /// Updates document type records to handle file storage in the cloud.
    /// </summary>
    [
        UpgradeScriptDescription("Updates document type records to handle file storage in the cloud"),
        UpgradeScriptStage(ReleaseUpdateScriptStage::PostSync),
        UpgradeScriptType(ReleaseUpdateScriptType::PartitionScript),
        UpgradeDependsOnTaskAttribute(methodStr(ReleaseUpdateDB72_Docu, updateDocuValue_CorrectFileLocations)),
        UpgradeScriptTable(tableStr(DocuType), false, true, true, false)
    ]
    public void updateDocuType_CorrectFilePlacement()
    {
        next updateDocuType_CorrectFilePlacement();
        this.myCreateNewDocuType();
    }

    /// <summary>
    /// Executes the provided SQL statement.
    /// </summary>
    /// <param name="_sqlStatement">The SQL statement to execute.</param>
    /// <param name="_connection>The SQL connection to use; otherwise a new connection will be created.</param>
    /// <returns>The number of rows impacted by the statement.</returns>
    private int myExecuteSQL(str _sqlStatement, Connection _connection = null)
    {
        Connection sessionConn = _connection ? _connection : new Connection();
        try
        {
            Statement statement     = sessionConn.createStatement();
            new SqlStatementExecutePermission(_sqlStatement).assert();
            int impactedRows = statement.executeUpdate(_sqlStatement);
            statement.close();
            CodeAccessPermission::revertAssert();
            return impactedRows;
        }
        finally
        {
            if (!_connection)
            {
                sessionConn.finalize();
            }
        }
    }

    /// <summary>
    /// gets a Set of all legal entities present in the staging
    /// </summary>
    /// <returns>Set</returns>
    public Set getCompanySet()
    {
        DocuRef docuRef;
        Set companySet = new Set(Types::String);
        while select ActualCompanyId from docuRef
            group by ActualCompanyId
        {
            companySet.add(docuRef.ActualCompanyId);
        }
        return companySet;
    }

    /// <summary>              
    /// Creates new DocuType records for legacy attachment moved now to SharePoint 
    /// </summary>
    private void myCreateNewDocuType()
    {
        Set             companySet  = this.getCompanySet();
        SetEnumerator   se          = companySet.getEnumerator();
        DocuType        documentType;
        ttsbegin;
        while (se.MoveNext())
        {
            SelectableDataArea currCompany = se.current();
            changecompany(currCompany)
            {
                DocuTypeId typeId = myLegacy+'_'+currCompany;
                if(!DocuType::exist( typeId))
                {
                    documentType.clear();
                    documentType.TypeGroup                  = DocuTypeGroup::File;
                    documentType.RemoveOption               = DocuRemoveOption::DocumentAndFile;
                    documentType.FileRemovalConfirmation    = NoYes::Yes;
                    documentType.TypeId                     = typeId;
                    documentType.ActionClassId              = myActionClassId; //DocuActionURLClassId
                    documentType.Name                       = myName+currCompany;
                    documentType.FilePlace                  = DocuFilePlace::SharePoint;
                    documentType.Host                       = myHost;
                    documentType.Site                       = mySite;
                    documentType.FolderPath                 = myLegacy+'/'+currCompany;
                    documentType.doInsert();
                }
            }
        }                     
        ttscommit;
    }

}]

Multiple company selection in an SSRS report (LedgerLegalEntityLookup)

If you need to provide an SSRS report with a multiple company selection, you can opt for a cross-company query. In this case, such an option will be maintained by the system automatically. (You can try this [SrsReportQuery(queryStr(LogisticsEntityLocationUnion))])

But what if you need to do that without the former? In this case you'll need to use LedgerLegalEntityLookup class as follows. Say we deal with some mySalesBySegment report, which is meant to return some data for a given selection of legal entities.

I hid some not relevant code; so that you can get the gist.

Data contract mySalesBySegmentContract: we keep the user selection of companies as a string.

[DataContract]
[SysOperationContractProcessing(classstr(mySalesBySegmentUIBuilder), SysOperationDataContractProcessingMode::CreateUIBuilderForRootContractOnly)]

class mySalesBySegmentContract implements SysOperationValidatable
{
    ...
    str                                 legalEntityOptionsStr;
    ...
 
    [
        DataMember('legalEntityOptions')
        ,
        SysOperationGroupMember('Grouping'),
        SysOperationDisplayOrder('5')
    ]
    public str parmLegalEntityOptions(str _legalEntityOptions = legalEntityOptionsStr)
    {
        legalEntityOptionsStr = _legalEntityOptions;

        return legalEntityOptionsStr;
    }

}

Report controller mySalesBySegmentController: if no companies selected, let's set it to the user's context.

public class mySalesBySegmentController extends SrsReportRunController
{
 
    protected void prePromptModifyContract()
    {
        mySalesBySegmentContract   dc = this.parmReportContract().parmRdpContract() as mySalesBySegmentContract;

       ...
        if (!dc.parmLegalEntityOptions())
        {
            // Set the default value for the legal entity selection
            dc.parmLegalEntityOptions(con2str([curExt()]));
        }
    }

   
    protected void preRunModifyContract()
    {
        mySalesBySegmentContract   dc;
        container                   legalEntityOptions;

        dc                  = this.parmReportContract().parmRdpContract() as mySalesBySegmentContract;
        legalEntityOptions  = str2con(dc.parmLegalEntityOptions());

        // Default current company if there were no company specifications provided to the API.
        if (legalEntityOptions == conNull())
        {
            legalEntityOptions = [curExt()];
            dc.parmLegalEntityOptions(con2str(legalEntityOptions));
        }
  
    }

    public static void main(Args _args)
    {
        mySalesBySegmentController controller = new mySalesBySegmentController();

        controller.parmReportName(ssrsReportStr(mySalesBySegment, Report));
        controller.parmArgs(_args);
         controller.startOperation();
    }

}

User interface builder mySalesBySegmentUIBuilder: when an SSRS report runs, it shows its dialog twice: the second time in the report viewer, when the report is rendered. Thus we have to override dialog methods in the UIBuilder class to avoid the lovely 'Object reference not set to an instance of an object' error.

public class mySalesBySegmentUIBuilder extends SrsReportDataContractUIBuilder
{
    mySalesBySegmentContract   dc;
    // Legal entity lookup controls
    FormStringControl           dialogLegalEntitySelection;
    LedgerLegalEntityLookup     legalEntityLookup;
    int                         dialogLegalEntityLookupId;
    str                         userLegalEntityRange;
    container                   legalEntityOptions;

    /// <summary>
    /// Override this method in order to initialize the dialog fields after the fields are built.
    /// </summary>
    public void postBuild()
    {
        DialogField dialogField;

        super();
        // parmCompanySelection
        dialogField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(mySalesBySegmentContract, parmLegalEntityOptions));
        this.setInVisible(dialogField);

        this.constructLegalEntityControl(dialog);
        
    }

    /// <summary>
    /// post runs
    /// </summary>
    public void postRun()
    {
        super();
        
        this.constructLegalEntityLookup(dialog);
        Set userLegalEntitySet = LedgerSecurityHelper::ledgersWithMinimumSecurityAccess(menuItemActionStr(LedgerExchAdj), AccessRight::Edit, MenuItemType::Action);
        userLegalEntityRange = LedgerLegalEntityLookup::getLegalEntityRangeFromLegalEntitySet(userLegalEntitySet);
    }

    /// <summary>
    /// Contstruct
    /// </summary>
    /// <param name = "_dialog">Dialog</param>
    private void constructLegalEntityControl(Dialog _dialog)
    {
        FormBuildGroupControl currentGroup = _dialog.form().design().control(_dialog.curFormGroup().name());
        FormBuildStringControl dialogLegalEntityLookup = currentGroup.addControl(FormControlType::String, 'LegalEntityLookup');
        dialogLegalEntityLookup.extendedDataType(extendedTypeNum(LedgerLegalEntitySelection));
        dialogLegalEntityLookup.lookupOnly(true);
        dialogLegalEntityLookupId = dialogLegalEntityLookup.id();
    }

    /// <summary>
    /// Constructs the lookup for the legal entity selection.
    /// </summary>
    /// <param name = "_control">The <c>FormStringControl</c> object.</param>
    private void legalEntityLookup(FormStringControl _control)
    {
        legalEntityLookup.lookup(_control.text(), userLegalEntityRange);
    }

    /// <summary>
    /// Lookup override
    /// </summary>
    /// <param name = "_dialog">dialog</param>
    private void constructLegalEntityLookup(Dialog _dialog)
    {
        dialoglegalEntitySelection = _dialog.formRun().design().control(dialogLegalEntityLookupId);
        legalEntityLookup = LedgerLegalEntityLookup::construct(_dialog.formRun(), dialoglegalEntitySelection);
        // populates it from the packed paramater
        legalEntityLookup.setSelection(str2con(dc.parmLegalEntityOptions()));
        // let's have our own lookup
        dialoglegalEntitySelection.registerOverrideMethod(methodstr(FormStringControl, lookup), methodstr(mySalesBySegmentUIBuilder, legalEntityLookup), this);
    }

    /// <summary>
    /// prebuilds
    /// </summary>
    public void preBuild()
    {
        dc = this.dataContractObject() as mySalesBySegmentContract;
        super();
    }

    /// <summary>
    /// Gets it back from the dialog
    /// </summary>
    public void getFromDialog()
    {
        super();
        dc.parmLegalEntityOptions(con2Str(legalEntityLookup.getLegalEntitySelection()));
    }

}

Report data provider mySalesBySegmentDP: we need just to convert the saved string back to a container, then we can loop through it as required by the report logic.

[SRSReportParameterAttribute(classStr(mySalesBySegmentContract))] 
public class mySalesBySegmentDP extends SRSReportDataProviderPreProcessTempDB
{
    container                       legalEntityOptions;
    
    public void processReport()
    {
        mySalesBySegmentContract        dc;
        List                            legalEntityList;
        ListEnumerator                  legalEntityListEnumerator;
        SelectableDataArea              companyId;
        str                             companyName;

        dc                          = this.parmDataContract() as mySalesBySegmentContract;
     
        this.setUserConnection(tmp);
        
        // getting all selected companies from the report query
        legalEntityList             = con2List(str2con(dc.parmLegalEntityOptions()));
        legalEntityListEnumerator   = legalEntityList.getEnumerator();

        while (legalEntityListEnumerator.moveNext())
        {
            companyId   = legalEntityListEnumerator.current();
            companyName = CompanyInfo::findDataArea(companyId).name();
            changecompany(companyId)
            {
                // Populate the base processing table with data from the appropriate source table
                ...
            }
        }
    }
}

AX World Elephants :)

 

How to resolve reference with a form control value

If you need not just to maintain lookup for a refence group but also to validate a manually input value, you need to implement resolveReference method for the field of the form data source. Say, we want to validate a custom financial dimension value.

You can check resolveReference* methods in EcoResCategory table as a good example.

The most interesting detail for me is the way how the related form control value is found inside of the given reference group.

            /// <summary>
            /// Resolve reference
            /// </summary>
            /// <param name = "_formReferenceControl"></param>
            /// <returns></returns>
            public Common resolveReference(FormReferenceControl _formReferenceControl)
            {
                Common ret;
            
                ret = myAssignedBankAccountDimension.dimensionResolveReference(_formReferenceControl);
            
                return ret;
            }

    public Common dimensionResolveReference(FormReferenceControl _formReferenceControl)
    {
        DimensionAttribute                          dimensionAttribute;
        DimensionAttributeDirCategory               dimAttributeDirCategory;
        DimensionFinancialTag                       dimensionFinancialTag;
        myFinancialDimensionValueFinancialTagView  view;
        myAssignedBankAccountDimension             myAssignedBankAccountDimension;
        DimensionDisplayValue                       dimensionDisplayValue;

        if (!_formReferenceControl || _formReferenceControl.handle() != classNum(FormReferenceGroupControl) )
        {
            throw(error(strFmt("@SYS137393", Error::wrongUseOfFunction(funcName())) ));
        }

        dimensionDisplayValue = _formReferenceControl.filterValue(AbsoluteFieldBinding::construct(fieldStr(DimensionFinancialTag, Value), tableStr(DimensionFinancialTag))).value();
        dimensionDisplayValue = strLRTrim(dimensionDisplayValue);
        
        <..  implement your logic with the display value ...>