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();
        }

    }

}

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 mgcFinanceFunction
{
    /// <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, mgcInvestmentDetailsLineTmp _mgcInvestmentDetailsLineTmp)
    {
        List listCashFlowsDates = new List(Types::Container);

        mgcInvestmentDetailsLineTmp mgcInvestmentDetailsLineTmpLocal;

        mgcInvestmentDetailsLineTmpLocal.linkPhysicalTableInstance(_mgcInvestmentDetailsLineTmp);

        while select mgcInvestmentDetailsLineTmpLocal
        {
            listCashFlowsDates.addEnd([mgcInvestmentDetailsLineTmpLocal.TransactionCurrencyAmount, DateTimeUtil::newDateTime(mgcInvestmentDetailsLineTmpLocal.LineDate, 0)]);
        }
        mgcProjXIRRTable.XIRRValue = mgcFinanceFunction::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 = mgcFinanceFunction::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.

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.

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

This is how you can achieve it. 

/// <summary>
    /// Process report data.
    /// </summary>
    public void processReport()
    {
        Query                   query;
        QueryRun                qr;
        mgcWarrantsDetails      details;
        LedgerJournalTrans      trans;
        ProjId                  prevProjId;
        // merge trans and detail even if details are empty
        void insertWithTrans()
        {
            mgcProjWarrantDetailTmp.clear();
            mgcProjWarrantDetailTmp.RefRecId            = trans.RecId;
            mgcProjWarrantDetailTmp.JournalNum          = trans.JournalNum;
            mgcProjWarrantDetailTmp.TransDate           = trans.TransDate;
            mgcProjWarrantDetailTmp.Voucher             = trans.Voucher;
            mgcProjWarrantDetailTmp.ProjId              = trans.mgcProjId;
            mgcProjWarrantDetailTmp.WarrantDetailId     = details.WarrantDetailId;
            mgcProjWarrantDetailTmp.WarrantsWithPrice   = details.WarrantsWithPrice;

            mgcProjWarrantDetailTmp.insert();
        }
        // merge empty trans and detail; it will be 'a hanging' detail
        void insertWarrants()
        {
            mgcProjWarrantDetailTmp.clear();
            mgcProjWarrantDetailTmp.ProjId              = details.ProjId;
            mgcProjWarrantDetailTmp.WarrantDetailId     = details.WarrantDetailId;
            mgcProjWarrantDetailTmp.WarrantsWithPrice   = details.WarrantsWithPrice;

            mgcProjWarrantDetailTmp.insert();
        }
        // process warrants for the previous projId
        // 'hanging' details on the right side of the report
        void processRestOfWarrants()
        {
            // 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
                processRestOfWarrants();
                // 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
        processRestOfWarrants();
    }

Raw outer joined data

Ascending ProjId

Descending ProjId

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!

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);
	}​
}​

Get Infolog as a string

/// <summary>
/// Gets infolog content
/// </summary>
/// <returns>infolog string</returns>
public static str getErrorStr(container _cont)
{
	SysInfologEnumerator        enumerator;
	SysInfologMessageStruct     msgStruct;
	Exception                   exception;
	str                         error;

	enumerator = SysInfologEnumerator::newData(_cont);

	while (enumerator.moveNext())
	{
		msgStruct = new SysInfologMessageStruct(enumerator.currentMessage());
		exception = enumerator.currentException();

		error = strfmt('%1 %2', error, msgStruct.message());
	}

	return error;
}

Code Review

Disclaimer: this is just a short recap of what Martin Dráb explained in his article https://dev.goshoom.net/2012/08/purpose-of-code-reviews/.

Purpose of code reviews

• Internal quality:

1. Overall approach – does the code do the right thing by proper means? 
2. Maintainability/extensibility – are we able to easily extend the solution, fix bugs etc.? 
3. Testability – are we able to test the code?
4. Documentation – are we able to understand why the code was made and what it does?
5. Performance – is the design correct from performance perspective? 

• Functional testing: Some bugs are much more visible when looking into code
• Knowledge sharing: Discussions between the developer and the reviewer

Tools

• BP check 
• xppbp.exe
• DevOps code review request

Top 10 issues discovered from Dynamics AX Code Review

https://www.linkedin.com/pulse/top-10-issues-discovered-from-dynamics-ax-code-review-caillet/