Power Automate (Flow) Expression usage samples

Get list items from Array

item()?['weborder']

Get compose output body content

outputs('Get_file_content')?['body']

Get Blob content

outputs('Get_blob_content_using_path_(V2)')?['body']

outputs('Get_blob_content_using_path_(V2)')?['value']

Split string by comma

split(variables('<var name>'),',')

Decode CSV file from Blob DECODE URI:

split(outputs('Get_blob_content_using_path_(V2)')?['body'], decodeUriComponent('%0D%0A'))

Skip output line 1

skip(outputs('splitNewLine'),2)

Get output value from list

outputs('OutputJSON')?['WebOrderNo']

outputs('D365FOSelectOut')?['body']

Find length of array

length(body('D365FOSelectOut'))

Concat string

concat(variables('OutputArrayResult'),outputs('D365FOSelectOut')?['body'])

Append array variables (Reference Blog) using Append to array variable (Action)

https://www.enjoysharepoint.com/power-automate-array-variable/

Get output Body/Value

outputs('Lists_items_present_in_table')?['body/value']

Date time format: (Reference Blog)

https://sharepains.com/2018/11/12/formatdatetime-power-automate/

Convert: (while using HTTP request output content type is octet stream, but excepted should be in Text/csv. Follow below

Header; Add in addition to existing header

"x-ms-access-tier-inferred": true

output:

{
"$content-type":  "application/octet-stream",
"$content":  "Ww0KICB7DQogICCJyZWNlaXZlciI6ICIgMTIzODUiLA0KICAgICJwYXlsb2FkIjogIiB7J21lc3NhZ2UnOiAndGVzdCAxJyIsDQogICAgIm9wZXJhdG9yIjogIiAnRW5naW5lSWQnOiAzIiwNCiAgICAic2VuZGVyIjogIiAnUGVyc29uSWQnOiAxIg0KICB9LA0KICB7DQogICAgImlkIjogIjIiLA0KICAgICJyZWNlaXZlciI6ICIgMTIzNDciLA0KICAgICJwYXlsb2FkIjogIiB7J21lc3NhZ2UnOiAndGVzdCAyJyIsDQogICAgIm9wZXJhdG9yIjogIiAnRW5naW5lSWQnOiAzIiwNCiAgICAic2VuZGVyIjogIiAnUGVyc29uSWQnOiAyIg0KICB9DQpd"
}

then we use

string(triggerBody()) ex: string(outputs('HTTPGetBlobContent')?['body'])

Date difference:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),864000000000)
div(sub(ticks({EndDateTime}),ticks({StartDateTime})),36000000000)
div(sub(ticks({EndDateTime}),ticks({StartDateTime})),600000000)
div(sub(ticks({EndDateTime}),ticks({StartDateTime})),10000000)

To extend the constructor to instantiate the extended (Custom) class in D365FO

    [PostHandlerFor(classStr(<Std class name>), staticMethodStr(<Std class name>, construct))]

    public static void <Std class name>_Post_construct(XppPrePostArgs args)

    {

        <Std class name>  objCls;

        objCls= args.getReturnvalue();       

        args.setReturnValue(<Custom class name>::construct(args.getArg(identifierStr(<Argument name>)),args.getArg(identifierStr(<Argument name>))));

    }

Copy Find Symbols Results in VS for D365FO - Development tools

 Reference: http://kashperuk.blogspot.com/2016/10/development-tool-copy-find-references.html 

Github:

https://github.com/IvanKashperuk/CopyFindReferencesToClipboard

File:

https://1drv.ms/u/s!ApLM1du7Goflm-VNJqQpHz6GRkS_VQ

Note: Copy might take time, based on no of results

Useful script for D365FO

 

Reference:

https://github.com/TrudAX/TRUDScripts/blob/master/D365FO/UsefulScripts.ps1

List:

https://github.com/d365collaborative/d365fo.tools/wiki

#MaintenanceMod

#SYNC

#INSTALL LICENCE 

#DOWNLOAD DB FROM LCS

#BACKUP TIER1

#RESTORE TIER1 DB on TIER1

#SQL update userinfo set enable= 1

#TRANSFER TO TIER2

#DEPLOY REPORTS

#RESTORE TIER2 TO TIER1

#prepare the database

#IMPORT USERS

#RUN JOB

Batch job clean up X++ D365FO

  Connection                      connection;

        Statement                       statement;

        SqlStatementExecutePermission   permission;

        str                             deleteStm;

        if (!Global::isSystemAdministrator())

        {

            error("@SYS130561");

            return;

        }

        connection  = new Connection();

        connection.ttsbegin();

        statement   = connection.createStatement();

        deleteStm = '(SELECT RECID FROM ' + ReleaseUpdateDB::backendTableName(tableNum(Batch)) + ' WHERE GROUPID = \'' + 'TT_BT' + '\')';

        deleteStm = 'DELETE FROM ' + ReleaseUpdateDB::backendTableName(tableNum(BatchConstraints)) + ' WHERE BATCHID IN ' + deleteStm +

            ' OR DEPENDSONBATCHID IN ' + deleteStm + ';';

        permission  = new SqlStatementExecutePermission(deleteStm);

        permission.assert();

        statement.executeUpdate(deleteStm);

        statement.close();

        CodeAccessPermission::revertAssert();

        statement   = connection.createStatement();

        deleteStm = 'DELETE FROM ' + ReleaseUpdateDB::backendTableName(tableNum(Batch)) + ' WHERE GROUPID = \'' + 'TT_BT' + '\';';

        permission  = new SqlStatementExecutePermission(deleteStm);

        permission.assert();

        statement.executeUpdate(deleteStm);

        statement.close();

        CodeAccessPermission::revertAssert();

statement   = connection.createStatement();

        deleteStm = 'DELETE FROM ' + ReleaseUpdateDB::backendTableName(tableNum(BatchJobHistory)) + ' WHERE STATUS != \'' + 3 + '\';';

        permission  = new SqlStatementExecutePermission(deleteStm);

        permission.assert();

        statement.executeUpdate(deleteStm);

        statement.close();

        CodeAccessPermission::revertAssert();

        connection.ttscommit();

        connection = null;

Postman Odata request for filters and Pre request script for dates

Postman: (Get request time rely on server timezone)

Request: GET ->

https://<D365FO URL>/data/BatchJobs?$filter=Status eq Microsoft.Dynamics.DataEntities.BatchStatus'Executing' and StartDate eq {{currentdate}} and StartTime gt {{currentTime}} &cross-company=true

or

https://<D365FO URL>/data/RetailTransactions?$filter=ChannelReferenceId eq '' and CustomerAccount eq '' and TransactionDate eq 2022-08-23 &cross-company=true

Pre-Request script:

var moment = require('moment');

pm.globals.set('currentdate', moment().format(("YYYY-MM-DD")));

pm.globals.set("currentTime", new Date().setTime(new Date().getTime()-10800)); (As per postman installed machine time Zone GMT)

//setting

postman.setEnvironmentVariable("currentdate", moment().format(("YYYY-MM-DD")));

Set Global variables:

var jsonData = JSON.parse(responseBody);

postman.setEnvironmentVariable("access_token", jsonData.access_token);

Select field list

&$select=CustomerAccount,OrganizationName

Test samples:

pm.test("Status code is 200", function () {

    pm.response.to.have.status(200);

});

var json = JSON.parse(responseBody);

tests["<Collection request name>"] = !json.error && responseBody !== '' && responseBody !== '{}' && json.Id !== '';

postman.setEnvironmentVariable("<Global variable name>", json.AmountDue);

var json = JSON.parse(responseBody);

tests["<Collection request name>"] = !json.error && responseBody !== '' && responseBody !== '{}' && json.CartLines !== '';

postman.setEnvironmentVariable("<Global variable name>", json.CartLines[0].LineId);

Create agreement header & lines and link with Sales order X++

            //Process Started

  select firstOnly agreementClassification

                where agreementClassification.AgreementRelationType == tableNum(SalesAgreementHeader);

            row =1;

            worksheetSysHeader = worksheets.itemFromName(worksheetHeader);

            cells = worksheetSysHeader.cells();

            do

            {

                row++;

                salesAgreementHeader = SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());

                if(!salesAgreementHeader)

                {

                    salesAgreementHeader.clear();

                    salesAgreementHeader.initValue();

                    salesAgreementHeader.SalesNumberSequence   = cells.item(row, 1).value().bStr();

                    salesAgreementHeader.CustAccount   =  cells.item(row, 2).value().bStr();

                    salesAgreementHeader.initFromCustTable();

                    salesAgreementHeader.AgreementClassification = agreementClassification.RecId;

                    salesAgreementHeader.Currency  = cells.item(row, 9).value().bStr();

                    salesAgreementHeader.AgreementState = str2enum(agreementState, cells.item(row, 10).value().bStr());

                    salesAgreementHeader.DefaultAgreementLineEffectiveDate = str2Date(this.COMVariant2Str(cells.item(row, 5).value(), 0),213);

                    salesAgreementHeader.DefaultAgreementLineExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 6).value(), 0),213);

                    if (salesAgreementHeader.validateWrite())

                    {

                        salesAgreementHeader.insert();

                    }

                }

                else

                {

                    salesAgreementHeader.selectForUpdate(true);

                    salesAgreementHeader.Currency  = cells.item(row, 9).value().bStr();

                    salesAgreementHeader.AgreementState = str2enum(agreementState, cells.item(row, 10).value().bStr());

                    salesAgreementHeader.DefaultAgreementLineEffectiveDate = str2Date(this.COMVariant2Str(cells.item(row, 5).value(), 0),213);

                    salesAgreementHeader.DefaultAgreementLineExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 6).value(), 0),213);

                    if (salesAgreementHeader.validateWrite())

                    {

                        salesAgreementHeader.update();

                    }

                }

                if(salesAgreementHeader)

                {

                    if(!conFind(salesAgreementIds, salesAgreementHeader.SalesNumberSequence))

                    {

                        salesAgreementIds += salesAgreementHeader.SalesNumberSequence;

                    }

                    salesAgreementHeaderDefault = SalesAgreementHeaderDefault::findSalesAgreementHeader(salesAgreementHeader.RecId);

                    if(salesAgreementHeaderDefault)

                    {

                        salesAgreementHeaderDefault.selectForUpdate(true);

                        salesAgreementHeaderDefault.CustomerRequisitionNumber = cells.item(row, 12).value().bStr();

                        salesAgreementHeaderDefault.update();

                    }

                    else

                    {

                        salesAgreementHeaderDefault.clear();

                        salesAgreementHeaderDefault.initValue();

                        salesAgreementHeaderDefault.SalesAgreementHeader = salesAgreementHeader.RecId;

                        salesAgreementHeaderDefault.CustomerRequisitionNumber = cells.item(row, 12).value().bStr();

                        salesAgreementHeaderDefault.insert();

                    }

                    agreementHeaderDefault  = AgreementHeaderDefault::findAgreementHeader(salesAgreementHeader.RecId);

                    if(agreementHeaderDefault)

                    {

                        agreementHeaderDefault.selectForUpdate(true);

                        agreementHeaderDefault.DeliveryPostalAddress = CustTable::find(cells.item(row, 2).value().bStr()).postalAddress().RecId;

                        agreementHeaderDefault.DeliveryName = CustTable::find(cells.item(row, 2).value().bStr()).name();

                        agreementHeaderDefault.ContactPerson = cells.item(row, 4).value().bStr();

                        agreementHeaderDefault.ExternalReference = cells.item(row, 11).value().bStr();

                        agreementHeaderDefault.DeliveryTerm = cells.item(row, 8).value().bStr();

                        agreementHeaderDefault.PaymentTerms = cells.item(row, 7).value().bStr();

                        agreementHeaderDefault.ContactPersonDataAreaId = curext();

                        agreementHeaderDefault.PaymentTermsDataAreaId = curext();

                        agreementHeaderDefault.DeliveryTermDataAreaId = curext();

                        agreementHeaderDefault.ContactPersonDataAreaId = curext();

                        agreementHeaderDefault.update();

                    }

                    else

                    {

                        agreementHeaderDefault.clear();

                        agreementHeaderDefault.initValue();

                        agreementHeaderDefault.AgreementHeader = salesAgreementHeader.RecId;

                        agreementHeaderDefault.ContactPerson = cells.item(row, 4).value().bStr();

                        agreementHeaderDefault.ExternalReference = cells.item(row, 11).value().bStr();

                        agreementHeaderDefault.DeliveryTerm = cells.item(row, 8).value().bStr();

                        agreementHeaderDefault.PaymentTerms = cells.item(row, 7).value().bStr();

                        agreementHeaderDefault.DeliveryPostalAddress = CustTable::find(cells.item(row, 2).value().bStr()).postalAddress().RecId;

                        agreementHeaderDefault.DeliveryName = CustTable::find(cells.item(row, 2).value().bStr()).name();

                        agreementHeaderDefault.ContactPersonDataAreaId = curext();

                        agreementHeaderDefault.PaymentTermsDataAreaId = curext();

                        agreementHeaderDefault.DeliveryTermDataAreaId = curext();

                        agreementHeaderDefault.insert();

                    }

                }

                type = cells.item(row+1, 1).value().variantType();

            }

            while (type != COMVariantType::VT_EMPTY);

            row =1;

            cells = null;

            worksheetSysHeader = worksheets.itemFromName(worksheetLine);

            cells = worksheetSysHeader.cells();

            do

            {

                row++;

                salesAgreementHeader =  SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());

                agreementHeader = AgreementHeader::find(salesAgreementHeader.RecId);

                agreementHeaderDefault = AgreementHeaderDefault::findAgreementHeader(agreementHeader.RecId);

                salesAgreementHeaderDefault = SalesAgreementHeaderDefault::findSalesAgreementHeader(salesAgreementHeader.RecId);

                if(salesAgreementHeader && agreementHeader)

                {

                     select firstonly agreementLineQty

                        where agreementLineQty.Agreement == agreementHeader.RecId

                            && agreementLineQty.ItemId == cells.item(row, 2).value().bStr();

                    if(!agreementLineQty)

                    {

                        agreementLineQty.clear();

                        agreementLineQty.initValue();

                        agreementLineQty.initFromAgreementHeader(agreementHeader);

                        agreementLineQty.Agreement = agreementHeader.RecId;

                        agreementLineQty.AgreementLineProduct   = AgreementLineProduct::Item;

                        agreementLineQty.AgreementLineType = CommitmentType::ProductQuantity;

                        agreementLineQty.ItemId = cells.item(row, 2).value().bStr();

                        agreementLineQty.initFromInventTable();

                        inventDim = agreementLineQty.inventDim();

                        switch (agreementLineQty.agreementModuleType())

                        {

                            case ModuleSalesPurch::Sales:

                                inventDim.initFromInventTable(agreementLineQty.inventTable(), InventItemOrderSetupType::Sales, inventDim);

                                break;

                            case ModuleSalesPurch::Purch:

                                inventDim.initFromInventTable(agreementLineQty.inventTable(), InventItemOrderSetupType::Purch, inventDim);

                                break;

                        }

                        inventDim.InventDimId = InventDim::findOrCreate(inventDim).InventDimId;

                        agreementLineQty.setInventDimId(inventDim.InventDimId);

                        agreementLineQty.InventDimDataAreaId = curext();

                        agreementLineQty.ExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 14).value(), 0),213);

                        agreementLineQty.CommitedQuantity =  str2num(this.COMVariant2Str(cells.item(row, 4).value(), 2));

                        agreementLineQty.ProductUnitOfMeasure = cells.item(row, 5).value().bStr();

                        agreementLineQty.PricePerUnit = str2num(this.COMVariant2Str(cells.item(row, 6).value(), 2));

                        agreementLineQty.LineDiscountAmount =str2num(this.COMVariant2Str(cells.item(row, 7).value(), 2));

                        agreementLineQty.LineDiscountPercent =str2num(this.COMVariant2Str(cells.item(row, 8).value(), 2));

                        agreementLineQty.LineNumber = AgreementLine::lastLineNum(agreementHeader.RecId) + 1;

                        if (agreementLineQty.validateWrite())

                        {

                            agreementLineQty.insert();

                            agreementLineQty.salesAgreementHeader().update();

                        }

                    }

                    else

                    {

                        agreementLineQty.selectForUpdate(true);

                        agreementLineQty.ExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 14).value(), 0),213);

                        agreementLineQty.CommitedQuantity =  str2num(this.COMVariant2Str(cells.item(row, 4).value(), 2));

                        agreementLineQty.ProductUnitOfMeasure = cells.item(row, 5).value().bStr();

                        agreementLineQty.PricePerUnit = str2num(this.COMVariant2Str(cells.item(row, 6).value(), 2));

                        agreementLineQty.LineDiscountAmount =str2num(this.COMVariant2Str(cells.item(row, 7).value(), 2));

                        agreementLineQty.LineDiscountPercent =str2num(this.COMVariant2Str(cells.item(row, 8).value(), 2));

                        agreementLineQty.update();

                        agreementLineQty.salesAgreementHeader().update();

                    }

                    

                    if(agreementLineQty)

                    {

                        agreementLineDefault = AgreementLineDefault::findAgreementLine(agreementLineQty.RecId);

                        if(!agreementLineDefault)

                        {

                            agreementLineDefault.clear();

                            agreementLineDefault.initValue();

                            agreementLineDefault.AgreementLine = agreementLineQty.RecId;

                            agreementLineDefault.initFromAgreementHeaderDefault(agreementHeaderDefault);

                            agreementLineDefault.initFromSalesAgreementHeaderDefault(salesAgreementHeaderDefault);

                            agreementLineDefault.insert();

                        }

                    }

                }

                else

                {

                    info(strFmt("%1 Invalid sales agreement header reference",cells.item(row, 1).value().bStr()));

                }

                type = cells.item(row+1, 1).value().variantType();

            }

            while (type != COMVariantType::VT_EMPTY);

            row =1;

            cells = null;

            worksheetSysHeader = worksheets.itemFromName(worksheetRef);

            cells = worksheetSysHeader.cells();

            do

            {

                row++;

                salesAgreementHeader.clear();

                agreementHeader.clear();

                salesAgreementHeader =  SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());

                agreementHeader = AgreementHeader::find(salesAgreementHeader.RecId);

                

                agreementLine.clear();

                select firstOnly agreementLine

                    where agreementLine.Agreement == agreementHeader.RecId

                        && agreementLine.ItemId == cells.item(row, 3).value().bStr();

                

                if(agreementLine)

                {

                    salesLine.clear();

                    select firstOnly salesLine

                        where salesLine.SalesId == cells.item(row, 2).value().bStr()

                            && salesLine.ItemId == cells.item(row, 3).value().bStr(); 

                    if(salesLine)

                    {

                        salesTable = salesLine.salesTable();

                        if(salesTable)

                        {

                            salesTable.selectForUpdate(true);

                            salesTable.initFromSalesAgreementHeader(salesAgreementHeader);

                            salesTable.update();

                        }

                        SalesTableForm::createAgreementLinkServer(salesLine, agreementLine);

                    }

                    else

                    {

                        info(strFmt("%1 %2 combination of Sales order and item number doesnot exist", 

                        cells.item(row, 2).value().bStr(),

                        cells.item(row, 3).value().bStr()));

                    }

                }

                else

                {

                    info(strFmt("%1 %2 combination of Sales agreement and item number doesnot exist", 

                        cells.item(row, 1).value().bStr(),

                        cells.item(row, 3).value().bStr()));

                }

                type = cells.item(row+1, 1).value().variantType();

            }

            while (type != COMVariantType::VT_EMPTY);

            ttsCommit;

            //Process ended

SQL script to delete all staging table data in D365FO

 declare cu cursor for SELECT [name] FROM SYSTABLEIDVIEW where [name] like '%Staging'

declare @table varchar(100)

declare @sql nvarchar(1000)

OPEN cu  

FETCH NEXT FROM cu INTO @table 

WHILE @@FETCH_STATUS = 0  

BEGIN  

    set @sql = N'delete from ' + @table

    PRINT @table

    EXEC sp_executesql @sql

    FETCH NEXT FROM cu INTO @table 

END   

CLOSE cu;  

DEALLOCATE cu;

Data management EXPORT operation using X++ in D365FO

         #DMF

        Query query;

        DMFEntityName entityName;

        boolean isGenerated = false;

        entityName = DMFEntity::findFirstByTableId(tableNum(BankPositivePayExportEntity)).EntityName;

            // Update query

            query = new Query(DMFUtil::getDefaultQueryForEntityV3(entityName));

            QueryBuildDataSource qbds = query.dataSourceTable(tableNum(BankPositivePayExportEntity));

            SysQuery::findOrCreateRange(qbds, fieldNum(BankPositivePayExportEntity, PositivePayNumber)).value(queryValue(bankPositivePayTable.PositivePayNum));

        // Export file

        DMFDefinitionGroupName definitionGroupName = strFmt('%1-%2', classStr(BankPositivePayExport), bankPositivePayTable.PositivePayNum);

        try

        {

            BankPositivePayFormat positivePayFormat = BankPositivePayFormat::findByFormatName(bankPositivePayTable.PayFormat);

            List xsltFileList = new List(Types::String);

            if (positivePayFormat.FileId != '')

            {

                xsltFileList.addEnd(this.copyTransformationFile(positivePayFormat));

            }

            DMFEntityExporter exporter = new DMFEntityExporter();

            fileId = exporter.exportToFile(

                entityName,

                definitionGroupName,

                '',

                positivePayFormat.DMFSourceName,

                #FieldGroupName_AllFields,

                query.pack(),

                curExt(),

                xsltFileList,

                true,

                false);

            if (fileId != '')

            {

                this.sendFileToDestination();

                DMFDefinitionGroup::find(definitionGroupName, true).delete();

                isGenerated = true;

            }

            else

            {

                // DMF execution failed and details were written to the execution log

                throw error("@CashManagement:DMFExportCallFailedToExecutionLog");

            }

        }

        catch

        {

            //

        }

        return isGenerated;

Error On data entity -> The data value violates integrity constraints in D365FO

Error: The data value violates integrity constraints

Solution:

1. Remove blank lines on sample file, If any (If in case, CSV or excel) or  Copy content alone from EXCEL or CSV, Create new workbook and paste it

2. Check all mandatory fields value filled in on input file or not and also check any index violation (duplicate values/Record)