How to add new data source to On-hand List Form in D365FO.

 I got a requirement to add Item group field in On-hand List Form. For this I have followed the below process.

Item group is stored in ' InventItemGroupItem' Table.

Step 1:  Added relation in InventSum table.

Step 2:  Added InventItemGroupItem table in form data source and need to given the below properties.

Step 3:  Write the below code in 'modifyQueryBasedOnDatasourceName' method.

[ExtensionOf(classStr(InventDimCtrl_Frm_OnHand))]
final class InventDimCtrl_Frm_OnHand_Extension 
{
    public void modifyQueryBasedOnDatasourceName(
        Query          _inventSum_DS_Query,
        str            _inventSum_DS_Name,
        FormDataSource _inventDim_DS)
    {
        next modifyQueryBasedOnDatasourceName(_inventSum_DS_Query,_inventSum_DS_Name,_inventDim_DS);

        QueryBuildDataSource qbds = _inventSum_DS_Query.dataSourceTable(tableNum(InventItemGroupItem));
        qbds.addGroupByField(fieldNum(inventItemGroupItem,ItemGroupId));
        qbds.addGroupByField(fieldNum(inventItemGroupItem,itemdataAreaID));
    }

}

Keep Daxing!!

How to fetch records to Include filter values using x++

 Hi guys, I have a requirement to fetch the record to include filed values from the report dialog.

I need to show those values in the report footer.

 Code :

        str                         value;
        Query                       query = New Query();
        QueryBuildDataSource        qbds;
        QueryBuildRange             qbr;
        int                         totalDS, totalRanges;
        QueryFilter                 qf;

        query             = qr.query();// getting query from query run.
        totalDS           = query.dataSourceCount();

        int  cnt = query.queryFilterCount();

        // Fetching data from query if any new tables are joined or new Fileds are added
        for (int ds =1; ds<= cnt; ds++)
        {
            qf = query.queryFilter(ds);

            if(qf.value())
            {
                value += strFmt('%1 : %2   ',
                                qf.field(),
                                qf.value());
            }
        }

        // Used to fectch data from the standard view query 
        for (int ds =1; ds<= totalDS; ds++)
        {
            qbds            = query.dataSourceNo(ds);
            totalRanges     = qbds.rangeCount();

            for (int r =1; r<= totalRanges; r++)
            {
                qbr =  qbds.range(r);
                if (qbr.value())
                {
                    // Way 1 : 
                    // qbr.prompt();
                    value += strFmt('%1 : %2  ',
                                       qbr.prompt(),
                                        qbr.value());

                    // Way2 :
                    TableId     tableId =  qbds.table();//tableName2Id(qbds.AOTname());
                    DictTable   dictTable = new SysDictTable(tableId);

                    str fieldname = qbr.AOTname();

                    if(qbr.value())
                    {
                        MiscParameters += strFmt('%1 : %2  ',
                                      dictTable.fieldObject(fieldName2Id(tableId, qbr.fieldName())).label(),
                                qbr.value());
                       
                    }

                    // Way3 :
                    // Direct Field
                    FieldId  fieldId = qbr.field();
                    if(qbr.value())
                    {
                        MiscParameters += strFmt('%1 : %2  ',
                                      dictTable.fieldObject(fieldId).label(),
                                qbr.value());
                              
                    }

                    // Way4 :
                    str     value = strFmt('%1', qbr.value());
                    Map     miscParametersMap = new Map(Types::String, Types::String);// Write this line at out side of loop

                    if (value)
                    {
                        if (miscParametersMap.exists(qbr.prompt()))
                        {
                            container   con = str2con(miscParametersMap.lookup(qbr.prompt()), ',' , false);

                            if (!conFind(con, value))
                            {
                                con += value;
                            }

                            miscParametersMap.insert(qbr.prompt(), con2Str(con));
                        }
                        else
                        {
                            miscParametersMap.insert(qbr.prompt(), value);
                        }
                    }

                    if (miscParametersMap.elements())
                    {
                        MapEnumerator   mapEnumerator = miscParametersMap.getEnumerator();

                        while (mapEnumerator.moveNext())
                        {
                            value += (strfmt("%1 : %2  ",
                                        mapEnumerator.currentKey(),
                                        mapEnumerator.currentValue()));
                        }
                    }

                }
            }
        }

Keep Daxing!!

Display the currency sign in Report

 Hi guys, Today I got the requirement to showcase the currency symbol in the report after that amount based on currency.

Below is the path for currency symbol.

General Ledger -> Currency -> Currencies form

For getting this 

• I have created a new field in the temp table.
• I have added the below code in the DP class.

myTemptable.Symbol = Currency::find(myTempTable.CurrencyCode).Symbol;

After that, I added the below expression in the report Design. (Anyone we can use)

=Format(Fields!Amount.Value, "N2") & space(1) & Fields!CurrencySymbol.Value

=Format(Fields!Amount.Value & " " & Fields!CurrencySymbol.Value)

Keep Daxing!!

Export report in excel using print management using x++

 Export report in excel using print management using x++.

I got a requirement to export the report in excel. But while exporting reports they want that in 2 sheets like below.

        1st sheet.

        2nd sheet.

• I have created a small design like the below.

Below are properties we need to set.

•         Table properties :(right-click on table)

• Table properties :(Alt + Enter) right side proerties

• print mgmnt properties :

• File properties :

Keep Daxing!! 

Trial balance report export to share point using x++

Trial balance report export to share point using x++.

using Microsoft.Dynamics.Platform.Integration.SharePoint;

[SRSReportParameterAttribute(classStr(TrialBalanceMainAccountReportContract))]
public class TrialBalanceMainAccountReportDP extends SrsReportDataProviderPreProcessTempDB
{
    #Define.BusinessUnitDim('BusinessUnit')
    #Define.DepartmentDim('Department')
    #Define.ExcelExtension('.xlsx')

    TrialBalanceMainAccountReportTmp        TrialBalanceMainAccountReportTmp;
    LedgerTrialBalanceTmp                   ledgerTrialBalanceTmp;
    TrialBalanceMainAccountReportContract   contract;
    Name                                    primaryDimensionFocus;
	
   
    public void setTrialBalanceTmpTable(TrialBalanceMainAccountReportTmp _TrialBalanceMainAccountReportTmp)
    {
        TrialBalanceMainAccountReportTmp.linkPhysicalTableInstance(_TrialBalanceMainAccountReportTmp);
    }

    [SRSReportDataSetAttribute(tablestr(TrialBalanceMainAccountReportTmp))]
    public TrialBalanceMainAccountReportTmp getDocAgreementHeader()
    {
        select TrialBalanceMainAccountReportTmp;
        return TrialBalanceMainAccountReportTmp;
    }

    public void processData()
    {
        FiscalCalendarPeriod                fiscalCalendarStartPeriod;
        GeneralJournalAccountEntry          generalJournalAccountEntryHeader, generalJournalAccountEntryLine;
        GeneralJournalEntry                 generalJournalEntryHeader, generalJournalEntryLine;
        DimensionAttributeValueCombination  dimensionAttributeValueCombination;
        LedgerJournalTrans                  ledgerJournalTrans;
        LedgerTrialBalanceTmp               ledgerTrialBalanceTmpReference;
        MainAccount                         mainAccount;
        AmountMST                           openingBalance, creditAmount, debitAmount, closingBalance;
        Name                                dimensionAttributeName;
        DimensionValue                      dimensionValue;
        boolean                             insertData = false;
        boolean                             displayOnlyBalances = true;
        CurrentOperationsTax                postingLayer;

        contract = this.parmDataContract() as TrialBalanceMainAccountReportContract;

        TransDate               FromDate            = contract.parmFromDate();
        TransDate               ToDate              = contract.parmToDate();
        TransDate               periodStartDate     = LedgerFiscalCalendar::findOpeningStartDateByDate(Ledger::fiscalCalendar(CompanyInfo::current()), FromDate);

        delete_from TrialBalanceMainAccountReportTmp;

		//  Based on date range looping records in 'generalJournalAccountEntryHeader' table.(Voucher transactions)
        while select MainAccount, LedgerDimension from  generalJournalAccountEntryHeader
            group by generalJournalAccountEntryHeader.LedgerDimension ,generalJournalAccountEntryHeader.MainAccount
        join generalJournalEntryHeader
            where generalJournalAccountEntryHeader.GeneralJournalEntry == generalJournalEntryHeader.RecId
                && generalJournalEntryHeader.AccountingDate >= fromDate
                && generalJournalEntryHeader.AccountingDate <= toDate
        {
            FiscalCalendarPeriod fiscalCalendarPeriod;
           
            //Debit balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount  from generalJournalAccountEntryLine
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
                    && generalJournalAccountEntryLine.LedgerDimension == generalJournalAccountEntryHeader.LedgerDimension
                    && generalJournalAccountEntryLine.IsCredit == NoYes::No
                join generalJournalEntryLine
                where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                    && generalJournalEntryLine.AccountingDate >= fromDate
                    && generalJournalEntryLine.AccountingDate <= toDate
                    && generalJournalEntryLine.Ledger == Ledger::current()
                exists join fiscalCalendarPeriod
                where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                    && fiscalCalendarPeriod.Type == FiscalPeriodType::Operating;

            debitAmount = generalJournalAccountEntryLine.AccountingCurrencyAmount;

            //Credit balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount from generalJournalAccountEntryLine
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
                    && generalJournalAccountEntryLine.LedgerDimension == generalJournalAccountEntryHeader.LedgerDimension
                    && generalJournalAccountEntryLine.IsCredit == NoYes::Yes
                join generalJournalEntryLine
                where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                    && generalJournalEntryLine.AccountingDate >= fromDate
                    && generalJournalEntryLine.AccountingDate <= toDate
                    && generalJournalEntryLine.Ledger == Ledger::current()
                exists join fiscalCalendarPeriod
                where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                    && fiscalCalendarPeriod.Type == FiscalPeriodType::Operating;

            creditAmount = generalJournalAccountEntryLine.AccountingCurrencyAmount;

            //Opening balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount from generalJournalAccountEntryLine
                group by generalJournalAccountEntryLine.MainAccount, generalJournalAccountEntryLine.LedgerAccount
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
            join generalJournalEntryLine
            where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                && generalJournalEntryLine.Ledger == Ledger::current()
                && generalJournalEntryLine.AccountingDate >= fromDate
                && generalJournalEntryLine.AccountingDate <= ToDate
                 exists join fiscalCalendarPeriod
            where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                && fiscalCalendarPeriod.Type == FiscalPeriodType::Opening;

            openingBalance  = generalJournalAccountEntryLine.AccountingCurrencyAmount;
            closingBalance  = openingBalance + debitAmount + creditAmount;

            str  mainAccountId, MainAcountName;

            mainAccountId = MainAccount::find(generalJournalAccountEntryHeader.MainAccount).MainAccountId;
            MainAcountName = MainAccount::find(generalJournalAccountEntryHeader.MainAccount).Name;

            //info(strFmt("Id %1, Name %2, Debit: %3, Credit%4, LedgerDim:%5, opnBl%6, CloseBl%7", mainAccountId, 
                    MainAcountName, drAmt, CrAmt, generalJournalAccountEntry.LedgerAccount,opnBl, CloseBl ));

            TrialBalanceMainAccountReportTmp.FromDate              = FromDate;
            TrialBalanceMainAccountReportTmp.ToDate                = ToDate;
            TrialBalanceMainAccountReportTmp.MainAccount           = str2Int64(mainAccountId);
            TrialBalanceMainAccountReportTmp.Name                  = MainAcountName;
            TrialBalanceMainAccountReportTmp.OpeningBalance        = openingBalance;
            TrialBalanceMainAccountReportTmp.ClosingTransactions   = closingBalance;
            TrialBalanceMainAccountReportTmp.AmountCredit          = creditAmount;
            TrialBalanceMainAccountReportTmp.AmountDebit           = debitAmount;

            // Fetching Dimension Values
            if (generalJournalAccountEntryHeader.LedgerDimension)
            {
                TrialBalanceMainAccountReportTmp.BusinessUnit       = this.getDefaultDimensionValue(generalJournalAccountEntryHeader.LedgerDimension, #BusinessUnitDim);
                TrialBalanceMainAccountReportTmp.Department         = this.getDefaultDimensionValue(generalJournalAccountEntryHeader.LedgerDimension, #DepartmentDim);
            }
            TrialBalanceMainAccountReportTmp.insert();
        }
        this.uploadOnSharePoint();
    }

    public DimensionValue getDefaultDimensionValue(DimensionDefault   _defaultDimension, Name  _dimensionName)
    {
        DimensionAttributeValueSetStorage   dimStorage;

        dimStorage = DimensionAttributeValueSetStorage::find(_defaultDimension);

        return dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName(_dimensionName).RecId);
    }

    public void uploadOnSharePoint()
    {
        System.IO.MemoryStream      memoryStream;
        int                         row;
        int                         col;
        Filename                    fileNameExt;
        System.Exception            ex;
        str                         errorMessage = '';

        try
        {
            ttsbegin;
            memoryStream = new System.IO.MemoryStream();

            row =   1;
        
            using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
            {
                var sheet = package.get_Workbook().get_Worksheets();
                var worksheet = sheet.Add("Hyperion trail balance"); // adding new work sheet.
                var cells = worksheet.get_Cells();
                OfficeOpenXml.Style.ExcelStyle style;
                OfficeOpenXml.Style.ExcelFont font;
				
				//adding colum names
                Col = 1;
                var cell = Cells.get_item(row,Col);
                cell.set_Value("Main Account");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Business Unit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Department");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Name");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Opening Balance");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Debit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Credit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Closing Balance");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                while select TrialBalanceMainAccountReportTmp
                {
                    row++;
                    Col = 1;
                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.MainAccount);
                    col++;
                
                    cell = Cells.get_item(row,col);
                    cell.set_Value((TrialBalanceMainAccountReportTmp.BusinessUnit));
                    col++;
                
                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.Department);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.Name);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.OpeningBalance);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.AmountDebit);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.AmountCredit);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.ClosingTransactions);
                    col++;
                }
                package.save();
            }

            memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
			
			// File name
            fileNameExt = 'Hyperiod Trail balance';
            fileNameExt =  subStr(fileNameExt, 1, 30) + #ExcelExtension;
			
			str fileContentType = System.Web.MimeMapping::GetMimeMapping(fileNameExt);
			
			//path
            str folderPath = "/Shared Documents/Integration/Input";

            ISharePointProxy    proxy = null;
            str                 src = '';
            str                 hostName = '';

			src = ' https://'domain name'.sharepoint.com';// 
				
			System.UriBuilder builder = new System.UriBuilder(src);
			hostName = builder.Host;
			str externalId = xUserInfo::getCurrentUserExternalId();

			try
			{
				proxy = SharePointHelper::CreateProxy(hostName, '/', externalId);
			}
			catch(Exception::CLRError)
			{
				proxy = null;
			}

            if(proxy)
            {
                Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider prov =
                    new Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider(proxy, folderPath);
                    
                prov.SaveFileWithOverwrite(newguid(), fileNameExt, fileContentType, memoryStream);
            }
            else
            {
                throw Error("Sharepoint Connection Error");
            }
            ttscommit;
        }
        catch (ex)
        {
            System.Exception e = ex;
            while (e != null)
            {
                errorMessage += e.Message;
                e = e.InnerException;
            }

            if (appl.ttsLevel() > 0)
            {
                ttsabort;
            }

            checkFailed("Process failed");
            error(errorMessage);
        }
    }

}

Trial balance using Standard class:

    LedgerTrialBalanceListPageBalanceParms 	balanceParameters;
    DimensionHierarchy 			        dimHier;
    LedgerTrialBalanceTmp   			LedgerTrialBalanceTmp;
    TransDate                                   fromDate,toDate;
    List 					selected = new List(Types::Enum);
 
    selected.addEnd(CurrentOperationsTax::Current);

    dimHier = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, 'Main account set');
	
    balanceParameters = LedgerTrialBalanceListPageBalanceParms::construct();
	
    balanceParameters.parmDateCode('');
    balanceParameters.parmDimensionSetHierarchy(dimHier);
    balanceParameters.parmStartDate(fromDate);
    balanceParameters.parmEndDate(toDate);
    balanceParameters.parmPostingLayers(selected);

    select firstOnly RecId from LedgerTrialBalanceTmp;

    LedgerTrialBalanceTmp::calculateBalances_V2(
						LedgerTrialBalanceTmp,
						dimHier.Name,
						balanceParameters.getStartDate(),
						balanceParameters.getEndDate(),
						balanceParameters.getIncludeOpeningPeriods(),
						balanceParameters.getIncludeClosingAdjustments(),
						balanceParameters.getIncludeClosingTransactions(),
						balanceParameters.getPostingLayers(),
						true,//ShowErrors,
						true,// runUpdateBalancesInNewSession,
						Ledger::primaryLedger(CompanyInfo::findDataArea('other company').RecId),//Ledger::current(),// current comapny.
						balanceParameters.getDisplayMainAccountCategory());


	while select LedgerTrialBalanceTmp
	    where LedgerTrialBalanceTmp.EndingBalance != 0
	{
	    info(strfmt('%1:%2', LedgerTrialBalanceTmp.DimensionValues[1],LedgerTrialBalanceTmp.EndingBalance))
	}

Keep Daxing!!

How to add fields to the form DirPartyQuickCreateForm in D365FO

 I got a requirement to add few fields while creating customer and those fields data should be auto populated from customer group.

• To create customer/vendor the standard system will call the  DirPartyQuickCreateForm. 
• We can't add fields directly to this form. It will not work and we can't get the control's in logic.

Solution :

•     From form inti method the below will trigger.

        

• Here They are calling field groups in table.
• If you open the table you can find that groups.

            

•  You can drag the required fields to the above mentioned groups.

• For my requirement I have add the field in cust table and cust group and dragged in to that group.

• For auto population I have added the below code in modified method

[ExtensionOf(tableStr(CustTable))]
final class custTable_Extension
{    
   public void modifiedField(FieldId _fieldId)   
   {        
	next modifiedField(_fieldId);

        switch(_fieldId)
        {
            case fieldNum(CustTable, CustGroup):
                this.MarkGUA = CustGroup::find(this.CustGroup).MarkGUA ;
                break;
        }
    }
 }

OuptPut :

Keep daxing!!

Add multiple fields data in one control in Report using x++

 I got a requirement to add multiple fields data in one control in Report

• Create new control in design and select the expression.
• Write the below code based on your fields.

=First(Fields!Field1.Value, "MyTable") & vbcrlf &  
First(Fields!Field2.Value, "MyTable")  & vbcrlf &
First(Fields!Field3.Value, "MyTable")

• Check the output.

Keep daxing!!

How to add a new dataset to SalesInvoice report in D365FO.

 Steps for adding new data source in salesInvoice report.

• created New temp / regular table. Set the CreateTransactionId property to Yes in table and sync it.
• For DP class we have 2 ways.

• Way 1 : Using COC.

[ExtensionOf(classStr(SalesInvoiceDP))]
final class MySalesInvoiceDP_Extension
{   public MySalesInvoiceTmp     mySalesInvoiceTmp;

    [SRSReportDataSetAttribute(tableStr(MySalesInvoiceTmp))]
    public MySalesInvoiceTmp getMySalesInvoiceTmp()
    {
        select mySalesInvoiceTmp;
        return mySalesInvoiceTmp;
    }
}  

• Way 2 : Using Extends

[SRSReportParameterAttribute(classStr(SalesInvoiceContract))]
class mySalesInvoiceDP extends SalesInvoiceDP
{
      public MySalesInvoiceTmp     mySalesInvoiceTmp;
    [SRSReportDataSetAttribute(tableStr(MySalesInvoiceTmp))]
    public MySalesInvoiceTmp getMySalesInvoiceTmp()
    {
        select mySalesInvoiceTmp;
        return mySalesInvoiceTmp;
    }
}

Methods:

protected void populateSalesInvoiceTmp(CustInvoiceJour _custInvoiceJour,
        CustInvoiceTrans _custInvoiceTrans,
        TaxSpec _taxSpec,
        CustPaymSchedLine _custPaymSchedLine,
        CustTrans _prepaymentCustTrans,
        TaxTrans _prepaymentTaxTrans)
    {
	// For extends
        super(_custInvoiceJour, _custInvoiceTrans, _taxSpec,
		 _custPaymSchedLine, _prepaymentCustTrans, _prepaymentTaxTrans);
	
	//COC you use
	next populateSalesInvoiceTmp(_custInvoiceJour, _custInvoiceTrans, _taxSpec,
		 _custPaymSchedLine, _prepaymentCustTrans, _prepaymentTaxTrans);

	//Write the code here If your added new fields in salesInvoiceTmp table
	//If some data exist in salesInvoiceTmp table this method will not trigger
	//So you need to delete existing records   
    }

   protected void populateSalesInvoiceHeaderFooterTmp(CustInvoiceJour _custInvoiceJour, 
                                                        CompanyInfo _companyInfo)
   {
      // For extends
      super(_custInvoiceJour, _companyInfo);

      //COC you use
	next populateSalesInvoiceTmp(_custInvoiceJour, _custInvoiceTrans, _taxSpec,
		 _custPaymSchedLine, _prepaymentCustTrans, _prepaymentTaxTrans);
    
     //Write the code here If your added new fields insalesInvoiceTmp
   }

 

•  Duplicate standard SalesInvoice report and create custom design.
• Right click on dataSets and add a new dataset. set the datasource type as Report data provider, dynamic filters = yes. select SaleSInvoiceDP in options and click next. 
• Select your data source.
• Add that data source in design and your fields. 

Print Management :

•     PrintmgmtDocType

final static class MyPrintMgmtDocType_EventHandler
{     
    [SubscribesTo(classstr(PrintMgmtDocType), delegatestr(PrintMgmtDocType, getDefaultReportFormatDelegate))]
    public static void getDefaultReportFormatDelegate(PrintMgmtDocumentType _docType, EventHandlerResult _result)
    {   switch (_docType)
        {
            case PrintMgmtDocumentType::SalesOrderInvoice:
                _result.result(ssrsReportStr(MySalesInvoice, Report));
                break;
        }  
    } 
}  

If you want add new element in print management write below code.

[ExtensionOf(classstr(PrintMgmtReportFormatPopulator))]
final class MyPrintMgmtReportFormat_Extension
{
    protected void addDocuments()
    {
        this.addStandard(PrintMgmtDocumentType::SalesOrderInvoice);
        next addDocuments();
    } 
}    

Run the below line in job.

PrintMgmtReportFormatPopulator::construct().populate();    

Keep daxing!!

Row visibility based one field in report using X++

Row visibility is based on one field in the report using X++.

• Select the whole row and right click and select properties.
• Select row visibility properties and add the below code.

=IIF(Fields!AccountNum.Value = "", true, false)   

AccountNum is my field. Use your field.

Column visibility 

• Select the whole column and right click and select properties.
• Select visibility properties and add the below code.

=Parameters!parmcompany1.Value = "" 

Keep daxing!!
 

Get sales totals amount by using x++ code

 Get sales totals amount by  using x++ code.

    //Total Discount from Sales Totals form
    SalesTable      salesTable      = SalesTable::find('sales order number');
    SalesTotals     salesTotals     = SalesTotals::construct(salesTable, salesUpdate::All);
    container       displayFields;
    DiscAmount      totalDisc;
    RealBase        subTotal;
    AmountCur       invoiceAmount;
    AmountMST       salesTax;
    AmountMST       totalCharges;

    displayFields = SalesTotals::displayFieldsServer(salesTable, 
                                                      SalesUpdate::All, 
                                                      salesTotals.CurrencyCode());
    //Total Discount
    totalDisc = conpeek(displayFields, TradeTotals::posEndDisc());
    
    info(Strfmt("Total discount %1", totalDisc));

    //Subtotal Amount
    subTotal=conpeek(displayFields, TradeTotals::posBalance());
    
    info(Strfmt("Subtotal Amount %1", subTotal));

    //Invoice Amount
    invoiceAmount =conpeek(displayFields, TradeTotals::posTotalAmount());
    
    info(Strfmt("Invoice Amount %1", invoiceAmount));
   //sales Tax
   salesTax = conpeek(displayFields, TradeTotals::posTaxTotal());
    
   info(Strfmt("sales Tax %1", salesTax));
 //Total Charges
  totalCharges = conpeek(displayFields, TradeTotals::posMarkup());
    
  info(Strfmt("Total Charges %1", totalCharges));

Keep Daxing!!

Convert Image to Base64 string in D365FO

 Convert Image to Base64 string in D365FO.

    HcmWorker           HcmWorker;
    HcmPersonImage      HcmPersonImage;
    ExecutePermission   perm;
    container           imgObj;
    BinData             bd;
    Notes               result;

    new InteropPermission(InteropKind::ClrInterop).assert();
    perm = new ExecutePermission();
    perm.assert();

    HcmWorker       = HcmWorker::findByPersonnelNumber('employeeId');
    HcmPersonImage  = HcmPersonImage::findByPerson(HcmWorker.Person);
    
    imgObj = HcmPersonImage.Image;
    bd = new BinData();
    bd.setData(imgObj);
    
    result = bd.base64Encode();
    //ssPermission::revertAssert();

Keep Daxing!!

Get workflow pending Approval Name using x++ in D365FO

 Get workflow pending Approval Name using x++ in D365FO.

display HcmWorkerName displayWorkflowApproverName()
{
    WorkflowWorkItemTable workItemTable;

    select workItemTable 
        where workItemTable.RefRecId == this.RecId 
           && workItemTable.RefTableId == this.TableId &&
            ((workItemTable.Status == WorkflowWorkItemStatus::Pending) 
                || (workItemTable.Status == WorkflowWorkItemStatus::Delegated));

    return HcmWorker::find(HcmWorker::userId2Worker(workItemTable.UserId)).name();
}

Keep Daxing!!

convert date time to string using x++ In D365FO

 Convert date time to string using x++ In D365FO.

static void dateToStr(Args _args)
{
    UtcDateTime     dateTime = DateTimeUtil::getSystemDateTime();

    DateTimeUtil::toStr(dateTime);// Convert to str : "2022-06-15T13:00:30.0900000"

---or---
    // If you want special characters.

    system.DateTime sysDateTime;

    sysDateTime =  dateTime;

    print sysDateTime.ToString("yyyy-MM-dd");
    print sysDateTime.ToString("MM/dd/yy H:mm:ss zzz");

----or----
use date2Str:

    str datestr;

    datestr = date2Str(today(),
                        321,
                        DateDay::Digits2,
                        DateSeparator::Hyphen, // separator1
                        DateMonth::Digits2,
                        DateSeparator::Hyphen, // separator2
                        DateYear::Digits4);
    
    info('Today is: ' + datestr);

}

---Or---
    utcDateTime     utcDateTimeFromString   = DateTimeUtil::anyToDateTime("2012-04-01T00:00:00");
    utcDateTime     utcDateTimeISOFormat    = 2012-04-01T00:00:00;
    utcDateTime     utcNow;
    System.DateTime systemDateTime;
    date            dateOnly;
    str             systemDateTimeStr;

    // AX UTC to System.DateTime: Result: "7/17/2022 3:57:25 AM"
    systemDateTime      = Global::utcDateTime2SystemDateTime(DateTimeUtil::utcNow());

    //System.DateTime to AX UTC: Result:"7/17/2022 03:57:52 am"
    utcNow              = Global::clrSystemDateTime2UtcDateTime(System.DateTime::get_UtcNow());

    // date portion from a UTC:  Result:"7/17/2022"
    dateOnly            = DateTimeUtil::date(utcNow);

    // Cast to string for output
    systemDateTimeStr   = systemDateTime.ToString();
     
    info(strFmt("%1, %2, %3",
                systemDateTimeStr,
                utcNow,
                dateOnly));
    
    info(strFmt("%1, %2",
                DateTimeUtil::toStr(DateTimeUtil::utcNow()),//2022-07-17T04:00:58
                DateTimeUtil::toStr(utcNow)));// 2022-07-17T04:00:59

Keep Daxing!!

Generate Bearer token using x++ in D365FO.

  Generate Bearer token using x++ in D365FO.

• While triggering API we need to generate a token.
• While calling their API We need to send the token, So they will validate that Token and send the response data If i.e. valid.
• To Generate Token we need
• Client Id
• Client secret
• Tenant Id
• For the above details, we need to do the app registration in the Azure portal.
• We need to create one record with the client id in the Azure application in the system administration module.

    AuthenticationResult        authResult; 

        
    ClientCredential        clientCrendential   = new ClientCredential('ClientId', 'ClientSecretKey');
    AuthenticationContext   authContext         = new AuthenticationContext(url);
            // https://login.microsoftonline.com/tenantId/oauth2/token
        
    authResult  = authContext.AcquireToken('Scope or Resource(URL)', clientCrendential);
        
    token =  authResult.AccessToken;

    Info(token);

Scope: ABCD/.default

--> api//:ABCD/.default 

    --> We need to remove api//: 

    --> Otherwise it will throw the error.

Keep Daxing!!

Generate token from key vault parameters using x++ in D365FO.

 Generate token from key vault parameters using x++ in D365FO.

•  We are saving our token URL in Key vault for security. By using the below code we will get the URL form Key Vault. 
• We have used same client ID and client secret for key vault also. So I have configured that in key vault parameters. 

    
    str                           url, token;
    KeyVaultParameters            vaultParameter;
    KeyVaultCertificateTable      vaultCertificate;

    select vaultParameter where vaultParameter.Name == 'Name';// which we have created in Key vault.

    if (vaultParameter)
    {
        select vaultCertificate where vaultCertificate.KeyVaultRef == vaultParameter.RecId;

        KeyVaultClientHelper::removeCachedKeyVaultClient(vaultCertificate.KeyVaultCertificate);

        url = KeyVaultCertificateHelper::getManualSecretValue(vaultCertificate.RecId);
        // Here KeyVault will generate the Token URL.

            
        AuthenticationResult        authResult; 

        
        ClientCredential        clientCrendential   = new ClientCredential(
                    vaultParameter.AppClientId, vaultParameter.getClientSecretKey());
                    // Based on client Id and client secret key we have created Key vault.
        AuthenticationContext   authContext         = new AuthenticationContext(url);
        
        authResult  = authContext.AcquireToken('Scope or Resource(URL)', clientCrendential);
        
        token =  authResult.AccessToken;

        Info(token);
    }

Keep Daxing!!

Get the previous value and current value If user changed any Record on form in D365FO

Hi guys, Today I got a requirement If user is changed any value in user info form that time I need to capture the previous value and current value.

For this I have created new table and I have inserting data into that table by using below class.

This class I am calling from write method in form data source.

public class UserModificationLog
{

    public static void updateLogTable(Common    _common)// This method I am calling from write method.
    {
        
        switch (_common.tableId)
        {
            case tablenum(UserInfo) :
                UserModificationLog::updateUserLog(_common);
                break;
        }
    }

    public static void updateUserLog(UserInfo    _userInfo)
    {
        DictTable   dictTable     = new sysDictTable(tableNum(UserInfo));
        //DictTable   dictTable     = new sysDictTable(_userInfo.TableId);
        fieldId     fieldId       = dictTable.fieldNext(0);
        dictField   dictField;
        UserInfo    userInfoOrg   = _userInfo.orig();

        while(fieldId)
        {
            if (userInfo.(fieldId) != userInfoOrg.(fieldId))
                //|| userInfo.(fieldId) != _userInfo.orig().(fieldId))
            {
                UserModifications    userModifications = 
                                    UserModificationLog::getUserModifications(fieldId);

                if (userModifications != UserModifications::None)
                {
                    UserModificationLog::insertData(_userInfo, userInfoOrg, fieldId,
                                                     dictTable.fieldObject(fieldId).name(),
                                                     userModifications);
                }
            }         

            fieldId = dictTable.fieldNext(fieldId);
        } 
   }

    public static void insertData(Common    common, Common    orgCommon, 
                                    fieldId     fieldId, str fieldName, 
                                    UserModifications  _userModifications)
    {
        SecurityUserRolesLogNew roleLocation;

        roleLocation.clear();
        roleLocation.ModifiedByUserId           = curUserId();
        roleLocation.Company                    = curExt();
        roleLocation.ModifiedDateTim            = DateTimeUtil::getSystemDateTime();
        roleLocation.UserId                     = _common.(fieldNum(UserInfo, id));
        roleLocation.UpdatedRecord              = any2Str(_common.(_fieldId));
        roleLocation.PreviousRecord             = any2Str(_orgCommon.(_fieldId));
        roleLocation.UserModifications          = _userModifications;
                                    //UserModificationLog::getUserModifications(_fieldId);
        roleLocation.insert();
    }

}         

 Keep Daxing!!

Get the company logo and company name in SSRS report in D365FO.

 Get the company logo and company name in SSRS report in D365FO.

          

   CompanyInfo companyInfo = CompanyInfo::find();
    tmpTable.CompanyLogo = FormLetter::companyLogo();
                //CompanyImage::findByRecord(CompanyInfo::find()).Image;
    tmpTable.CompanyName = companyInfo.Name; 

Keep Daxing!!

Address in D365Fo

Address in D365Fo.

Vendor address: 

    Info(strFmt('Postal - %1', VendTable::find('Vendor Id').postalAddress().Address));

    Info(strFmt('Delivey - %1', VendTable::find('Vendor Id').deliveryAddress().Address));

    Info(strFmt('Invoice - %1', VendTable::find('Vendor Id').invoiceAddress().Address));

Cusomer address:

    Info(strFmt('Postal - %1', CustTable::find('Customer Id').postalAddress().Address));

    Info(strFmt('Postal - %1', CustTable::find('Customer Id').address()));

    Info(strFmt('Invoice - %1', CustTable::find('Customer Id').invoiceAddress().Address));

Warehouse address:

    Info(strFmt('Address - %1', InventLocation::find('InventLocationId').
                                                    logisticsPostalAddress().Address));

    Info(strFmt('Address - %1', InventLocation::find('InventLocationId').Address())); 

    InventLocationLogisticsLocation        inventLocationLogisticsLocation;
    RecId                                  inventLocationRecId;        
    
    Select firstOnly inventLocationLogisticsLocation
        where inventLocationLogisticsLocation.InventLocation == inventLocationRecId
           && inventLocationLogisticsLocation.IsPrimary == NoYes::Yes;

    LogisticsLocation       logisticsLocation = LogisticsLocation::find(
                                                    inventLocationLogisticsLocation.Location);
    
    LogisticsPostalAddress  logisticsPostalAddress  = LogisticsPostalAddress::findRecId(
                                                                    logisticsLocation.RecId);

    Info(strFmt('Address - %1', logisticsPostalAddress.Address));

Site address:

    Info(strFmt('Address - %1', InventSite::find('Site Id').logisticsPostalAddress().Address));

Different Way to Get any Active address:

    LogisticsLocationEntity::location2PostalAddress(transTaxInformation.CompanyLocation, 
                                            DateTimeUtil::getSystemDateTime(), true)

Keep Daxing!! 

How to display information only on the last page within the page footer in SSRS

How to display information only on the last page within the page footer in SSRS.

In the page footer add rectangle and in the hidden property paste the below one.

=IIF(Globals!PageNumber=Globals!TotalPages,false,true)

Keep Daxing!!

How to add Records to include option on report dialog in D365FO

 How to add Records to include option on report dialog in D365FO. Some times user want to apply the filter from UI. That time we need to use Records to include option.

• In Report -- data set properties -- Dynamic Filters (set as True)
• Need to create static query.
• That query we need to call from DP class. 
• After that Need to write logic in controller class.

Controller class: 

    public class new MyReportController extends SrsReportRunController
      {
            public static void main(Args args)
            {
                MyReportController  controller = new new MyReportController();

                controller.parmReportName(ssrsReportStr(MyReport, Report));
                controller.parmArgs(args);

                if(args && args.dataset() == tableNum(MyTable))
                {
                    MyTable     myTable = _args.record();
                    Query       query;
                    // Get Contract
                    //controller.getDataContractObject()//// for sys operation
                    //controller.parmReportContract().parmRdpContract() // For Report

                    query = controller.getFirstQuery();// get the query from dp class

                    query.dataSourceTable(tableNum(MyTable)).clearRanges();
                    query.dataSourceTable(tableNum(MyTable)).addRange(fieldNum(MyTable,
                                    Id)).value(queryValue(myTable.Id));
                    query.dataSourceTable(tableNum(MyTable)).addRange(fieldNum(MyTable,
                                    Status)).value(queryValue(MyTable.Status));
                }
                controller.startOperation();
            }
        }    

        --------------------------------or--------------------------------------------

        public class new MyReportController extends SrsReportRunController
        {
            public void prePromptModifyContract()
            {
                //add a range in the report query
                SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),
                        tableNum(Mytable), fieldNum(Mytable, RecId),
                        SysQuery::value(this.parmArgs().record().RecId));
            }
        }

        -------------------------------------------------------------------------------
    
     public void runReport()
    {
	myContract contract;
	queryRun   queryRun = new queryRun(this.getFirstQuery());
	int        i = 1;

	contract = this.parmReportContract().parmRdpContract();

	contract.intialize();

	while (queryRun.next())
	{
	    CompanyInfo     companyInfo = queryRun.GetNo(1);

	    switch (i)
	    {
		case 1 : contract.parmcompany1(companyInfo.NameAlias); break;
		case 2 : contract.parmcompany2(companyInfo.NameAlias); break;
		case 3 : contract.parmcompany3(companyInfo.NameAlias); break;
		case 4 : contract.parmcompany4(companyInfo.NameAlias); break;
		case 5 : contract.parmcompany5(companyInfo.NameAlias); break;
	    }

	    i++;
	}

       super();
    }
         

Dp class: 

    [
    SrsReportQueryAttribute(queryStr(TestQuery)),// This line we need to add
    SrsReportParameterAttribute(classStr(TestContract))
    ]
    class TestDP extends SRSReportDataProviderBase
    {
        TestTmp     testTmp;

        public void processReport()
        {
            TestQuery    testQuery = this.parmQuery();// get the query       
            queryRun     queryRun = new queryRun(testQuery));

            while(queryRun.next())
            {

            }
        }
    }

        

Keep Daxing!!