Fixing Unbalanced TTS Error in D365FO Excel Upload

Rohith_Veerapalli — Sat, 27 Dec 2025 17:14:16 +0000

When developing Excel upload in custom script functionality in Dynamics 365 Finance & Operations (D365FO) using X++, we commonly encounter the unbalanced X++ TTSBEGIN/TTSCOMMIT error. This guide shows you exactly how to fix it.

The Error You'll Encounter

Understanding How Custom Scripts Process Transactions

Lets understand how custom script is processing, when we see the class AppConsistencyCustomScriptStateChange,
when we do Run test the operation runs under ttsbegin and ttsabort, when we do Run operation, it process under ttsbegin, ttscommit.
the process is already in transaction when we upload the package

Why This Error Occurs

When you read an Excel file using classes like OfficeOpenXml.ExcelPackage or similar COM objects, these operations internally manage transactions. If your code already has an active TTS level, you'll get an unbalanced transaction error.

The Solution: Abort Transactions Before Reading Excel

The fix is simple but crucial: abort all existing transactions before reading the Excel file, then restore the TTS level afterward.

while (appl.ttsLevel() > 0)
{
    ttsAbort;
}

Since we are aborting the transaction, we need to add ttsBegin transaction begin in the end of the process to not get Unbalanced transaction errors

Complete Working Code Example

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;

class ExcelReaderForCustomScript 
{

    public  CustAccount      accountNum;
    public CCMNum_BR         ccmNum ;
    /// <summary>
    /// Class entry point. The system will call this method when a designated menu
    /// is selected or when execution starts and this class is set as the startup class.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        ExcelReaderForCustomScript curentClass = new ExcelReaderForCustomScript();
        curentClass.importData();

        Info('script executed');
    }

    void importData()
    {
        System.IO.Stream                        stream;
        DialogGroup                             dlgUploadGroup;
        FileUploadBuild                         fileUploadBuild;
        FormBuildControl                        formBuildControl;
        list List = new List(types::String);

        while (appl.ttsLevel() > 0)
        {
            ttsAbort;
        }

        Dialog           dialog =    new Dialog("Upload file");

        dlgUploadGroup          = dialog.addGroup("File");
        formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild         = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted('.xlsx');

        if (dialog.run() && dialog.closedOk())
        {
            FileUpload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId('Upload'));
            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
            CustTable   custTable;
            if(fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (ExcelPackage Package = new ExcelPackage(stream))
                {
                    int rowCount, i,columncount;

                    Package.Load(stream);
                    ExcelWorksheet worksheet           = package.get_Workbook().get_Worksheets().get_Item(1);
                    OfficeOpenXml.ExcelRange range     = worksheet.Cells;

                    rowCount           = (worksheet.Dimension.End.Row) - (worksheet.Dimension.Start.Row)  + 1;
                    columncount        = (worksheet.Dimension.End.Column);

                    for (i = 2; i<= rowCount; i++)
                    {
                        accountNum = (range.get_Item(i,1).Value);
                        ccmNum   = (range.get_Item(i,2).Value);
                        List.addEnd(accountNum);

                        ttsbegin;
                        update_recordset custTable setting CCMNum_BR = ccmNum
                            where custTable.AccountNum == accountNum;
                        ttscommit;

                    }
                    info(strFmt('AccountNum %1',list));
                    **ttsbegin;**
                }
            }
        }
    }

}

Critical Implementation Note

Keep in mind, since we are aborting transaction, Run Test process will write the data to system. we dont have to process Run operation again.

Computed columns in D365FO – Using Correlated Subqueries with Real SQL

Rohith_Veerapalli — Thu, 18 Dec 2025 16:30:25 +0000

When working in Dynamics 365 Finance & Operations, we often need to display or filter data that doesn’t physically exist in a table. Adding a new field and populating it with batch jobs or event handlers is sometimes overkill — and that’s where computed columns shine.

In this post, I’ll explain computed columns in D365FO, when to use them, and a real-world SQL subquery pattern .

Computed Columns in D365FO

Computed Column in D365FO x++?

A computed column is a virtual field whose value is calculated at runtime using SQL, not stored in the database.

Key points:

No physical column is created in SQL

Logic is executed at query time

Implemented using an X++ static method that returns an SQL expression

Commonly used in views, data entities, and computed fields

The Pattern Most People Use (and Its Limitation)

Most examples online show simple patterns like:

Concatenating fields
Simple CASE WHEN
Basic SUM or COUNT

But in real projects, we often need context-aware values, based on:

Date ranges
Related transactions
Conditional joins

That’s where subqueries inside computed columns become powerful.

A Real-World Example: Fetching a Request ID Based on Transaction Date

Let’s look at a computed column that returns a Leave Request ID for a worker, based on whether a transaction date falls within a leave period.

public static str requestId()
{
    str sqlQuery;

    sqlQuery = strFmt(
        "(SELECT TOP 1 LR.REQUESTID " +
        " FROM LeaveRequest LR " +
        " JOIN LeaveOfAbsenceRequestDetail LRD " +
        "   ON LRD.REQUEST = LR.RECID " +
        "  AND LRD.DATAAREAID = LR.DATAAREAID " +
        " WHERE LR.WORKER = T1.WORKER " +
        "   AND LRD.STARTDATE <= T1.TRANSACTIONDATE " +
        "   AND LRD.ENDDATE   >= T1.TRANSACTIONDATE " +
        "   AND LR.DATAAREAID = T1.DATAAREAID " +
        "   AND LR.PARTITION  = T1.PARTITION)"
    );

    return sqlQuery;
}

Understanding What’s Happening

1. Why T1?

T1 refers to the primary datasource of the view or entity where this computed column is defined.

For example:

T1.WORKER

T1.TRANSACTIONDATE

These values come from the current record being queried.

2. Why SELECT TOP 1?

Computed columns must return a single scalar value.
If multiple records are possible, SQL must be constrained to one result.

You can also:

Add ORDER BY
Add additional filters
Use MAX() or MIN() if appropriate

3. Why This Is Powerful

This pattern allows you to:

Dynamically resolve relationships
Use date-effective logic
Avoid storing derived data

Use the field in:

Filters
Sorting
Data entities
Reports

All without writing X++ loops.

More examples

Lets say we have multiple approvers for single invoice, we can concatenate multiple pending approvers like this

public static str pendingApprovers()
{
    str sqlQuery;


    sqlQuery = strFmt( 
                     "STUFF((" +
                    " SELECT ', ' + USERINFO.Name" +
" FROM WORKFLOWWORKITEMTABLE WWI" +
" JOIN DIRPERSONUSER PU ON PU.USER_ = WWI.USERID" +
" JOIN HCMWORKER HW ON HW.PERSON = PU.PERSONPARTY" +
" join USERINFO ON USERINFO.ID = WWI.USERID"+
" WHERE WWI.PURCHINVOICE = T1.RECID" +
" AND WWI.STATUS  = 0"+
" AND WWI.PARTITION = T1.PARTITION" +
" FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')" +
", 1, 2, '')"
        );

        return sqlQuery;


}

Here T1 is the primary data source table

This is how the view design will be

and final result

Keep experimenting 🚀

DEV Community: Rohith_Veerapalli