SQL Keywords

STRING_AGG

SQL Query:

select STRING_AGG(erc.Code, ';') as ProductLine From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Output: 

2;7;13;G;9;3;Z;11;17;D;B;F;8;6;1;05

Execute:

Execute ('

Declare @val Varchar(MAX)

Select @val = COALESCE(@val + '','' + erc.Code, erc.Code) 

        From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Group by erc.Code order by erc.Code asc

Select @val')

COALESCE

Declare @val Varchar(MAX)

Select @val = COALESCE(@val + ';' + erc.Code, erc.Code) 

        From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Group by erc.Code order by erc.Code asc

Select @val

Get ER format mapping from Print management destination setting in D365FO X++

ERFormatMappingId                   formatMapping;

 str defaultReportName = PrintMgmtDocType::construct(PrintMgmtDocumentType::SAN_CommercialInvoice).getDefaultReportFormat();

        if(defaultReportName)

        {

            int prefixLength = strLen(ERPrintMgmtSetupUISettingsCtlEventHandler::PrintManagementFormatPrefix);

            var formatGuid = str2Guid(subStr(defaultReportName, prefixLength + 1, strLen(defaultReportName) - prefixLength));

            formatMapping = ERFormatMappingTable::findByGUID(formatGuid).RecId;

        }