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 ...>

How to change currency symbol in a given number format

Say, we need to print a Vendor payment advice in the vendor's language, whic is es (Spanish) in the example below. Once the report parameter AX_RenderingCulture is set to 'es', all related number formatting will be applied to amounts cells. 

However, the payment may be made in different currencies; thus its currency symbol $ must be used instead of Euro.

Basically, such parameters, like currency symbol etc, can be changed through System.Globalization.CultureInfo class created for the rendering culture. I did not find a way how to achieve it for a particular textbox in SSRS design. So, I formatted the amount directly in X++.

I used Global::strFmtByLanguage method as a basis for my method to replace the culture number format currency symbol to a given one. There are a few other interesting methods you can check to see how to deal with formatting dates and numbers.

    public str myChangeCurSymbolForAmountStr(LanguageId _languageId, System.Double _amountCur, CurrencySymbol _currencySymbol)
    {
        System.Globalization.CultureInfo    culture;
        str                                 res;
        System.Exception                    e;
        str                                 curSymbol;

        culture = new System.Globalization.CultureInfo(_languageId);

        try
        {
            res         = _amountCur.ToString("C", culture);
            curSymbol   = culture.NumberFormat.CurrencySymbol;
            res         = strReplace(res, curSymbol, _currencySymbol);
        }
        catch(Exception::CLRError)
        {
            e = CLRInterop::getLastException();
            while( e )
            {
                error( e.get_Message() );
                e = e.get_InnerException();
            }
            throw Exception::Error;
        }
        return res;
    }

    public myAmountStringWithCurrencySymbol myAmountStringWithCurrencySymbol(AmountCur _amountCur, CurrencyCode _currency, LanguageId _languageId)
    {
        Currency                            currency = Currency::find(_currency);
        
        return this.myChangeCurSymbolForAmountStr(_languageId, _amountCur, currency.Symbol);;
    }

    protected void insertBankPaymAdviceTmp()
    {
        BankPaymAdviceVendTmp bankPaymAdviceVendTmp;
        str                   email;

        next insertBankPaymAdviceTmp();

        bankPaymAdviceVendTmp = this.bankPaymAdviceTmp as BankPaymAdviceVendTmp;
                
        if (bankPaymAdviceVendTmp.RecId)
        {          
            ttsbegin;
            bankPaymAdviceVendTmp.selectForUpdate(true);
            bankPaymAdviceVendTmp.myBalance01Total+=bankPaymAdviceVendTmp.Balance01;
            bankPaymAdviceVendTmp.myAmountStringWithCurrencySymbol  = this.myAmountStringWithCurrencySymbol(bankPaymAdviceVendTmp.EOGBalance01Total, bankPaymAdviceVendTmp.CurrencyCode, VendTable::find(bankPaymAdviceVendTmp.AccountNum).languageId());          
            bankPaymAdviceVendTmp.update();
            ttscommit;
        }
    }

The final string can be referenced in Total textbox as Last(bankPaymAdviceVendTmp.myAmountStringWithCurrencySymbol) and with default format.

Lookup, JumpRef, Modified for a form data source field through CoC

 As Ievgen Miroshnikov explained in his old article https://community.dynamics.com/365/financeandoperations/b/ievgensaxblog/posts/d365foe-how-to-override-form-data-source-field-lookup-method, it is much better to override methods directly on a data source field than on its linked form controls.

I just want to re-iterate it and place here code snippets.

• User can add new control using form personalization and this control won’t support overridden logic. It could be critical if you are restricting field lookup values or adding validations.
• One form could have several controls that refers to one data source field so you have to duplicate your code.
• Number of delegates are limited as well.

So, say we need to implement Lookup, JumpRef, and Modified methods for a custom field on CustInvoiceTable data source of CustFreeInvoice form. Implement these three aforementioned methods, e.g., directly in an extension to the form class.

 

[ExtensionOf(formStr(CustFreeInvoice))]
final class myCustFreeInvoice_Form_Extension
{
    public void myAssignedBankAccountIdModified(FormDataObject _targetField)
    {

       <logic>
    }

    public void myAssignedBankAccountIdJumpRef(FormDataObject _targetField)
    {

       <logic>
    }

    // Different parameter here!

    public void myAssignedBankAccountIdLookup(FormStringControl _callingControl)
    {
       <logic>
    }

Now simply override them for the field when the data source is initialized.

 

public class myCustFreeInvoice_Form_EventHandler
{
    [FormDataSourceEventHandler(formDataSourceStr(CustFreeInvoice, CustInvoiceTable), FormDataSourceEventType::Initialized)]
    public static void myCustInvoiceTable_OnInitialized(FormDataSource _sender, FormDataSourceEventArgs _e)
    {
        FormRun         eogFormRun                  = _sender.formRun();
        FormDataObject  eogCustomField              = _sender.object(fieldNum(CustInvoiceTable, eogCustomField));

        fdoEOGAssignedBankAccountId.registerOverrideMethod(methodStr(FormDataObject, jumpRef), formMethodStr(CustFreeInvoice, myAssignedBankAccountIdJumpRef), myFormRun);
        fdoEOGAssignedBankAccountId.registerOverrideMethod(methodStr(FormDataObject, lookup), formMethodStr(CustFreeInvoice, myAssignedBankAccountIdLookup), myFormRun);
        fdoEOGAssignedBankAccountId.registerOverrideMethod(methodStr(FormDataObject, modified), formMethodStr(CustFreeInvoice, myAssignedBankAccountIdModified), myFormRun);
    }

How to populate custom fields in GeneralJournalAccountEntry from LedgerJournalTrans for Ledger, Customer, Vendor, and Bank account type

 Say, we need to add a new field UniqueId to LedgerJournalTrans and then have it populated in GeneralJournalAccountEntry, once a General journal posted.

In other words the field value must be transferred from a general journal line to a related voucher transaction.

Generally speaking there are two different ways how GL transactions created in D365FO: via Source document framework and via LedgerVoucherObject. Moreover, one transaction may be a result of summarization of multiple documents. So, this approach works for this particular scenario, when GL transactions come from a general journal. The proposed solution covers Ledger, Customer, Vendor, and Bank types. You can elaborate it for Project, Fixed Asset, etc. Check their appropriate classes.

LedgerJournalCheckPost class creates one transaction per a line of Ledger type, two if the latter has an offset info.

When it comes to other transaction type, first, a transaction in CustTrans, VendTrans, BankTrans etc is created, and then based on the latter a new transaction is added.

So, we creates the following extensions.

Tables.

Classes.

Below, you can find code snippets for each of them.

BankVoucher_Extension

[ExtensionOf(classStr(BankVoucher))]
final class BankVoucher_Extension
{
    public UniqueId UniqueId;


    public UniqueId parmUniqueId(UniqueId _parm = uniqueId)
    {
        uniqueId = _parm;

        return uniqueId;
    }


    protected LedgerVoucherTransObject initializeLedgerVoucherTransObjectForPosting(LedgerVoucherObject _ledgerVoucherObject, CurrencyExchangeHelper _exchangeRateHelper)
    {
        LedgerVoucherTransObject ledgerVoucherTransObject = next initializeLedgerVoucherTransObjectForPosting(_ledgerVoucherObject, _exchangeRateHelper);
        if(ledgerVoucherTransObject)
        {
             ledgerVoucherTransObject.parmUniqueId(_ledgerVoucherObject.parmUniqueId());
        }
        return ledgerVoucherTransObject;
    }

}

CustVendVoucher_Extension

[ExtensionOf(classStr(CustVendVoucher))]
final class CustVendVoucher_Extension
{
    public UniqueId uniqueId;

    public UniqueId parmUniqueId(UniqueId _parm = uniqueId)
    {
        uniqueId = _parm;

        return uniqueId;
    }

   protected LedgerVoucherTransObject createLedgerVoucherTransObject(boolean _useSubLedger,
                                                                     LedgerDimensionAccount _ledgerDimensionMerged,
                                                                     LedgerJournalTrans _ledgerJournalTrans,
                                                                     LedgerVoucher _ledgerPostingJournal,
                                                                     CustVendTrans _custVendTrans)
    {
        LedgerVoucherTransObject ledgerVoucherTransObject = next createLedgerVoucherTransObject( _useSubLedger, _ledgerDimensionMerged, _ledgerJournalTrans, _ledgerPostingJournal, _custVendTrans);
        if(ledgerVoucherTransObject)
        {
             ledgerVoucherTransObject.parmUniqueId(uniqueId);
        }
        return ledgerVoucherTransObject;
    }

}

LedgerJournalCheckPost_Extension

[ExtensionOf(classStr(LedgerJournalCheckPost))]
final class LedgerJournalCheckPost_Extension
{

    protected LedgerVoucherObject createPostingReference(LedgerJournalTrans _ledgerJournalTrans, SysModule _sysModule)
    {
        LedgerVoucherObject newVoucher = next  createPostingReference(_ledgerJournalTrans, _sysModule);
        if(newVoucher)
        {
             newVoucher.parmUniqueId(_ledgerJournalTrans.uniqueId);
        }
        return newVoucher;
    }

    protected LedgerVoucherObject updatePostingReference(LedgerVoucherObject _postingReference, LedgerJournalTrans _ledgerJournalTrans, SysModule _sysModule)
    {
        next updatePostingReference(_postingReference, _ledgerJournalTrans, _sysModule);
        if(_postingReference)
        {
            _postingReference.parmUniqueId(_ledgerJournalTrans.uniqueId);
        }
        return _postingReference;
    }

}

LedgerJournalTransUpdateBank_Extension

[ExtensionOf(classStr(LedgerJournalTransUpdateBank))]
final class LedgerJournalTransUpdateBank_Extension
{

    protected BankVoucher initBankVoucher(  LedgerJournalTrans _ledgerJournalTrans,
                                            TaxAmount _taxAmount,
                                            real _taxWithholdAmount,
                                            DimensionDefault _defaultDimension,
                                            LedgerJournalType _ledgerJournalType,
                                            boolean _skipDimensionValidation)
    {
        BankVoucher bankVoucher = next initBankVoucher(_ledgerJournalTrans, _taxAmount, _taxWithholdAmount, _defaultDimension, _ledgerJournalType, _skipDimensionValidation);
        if(bankVoucher)
        {
            bankVoucher.parmUniqueId(_ledgerJournalTrans.uniqueId);
        }
        return bankVoucher;
    }

}

LedgerVoucherObject_Extension

[ExtensionOf(classStr(LedgerVoucherObject))]
final class LedgerVoucherObject_Extension
{
    public UniqueId uniqueId;

    public UniqueId parmUniqueId(UniqueId _parm = uniqueId)
    {
        uniqueId = _parm;

        return uniqueId;
    }

}

LedgerVoucherTransObject_Extension

[ExtensionOf(classStr(LedgerVoucherTransObject))]
final class LedgerVoucherTransObject_Extension
{
    public UniqueId uniqueId;

    public UniqueId parmUniqueId(UniqueId _parm = uniqueId)
    {
        generalJournalAccountEntry.UniqueId  = _parm;
        return generalJournalAccountEntry.UniqueId;
    }

   
    public static LedgerVoucherTransObject newTransLedgerJournal(
                                                                    LedgerJournalTrans  _ledgerJournalTrans,
                                                                    TaxAmount           _taxAmount,
                                                                    boolean             _bridging,
                                                                    container           _intercompanyRecIds,
                                                                    boolean             _reversalsMayExist,
                                                                    boolean             _forcedExchangeRate)
    {
        LedgerVoucherTransObject ledgerVoucherTransObject = next newTransLedgerJournal(_ledgerJournalTrans, _taxAmount, _bridging, _intercompanyRecIds, _reversalsMayExist, _forcedExchangeRate);
        if(ledgerVoucherTransObject)
        {
            ledgerVoucherTransObject.parmUniqueId(_ledgerJournalTrans.UniqueId);
        }
        return ledgerVoucherTransObject;
    }


    public LedgerPostingTransactionTmp getLedgerPostingTransaction()
    {
        LedgerPostingTransactionTmp ledgerPostingTransaction =  next getLedgerPostingTransaction();

        ledgerPostingTransaction.UniqueId = generalJournalAccountEntry.UniqueId;

        return ledgerPostingTransaction;
    }

  
    public void initFromLedgerPostingTransaction(LedgerPostingTransactionTmp _ledgerPostingTransaction,LedgerPostingTransactionProjectTmp _projectPostingTransaction)
    { 
        next initFromLedgerPostingTransaction(_ledgerPostingTransaction,_projectPostingTransaction);

        generalJournalAccountEntry.UniqueId = _ledgerPostingTransaction.UniqueId;
    }

  
    public static LedgerVoucherTransObject newTransactionAccountingAmountsDefault(
                                                                                    LedgerVoucherObject _defaultLedgerPostingReference,
                                                                                    LedgerPostingType _postingType,
                                                                                    RecId _ledgerDimensionId,
                                                                                    CurrencyCode _transactionCurrencyCode,
                                                                                    Money _transactionCurrencyAmount,
                                                                                    MoneyMST _accountingCurrencyAmount,
                                                                                    CurrencyExchangeHelper _currencyExchangeHelper)
    {
        LedgerVoucherTransObject postingTrans;

        postingTrans = next newTransactionAccountingAmountsDefault(_defaultLedgerPostingReference, _postingType, _ledgerDimensionId, _transactionCurrencyCode, _transactionCurrencyAmount, _accountingCurrencyAmount, _currencyExchangeHelper);
        if(postingTrans)
        {
            postingTrans.parmUniqueId(_defaultLedgerPostingReference.parmUniqueId());
        }
        return postingTrans;
    }

}

I wish to credit the following articles I used:

http://axforum.info/forums/showthread.php?t=74038

https://allaboutdynamic.com/2018/06/25/d365-ax7-update-custom-fields-in-custtrans-vendtrans-from-ledgerjournaltrans-during-the-posting-of-journal/

http://axwiki.blogspot.com/2017/01/customize-field-in-ledgerjournaltabletr.html