Tuesday, September 19, 2023

How to create a new custom financial dimension value with description and other parameters via X++

User can add values manually to Custom dimension attribute type only. For all other backing entities it should go via standard table creation, say, new CustTable record, etc.



The easiest way to create a new Custom list dimension value is to use the standard service DimensionValueService as follows. Say, you need to create a new value by using _newProjCategory record fields.

DimensionValueService dimensionValueService = new DimensionValueService();
DimensionValueContract dimensionValueContract = new DimensionValueContract();
dimensionValueContract.parmValue(_newProjCategory.Id);
dimensionValueContract.parmDimensionAttribute(myDimHelper::getProjCategoryAttribute);
dimensionValueContract.parmDescription(_newProjCategory.Name);

dimensionValueService.createDimensionValue(dimensionValueContract);

It creates the display value as its description.





 

Saturday, September 2, 2023

Ledger dimension vs Default dimension

 In short Ledger dimension it is Main account + default dimension.

So, if you need to replace any attribute value in a given Ledger dimension, do not forget to get a Default dimension first.

LedgerDimensionFacade::getDefaultDimensionFromLedgerDimension(ledgerjournalTrans.LedgerDimension);

Then you can use this old good way.

DimensionDefault newDim = DimensionHelper::setValueToDefaultDimension(hcmEmployment.DefaultDimension, DimensionAttribute::findByName(_dimensionName).RecId, _dimensionValue);
        

BTW, the opposite thing works like that:

LedgerDimensionFacade::serviceCreateLedgerDimension(ledgerDimensionMainAccount, inventTrans.defaultDimension);
Supporting method
public static DimensionDefault setValueToDefaultDimension(DimensionDefault _dimensionDefault, RefRecId _dimensionAttributeRecId, DimensionValue  _newDimensionValue)
    {
        DimensionAttributeValueSetStorage   dimStorage;
        DimensionDefault                    newDimensionDefault = _dimensionDefault;
        DimensionAttributeValue             dimensionAttributeValue;
        if (_dimensionAttributeRecId)
        {
            dimStorage = DimensionAttributeValueSetStorage::find(_dimensionDefault);
            if (_newDimensionValue)
            {
                dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::find(_dimensionAttributeRecId), _newDimensionValue, false, true);
                dimStorage.addItem(dimensionAttributeValue);
            }
            else
            {
                dimStorage.removeDimensionAttribute(_dimensionAttributeRecId);
            }
            newDimensionDefault = dimStorage.save();
        }
        return newDimensionDefault;
    }

Bigger usage example

        // replace TransactionType to the one from parameters
        // first we need to get main account and default dimension from the originl transaction ledger dimension 
        mainAccountRecId = LedgerDimensionFacade::getMainAccountRecIdFromLedgerDimension(_ledgerJournalTransOrig.LedgerDimension);
        dimensionDefault = LedgerDimensionFacade::getDefaultDimensionFromLedgerDimension(_ledgerJournalTransOrig.LedgerDimension);
        // then replace this attribute with a new value
        dimensionDefault = myDimValueHelper::setDefaultDimensionValue(dimensionDefault, myDimensionConstants::TransactionType, LedgerParameters::find().myGLDimInvTransSale);
        // get default dimension for main account
        ledgerDimensionMainAccount = LedgerDefaultAccountHelper::getDefaultAccountFromMainAccountRecId(mainAccountRecId);
        // finally combine it with original main account to get a new ledger dimension
        ledgerJournalTrans.OffsetLedgerDimension  = LedgerDimensionFacade::serviceCreateLedgerDimension(ledgerDimensionMainAccount, dimensionDefault);
        ledgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans, OffsetLedgerDimension));

 https://alexvoy.blogspot.com/2015/10/how-to-lookup-and-set-new-value-for.html

Thanks Sasha Nazarov and Ievgen Miroshnikov and Denis Trunin

Friday, August 4, 2023

How to select\unselect all records in a form grid

 While adding standard command button you can opt for SelectAll to mark all records in a form grid. However, there is no such a command for the opposite - unselect all records.


You can easily achieve it by using the following method and two usual button form controls.

[Form]
public class myForm extends FormRun
{
    public void selectAll(boolean _select)
    {
        VendPaymFormat_DS.markAllLoadedRecords(_select);
    }

    [Control("Button")]
    class FormButtonControlSelectAll
    {
        public void clicked()
        {
            element.selectAll(true);
            super();
        }
    }

    [Control("Button")]
    class FormButtonControlUnSelectAll
    {
        public void clicked()
        {
            element.selectAll(false);
            super();
        }
    }
}






How to get rid of a stuck report design in SSRS

It is difficult to say if your recent changes to an SSRS report design are really deployed. I suggest that any textbox be colored to visualize it (you can revert it in the end).

However, sometimes previous design is stuck on the server, and you still see no changes deployed, nevertheless, you already restarted Reporting services. Fortunately, there is a direct way to delete such a stubborn report from the server.

Open Report Server configuration manager as administrator and apply Portal URL setting if it is not done yet.



Then go for your report and delete it.

Next deployment should be OK.


Friday, July 28, 2023

XDS in action: How to restraint access to data based on the current user employee

Extensible Data Security (XDS) policies allow restraint access to D365FO data in a very flexible way.

Business case: a user can see those purchase orders and their related confirmations only if he or she is Requester. The whole project can basically contain three objects: role, query, and policy.



After creating a specific security role, we need to create a query with HCMWorker table so that it is filtered for the current user.





All constrained tables should be added to the policy by referencing a particular relation (PurchTable, for example, has two relations with HCMWorker table; thus, we need to pick up the required one related to Requester field)







Note: we can create a join expression if a required relation does not exist for a given table.

Once the project built and synchronized, we can assign this new role along with some standard ones to a user. For example sake, I added Ada to my user.





Monday, June 12, 2023

Complete pack/unpack pack for RunBaseBatch adventure

Usage

We can use pack-unpack methods in RunBaseBatch classes and all its descendants to save and/or store the state of an object, and then later re-instantiate the same object.

We need it, for example, to use the user's input for any batch job: some parameters can be used when a batch job is executed on the server. 

Documentation

Microsoft doc article is here.

Quote: "A reinstantiated object is not the same object as the one that was saved. It is just an object of the same class whose members contain the same information." 

You need to teleport your objects without a fly or other bugs.

Case examples

Basics

Basic example of such a class is Tutorial_RunbaseBatch.

    TransDate       transDate;
    CustAccount     custAccount;

    #define.CurrentVersion(1)
    #localmacro.CurrentList
        transDate,
        custAccount
    #endmacro
    public container pack()
    {
        return [#CurrentVersion,#CurrentList];
    }

    public boolean unpack(container packedClass)
    {
        Version version = RunBase::getVersion(packedClass);
        ;
        switch (version)
        {
            case #CurrentVersion:
                [version,#CurrentList] = packedClass;
                break;
            default:
                return false;
        }

        return true;
    }

Containers

Basically you can add a container directly to pack() or convert a container to a string and save the latter. Opposite conversion required for restoring. 

container                   siteIds;
str                         siteIdsStr;

#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
  siteIdsStr
#endmacro

public boolean getFromDialog()
{
  ...
  // convert it to string for pack/unpack
  siteIdsStr  = con2Str(siteIds);
  
  return super();
}

public void dialogPostRun(DialogRunbase _dialog)
{
    FormRun formRun;

    super(dialog);

    formRun = _dialog.dialogForm().formRun();

    if (formRun)
    { 
        // if we restored from last values
        if(siteIdsStr)
        {
            //then we convert the string to container
            siteIds = str2con(siteIdsStr);
        }
    }
}

See the following article for the complete usage https://alexvoy.blogspot.com/2014/11/dialog-field-with-multiple-choice.html

Tables

Save an unique key value, say, RecId to find a target buffer after unpack()

Collection types

Maps, lists, and sets are equipped with method pack, which you can use directly for packing.

        // collections
        List        list;
        Map         map;
        Set         set;
        // saved states
        container   packedCont;

        packedCont = list.pack();
        list = List::create(packedCont);

        packedCont = map.pack();
        map = Map::create(packedCont);

        packedCont = set.pack();
        set = Set::create(packedCont);

Queries

How to pack/unpack a query among other parameters, you can find in IntrastatTransfer class.

    QueryRun        queryRunIntrastatTransfer;

    container pack()
    {
        return [#CurrentVersion,#CurrentList,queryRunIntrastatTransfer.pack()];
    }

    boolean unpack(container packedClass)
    {
        Integer      version      = conPeek(packedClass,1);
        container    packedQuery;

        switch (version)
        {
            case #CurrentVersion:
                [version,#CurrentList,packedQuery]      = packedClass;
                if (packedQuery)
                {
                    queryRunIntrastatTransfer = new QueryRun(packedQuery);
                }
                break;

            default:
                return false;
        }
        return true;
    }

Subclasses

How to pack/unpack additional parameters in a subclass.

class myVendCreateGlobalPaymJournal extends CustVendCreatePaymJournal_Vend

    NoYes       myParm;

    #define.CurrentVersion(1)
    #localmacro.CurrentList
        myParm
    #endmacro
    /// <summary>
    /// Pack the new parameters
    /// </summary>
    /// <returns>standard list of parameters with the new ones</returns>
    public container pack()
    {
        return [#CurrentVersion,#CurrentList] + [super()];
    }

    /// <summary>
    /// Unpacks saved parameters
    /// </summary>
    /// <param name = "_packedClass">Parameters container</param>
    /// <returns>True if OK</returns>
    public boolean unpack(container  _packedClass)
    {
        Integer  version = conPeek(_packedClass,1);
        container packedBase;

        switch (version)
        {
            case #CurrentVersion:
                [version, #CurrentList, packedBase] = _packedClass;
                return super(packedBase);
        }
        
        return super(_packedClass);
    }

Extensions

How to pack/unpack additional parameters in an augmented class (extension).

/// <summary>
/// We are going to use a new additional parameter
/// </summary>
[ExtensionOf(classStr(<ClassName>))]
public final class My<ClassName>_Extension
{
    private boolean     myNewParm;   
    #define.CurrentVersion(1)
    #localmacro.CurrentList
        myNewParm
    #endmacro

    
    /// <summary>
    /// myNewParm access
    /// </summary>
    /// <param name = "_parm">boolean</param>
    /// <returns>boolean</returns>
    public boolean parmMyNewParm(boolean _parm = myNewParm)
    {
        myNewParm= _parm;
        return myNewParm;
    }

    /// <summary>
    /// Extends Pack
    /// </summary>
    /// <returns>container</returns>
    public container pack()
    {
        container packedClass = next pack();
        return SysPackExtensions::appendExtension(packedClass, classStr(My<ClassName>_Extension), this.myPack());
    }

    /// <summary>
    /// Extends Unpack
    /// </summary>
    /// <param name = "packedClass">container</param>
    /// <returns>boolean</returns>
    private boolean myUnpack(container packedClass)
    {
        Integer version = RunBase::getVersion(packedClass);
        switch (version)
        {
            case #CurrentVersion:
                [version, #currentList] = packedClass;
                break;
            default:
                return false;
        }
        return true;
    }

    /// <summary>
    /// Packs my locals
    /// </summary>
    /// <returns>container</returns>
    private container myPack()
    {
        return [#CurrentVersion, #CurrentList];
    }

    /// <summary>
    /// Extends unpack
    /// </summary>
    /// <param name = "_packedClass">container</param>
    /// <returns>boolean</returns>
    public boolean unpack(container _packedClass)
    {
        boolean result = next unpack(_packedClass);

        if (result)
        {
            container myState = SysPackExtensions::findExtension(_packedClass, classStr(My<ClassName>_Extension));
            //Also unpack the extension
            if (!this.myUnpack(myState))
            {
                result = false;
            }
        }

        return result;
    }

}

Originally from https://alexvoy.blogspot.com/2022/02/additional-parameters-in-runbasebatch.html

Please, ping me if I missed anything.

Monday, May 8, 2023

How to create a new folder on Sharepoint

There are some useful code behind OfficeSharePointFolderSelectionDiaolog form; however, there is a limitation.


Well, we can create a new folder via REST API call as follows. Take note that the access token is created in testSharePointConnection

using Microsoft.Dynamics.Platform.Integration.SharePoint;
using System.Net;
using System.Net.Http;

[Form]
public class wzhSPFolderForm extends FormRun
{
    str                 apiUrl;
    System.Exception    ex;
    ISharePointProxy    proxy;


    public void init()
    {
        super();
        fNewFolder.text('_wzhNewFolder');
        fServer.text('https://wzhServer.sharepoint.com');
        fRootFolder.text('Shared Documents');
    }

    public boolean testSharePointConnection()
    {
        str src = fServer.text();
        boolean validConnection = false;

        if(src)
        {
            System.UriBuilder   builder     = new System.UriBuilder(src);
            str                 hostName    = builder.Host;
            str                 siteName    = fRootFolder.text();
            str                 externalId  = xUserInfo::getExternalId();
            
            proxy   = SharePointHelper::CreateProxy(hostName, '/', externalId);
            if(proxy)
            {
                if(SharePointHelper::VerifyAuthentication(proxy))
                {
                    validConnection = true;
                    info(strfmt('@ApplicationFoundation:SPServerCommunicationSuccess', hostName));
                }
                else
                {
                    info(strfmt('@ApplicationFoundation:SPServerUserNotAuthorized', hostName));
                }
            }
            else
            {
                info(strfmt('@ApplicationFoundation:SPSelectionDlg_ErrorNoProxy', hostName));
            }
        }

        return validConnection;
    }

    public str GetAuthorizationToken()
    {
        return proxy.AccessToken;
    }

    public void createSPFolder()
    {
        apiUrl = strFmt("%1/_api/web/lists/getbytitle('%2')/rootfolder/folders/add('%3')", fServer.text(), fRootFolder.text(), fNewFolder.text());
        try
        {
            if(this.testSharePointConnection())
            {
                str token = this.GetAuthorizationToken();
            
                System.Net.WebHeaderCollection  httpHeader  = new System.Net.WebHeaderCollection();
                httpHeader.Add("Authorization", 'Bearer ' + token);

                HttpWebRequest                  request     = System.Net.WebRequest::Create(apiUrl);
                request.set_Headers(httpHeader);
                request.set_ContentLength(0);
                request.set_Method("POST");
                request.set_ContentType("application/json;odata=verbose");

                System.Net.HttpWebResponse  response        = request.GetResponse();
                int                         statusCode      = response.get_StatusCode();

                info(strFmt("HTTP status code: %1", statusCode));
            }
        }
        catch
        {
            //exception
            ex = CLRInterop::getLastException().GetBaseException();
            error(ex.get_Message());
        }
    }

    [Control("Button")]
    class bCreateREST
    {
        /// <summary>
        ///
        /// </summary>
        public void clicked()
        {
            element.createSPFolder();
            super();
        }

    }

    [Control("Button")]
    class bTestConnection
    {
        /// <summary>
        ///
        /// </summary>
        public void clicked()
        {
            element.testSharePointConnection();
            super();
        }

    }

}

Thursday, April 20, 2023

X++ to run XIRR

I created a small X++ wrapper to call XIRR function from the C# project provided by https://github.com/klearlending/XIRR (Thank you!) 

You can test XIRR function in Excel, btw.

using tmxExcelFinance;
/// <summary>
/// Implements various financial functions
/// </summary>
public final class myFinanceFunction
{
    /// <summary>
    /// Calculates XIRR value
    /// </summary>
    /// <param name = "_cashFlows">List of containers kind [cashflow amount, its date]</param>
    /// <param name = "_decimals">how many decimals to calculate</param>
    /// <param name = "_maxRate">Maximum rate</param>
    /// <returns>XIRR value</returns>
    public static real calculateXIRR(List _cashFlows, int _decimals, real _maxRate = 1000000)
    { 
         
        var                                 cashFlows       = new System.Collections.Generic.List<CashFlowDates>();
        System.Collections.IEnumerable      cashFlowsI;
        ListEnumerator                      leC             = _cashFlows.getEnumerator();
        System.Double                       dbl;
        System.DateTime                     dt;

        while(leC.moveNext())
        {
            [dbl, dt] = leC.current();
            cashFlows.Add(new CashFlowDates(dbl, dt));
        }
        // convert to iterable
        cashFlowsI = cashFlows;

        return XIRR::Calc(cashFlowsI, _decimals, _maxRate);
    }

}

This is how we can call the wrapper

 public static void calculateXIRRFromInvestmentDetailsLineTmp(RefRecId _projTableRecId, myInvestmentDetailsLineTmp _myInvestmentDetailsLineTmp)
    {
        List listCashFlowsDates = new List(Types::Container);

        myInvestmentDetailsLineTmp myInvestmentDetailsLineTmpLocal;

        myInvestmentDetailsLineTmpLocal.linkPhysicalTableInstance(_myInvestmentDetailsLineTmp);

        while select myInvestmentDetailsLineTmpLocal
        {
            listCashFlowsDates.addEnd([myInvestmentDetailsLineTmpLocal.TransactionCurrencyAmount, DateTimeUtil::newDateTime(myInvestmentDetailsLineTmpLocal.LineDate, 0)]);
        }
        myProjXIRRTable.XIRRValue = myFinanceFunction::calculateXIRR(listCashFlowsDates, 4);
...

Or

 cashFlows1 = new List (Types::Container);
        
        cashFlows1.addEnd([-10000,str2Date("01/01/2008", 123)]);
        cashFlows1.addEnd([2750, str2Date("01/03/2008", 123)]);
        cashFlows1.addEnd([4250, str2Date("30/10/2008", 123)]);
        cashFlows1.addEnd([3250, str2Date("15/02/2009", 123)]);
        cashFlows1.addEnd([2750, str2Date("01/04/2009", 123)]);

res = myFinanceFunction::calculateXIRR(cashFlows1, 10);

Notes about the solution creation.



D365FO project must be <TargetFrameworkVersion>v4.6</TargetFrameworkVersion>


Last remark. I tried to implemented the same algorithm in X++, but I bumped into two limitations.

First, there is a limit for recursion depth - 400 levels; we can flatten it, though. 

Second, real loses required decimals and ends up with division by zero.

Sunday, April 9, 2023

Two outer joined tables in a report without duplicates from both sides

 I cannot find a particular term for this type of join; so I called it 'hanging left outer join'.


Let's state the problem.


We have two tables:

- LedgerJournalTrans (trans) with a field mgcProjId, which is a reference to ProjTable.

- myDetails (details), which may have 0..N records for certain records from ProjTable.


Examples of data in both may look like the following:



If we apply standard outer join, the output will be as follows


But we need to print all details records by 'hanging' them against the same projId, like this


So, no duplicates from both tables must be present in the merged table myDetailsTmp.


This is how you can achieve it. 

/// <summary>
    /// Process report data.
    /// </summary>
    public void processReport()
    {
        Query                   query;
        QueryRun                qr;
        myDetails               details;
        LedgerJournalTrans      trans;
        ProjId                  prevProjId;
        // merge trans and detail even if details are empty
        void insertWithTrans()
        {
            myDetailsTmp.clear();
myDetailsTmp.RefRecId = trans.RecId;
myDetailsTmp.JournalNum = trans.JournalNum;
myDetailsTmp.TransDate = trans.TransDate;
myDetailsTmp.Voucher = trans.Voucher;
myDetailsTmp.ProjId = trans.mgcProjId;
myDetailsTmp.DetailId = details.DetailId;
myDetailsTmp.WithPrice = details.WithPrice;
myDetailsTmp.insert();
} // merge empty trans and detail; it will be 'a hanging' detail void insertDetails() { myDetailsTmp.clear();
myDetailsTmp.ProjId = details.ProjId;
myDetailsTmp.DetailId = details.DetailId;
myDetailsTmp.WithPrice = details.WithPrice;
myDetailsTmp.insert();
} // process warrants for the previous projId // 'hanging' details on the right side of the report void processRestOfDetails() { // if there are still some warrants we found for the previous projId if (details) { // as we do not have enough rows from trans with the same previous projId, // let's add them with empty part from trans side next details; while(details) { insertWarrants(); next details; } } } // Get the query from the runtime using a dynamic query. query = this.parmQuery(); qr = new QueryRun(query); // we suppose that trans sorted by projId // each projId may have 0..N of warrants (details) while(qr.next()) { // get next transaction trans = qr.get(tablenum(LedgerJournalTrans)); // projId changes if(prevProjId != trans.mgcProjId) { // if there are still some warrants we found for the previous projId processRestOfDetails(); // get the first warrant for the new projId select details where details.ProjId == trans.mgcProjId; // keep the new as a previous for the next iteration prevProjId = trans.mgcProjId; } // if this is a trans with the same projId, then we can merge it with the next warrant else if (details) { next details; } // merge trans with warrants insertWithTrans(); } // if there are still some warrants we found for the previous projId processRestOfDetails(); }

Raw outer joined data




Ascending ProjId



Descending ProjId





Friday, March 10, 2023

How to export/import Tax registration numbers for Vendors

 D365FO contains a special data entity which can be used for both DMF export/import and also in Excel Add-in, but... 

There is an interesting InnerJoin inside of this data entity, which may be a hurdle for understanding why you still have no records exported; nevertheless, you have some in TaxRegistration table.



In fact, when you create records in Vendor Registration IDs form, it does not tell you about this constraint.



To get these records exported, you simply need to add it in Registration categories.



and voila!



Monday, March 6, 2023

How to open multiple Purchase orders in new browser tabs

static public void initFromPurchTable(FormDataSource _formDS)
{
	PurchTable      currentPurchTable;
	Browser         browser = new Browser();
	for (currentPurchTable = _formDS.getFirst(true) ? _formDS.getFirst(true): _formDS.cursor();
	currentPurchTable;
	currentPurchTable= _formDS.getnext())
	{
		var generator     = new Microsoft.Dynamics.AX.Framework.Utilities.UrlHelper.UrlGenerator();
		var currentHost   = new System.Uri(UrlUtility::getUrl());
		generator.HostUrl = currentHost.GetLeftPart(System.UriPartial::Authority);
		generator.Company = curext();
		generator.MenuItemName = 'PurchTableListPage';
		generator.Partition = getCurrentPartition();
		// repeat this segment for each datasource to filter
		var requestQueryParameterCollection = generator.RequestQueryParameterCollection;
		requestQueryParameterCollection.AddRequestQueryParameter(
																'PurchTable',
																'PurchId', currentPurchTable.PurchId
																);
		System.Uri fullURI = generator.GenerateFullUrl();
		browser.navigate(fullURI.AbsoluteUri, true);
	}
}