Advanced query syntax in Dynamics 365 Finance: an example with joined tables

Microsoft documents the syntax for advanced queries in Dynamics 365 Finance here: Advanced filtering and query syntax – Microsoft Learn. The documentation contains an example of a query that uses fields from two data sources:

((AccountNum LIKE "US*") && (DirPartyTable.Name LIKE "Cont*"))

This is useful, but it does not really help you construct more complex queries when the required fields are several joins away from the primary data source. This was also the subject of a question in the Dynamics 365 Community: Seeking “Finance and Operations query syntax” examples from “Advanced filter or sort”.

In reality, the Microsoft’s example is even incorrect. When the query is entered in the Advanced filter or sort dialog, the system may not use the table name exactly as shown in the documentation. In the example above, the system-generated data source name can require a “_1” suffix, for example: DirPartyTable_1.Name rather than DirPartyTable.Name.

Let’s have a look at a more complex example, where customers should be filtered by the customer group, unless they are from specific “countries” (French overseas territories, to be exact). The customer group is available directly from the “CustTable”, but the country/region is stored in the address. The required tables are therefore: Customers → Parties → Addresses

The required joins can be created directly in the Dynamics’ Query: on the Joins tab, select + Add table join and enable Show details. Scroll down to n:1 Global address book and select it. Then add another nested join to the Global address book and select n:1 Addresses.

The resulting structure is:

CustTable

└── n:1 Global address book

└── n:1 Addresses

It is important to use Show details and be able to select the n:1 relationships: there are also 1:n relationships in the list, but they return an empty dataset.

The range can then reference fields from both CustTable and LogisticsPostalAddress:

((((LogisticsPostalAddress_1.CountryRegionId = "REU") || (LogisticsPostalAddress_1.CountryRegionId = "GLP")|| (LogisticsPostalAddress_1.CountryRegionId = "MTQ")) && (CustTable_1.CustGroup LIKE "C-EX-NOEU*")) || (CustTable_1.CustGroup LIKE "*DO*"))

This means:
((Country = REU, GLP or MTQ) AND Customer group = C-EX-NOEU*) OR Customer group contains DO

The same approach can be used for considerably more complex filters: fields from the primary table can be combined with fields from joined tables, and the &&, || and LIKE operators can be used to build the required expression.

Important notice: if you used a field in the body of an advanced query, do not use the same field in another range line of the Advanced filter or sort dialog. Unlike regular queries, these conditions will result in an AND clause, not in an OR clause as usual. In other words, if you added a line with an additional condition Customer group = “EX*”, then the query is not going to return any records at all, because a customer may not have a group like “*DO*” and “EX*” at the same time, unless there is an “EX-DO*” group.

Dynamics 365 tips and tricks

Automate ICO purchase invoice in Dynamics 365 Finance

On the current occasion that spoiled my Saturday: one legal entity posts an intercompany sales invoice, but the receiving entity does not automatically react. As a result, stock and intercompany balances start to diverge, because the sales side is already fully posted while the corresponding vendor invoice on the purchasing side is either missing or only created after manual intervention. In day-to-day operations, this leads to inconsistencies and unnecessary reconciliation effort. In the ICO direct delivery – “a chain of three” SO <- ICO PO <- ICO SO – this can be fully automated out of the box, but in the “chain of two” ICO SO -> ICO PO it cannot.

The standard process requires a user to actively navigate to the purchase order in the receiving company, click Intercompany tracing / Invoice journal (it switches the legal entities in the process), and manually trigger the “Generate intercompany invoice” function. Only after this step does the vendor invoice appear. The expectation is straightforward: once the sales invoice is posted, the corresponding intercompany vendor invoice should automatically be created in the receiving entity and appear there as a pending invoice, ready for review and approval. The system already contains all the necessary logic, but it simply does not execute it at the right moment.

The solution may be a surprisingly small extension at the right place. By hooking into the invoice posting process and calling the existing intercompany update logic immediately after posting, the missing step is automated:

				
					[ExtensionOf(classStr(SalesFormLetter_Invoice))]
internal final class SalesFormLetter_Invoice_Extension
{
    public void run()
    {
        next run();

        FormletterOutputContract outputContract = this.getOutputContract();

        if (outputContract)
        {
            CustInvoiceJour custInvoiceJour = outputContract.parmJournal() as CustInvoiceJour;
            if (custInvoiceJour && custInvoiceJour.InterCompanyCompanyId)
            {
                setPrefix("@SCM:IntercompanyGenerateInvoiceMenuCaption");
                custInvoiceJour.interCompanyUpdate();
            }
        }
    }
}

The key method is deliberately NOT wrapped into the same transaction as the main run(): a workflow or a closed period in the company of the recipient should not disrupt the seller’s operations.

Intercompany blog series

Deferred revenue in foreign currency: closing the gap in D365

The gap

In Microsoft Dynamics 365 for Finance, Deferred revenue in Subscription Billing works reliably as long as you stay within a single currency context. Once foreign currencies are involved, a design flaw starts to show its effects: the system recognises deferred revenue exclusively in accounting currency. The original transaction currency is disregarded, and the exchange rate used at invoice posting is not retained in the deferral logic.

This is consistent behaviour Revenue <-> COGS, but it creates a persistent issue: accounts stop balancing in currency, and this is even carried forward from year to year, at every FY closing.

At invoice posting, the exchange rate is effectively fixed, and from there, the deferral engine operates purely in accounting currency. This becomes visible when the deferred balance is fully recognised. In accounting currency, the balance reaches zero as expected. In transaction currency, however, it does not. Residual amounts remain not because of revaluation or fluctuating exchange rates, but because the postings were never aligned in the first place.

From a reconciliation point of view, this is difficult to justify. An account that is technically cleared still shows values in another currency view.

It is worth noting that this behaviour has been raised repeatedly: many customers and partners complained, the “Idea” Deferral schedule recognition to include transaction currency is at the top of complaints about the Subscription billing module.

Customisation: store the historical exchange rate, fix the recognition journal

Rather than working around the issue, the approach taken here was more straightforward: the Subscription Billing deferral logic was adjusted so that revenue recognition is posted in the original transaction currency, using the exchange rate from the invoice; in other words, the system was made to behave as one would expect.

Technically, this requires intervening in the deferral posting process. First, we add 3 fields – currency, amount, and rate to the Deferrals schedule table, and populate them:

				
					[ExtensionOf(classStr(SubBillDeferralScheduleCreate))]
final class SubBillDeferralScheduleCreate_Extension
{
    public static void initDeferralScheduleTable(
        SubBillDeferralAmountSource _subBillDeferralAmountSource,
        SubBillDeferralTransactionLineDeferral _deferralTable,
        SubBillDeferralScheduleTable _schedTable,
        boolean _usingShortTerm)
    {
        next initDeferralScheduleTable(_subBillDeferralAmountSource, _deferralTable, _schedTable, _usingShortTerm);
        if (_subBillDeferralAmountSource == SubBillDeferralAmountSource::DeferralAmount)
        {
            _schedTable.SubBillDeferralAmountCur = _deferralTable.SubBillDeferralAmountCur;
            _schedTable.ExchRate = _deferralTable.ExchRate;
            _schedTable.CurrencyCode = _deferralTable.CurrencyCode;
        }
    }
}

The form Subscription billing > Revenue and expense deferrals > Deferral schedules > All deferral schedules has been extended to reflect the new fields, too:

Next, we make sure that the 3 values are carried through correctly as the invoice is posted and the final schedule lines are re-initialised from the posted invoice line:

				
					[ExtensionOf(tableStr(SubBillDeferralTransactionLineDeferral))]
final class SubBillDeferralTransactionLineDeferral_Extension
{
    public void updateFromTransaction()
    {
        next updateFromTransaction();

        if (! this.TransRecId)
        {
            return;
        }
        switch (this.SubBillDeferralSourceRecType)
        {
            case SubBillDeferralSourceRecType::SalesLine:
                if (this.SubBillDeferralTransactionType != SubBillDeferralTransactionType::SalesOrder)
                {
                    // Not supported
                    return;
                }
                SalesLine salesLine = SalesLine::findRecId(this.TransRecId);
                this.SubBillDeferralAmountCur = salesLine.calcGrossAmountExclTax(this.Qty);
                SalesFixedExchRate fixedExchRate = salesLine.salesTable().fixedExchRate();
                if (fixedExchRate != 0.0)
                {
                    this.ExchRate = fixedExchRate;
                }
                else
                {
                    ExchangeRateHelper helper = ExchangeRateHelper::construct();
                    helper.parmFromCurrency(salesLine.CurrencyCode);
                    helper.parmExchangeDate(this.TransDate);
                    helper.parmToCurrency(Ledger::accountingCurrency());
                    this.ExchRate = helper.getExchangeRate1();
                }
                this.CurrencyCode = salesLine.CurrencyCode;
                break;

            case SubBillDeferralSourceRecType::CustInvoiceTrans:
                CustInvoiceTrans custInvoiceTrans = CustInvoiceTrans::findRecId(this.TransRecId);
                this.SubBillDeferralAmountCur = custInvoiceTrans.LineAmount + custInvoiceTrans.SumLineDisc;
                this.ExchRate = custInvoiceTrans.exchRate();
                this.CurrencyCode = custInvoiceTrans.CurrencyCode;
                break;

            default:
                return;
        }

        this.SubBillDeferralAmountCur = abs(this.SubBillDeferralAmountCur);
    }
}

At last, these stored rates are used in the monthly revenue recognition (do not Summarise the journal lines!). This is the hardest part, because the class SubBillDeferralLedgerJournalCreate is nearly impossible to extend in the D365 FO standard, as the developers either “privatised” or “internalised” all methods for no obvious reason. All.. but one. I’ll leave this adventurous Chinese hack uncommented:

				
					[ExtensionOf(tableStr(LedgerParameters))]
final class LedgerParameters_Extension
{
    public static boolean isChineseVoucher_CN()
    {
        boolean ret = next isChineseVoucher_CN();
        if (ret)
        {
            return ret;
        }

        str txt = con2Str(xSession::xppCallStack());
        if (strscan(txt, "SubBillDeferralLedgerJournalCreate", 1, 1000) > 0  &&
            strscan(txt, "assignChineseVoucherFromDefaultType_CN", 1, 1000) == 0)
        {
            ret = true;
        }
        return ret;
    }

}

The hack then ultimately triggers the actual update of the general journal line with the proper amount and rate:

				
					[ExtensionOf(classStr(SubBillDeferralLedgerJournalCreate))]
final class SubBillDeferralLedgerJournalCreate_Extension
{
    protected void assignChineseVoucherFromDefaultType_CN(LedgerJournalTrans _ledgerJournalTrans)
    {
        next assignChineseVoucherFromDefaultType_CN(_ledgerJournalTrans);

        SubBillDeferralScheduleNumber   schedNumber = subStr(_ledgerJournalTrans.Txt, 1, strFind(_ledgerJournalTrans.Txt, " ", 1, 20)-1);
        if (! schedNumber)
        {
            return;
        }
        SubBillDeferralScheduleTable deferralScheduleTable;
        deferralScheduleTable = SubBillDeferralScheduleTable::find(schedNumber);
        if (! schedNumber)
        {
            return;
        }
        if (deferralScheduleTable.ExchRate == 0.00 || deferralScheduleTable.ExchRate == 100.00)
        {
            return;
        }

        _ledgerJournalTrans.CurrencyCode = deferralScheduleTable.CurrencyCode;

        if (_ledgerJournalTrans.AmountCurDebit)
        {
            _ledgerJournalTrans.AmountCurDebit = CurrencyExchangeHelper::curAmount(
                _ledgerJournalTrans.AmountCurDebit,
                deferralScheduleTable.CurrencyCode,
                deferralScheduleTable.TransDate,
                UnknownNoYes::Unknown,
                deferralScheduleTable.ExchRate);
        }
        else
        {
            _ledgerJournalTrans.AmountCurCredit = CurrencyExchangeHelper::curAmount(
                _ledgerJournalTrans.AmountCurCredit,
                deferralScheduleTable.CurrencyCode,
                deferralScheduleTable.TransDate,
                UnknownNoYes::Unknown,
                deferralScheduleTable.ExchRate);
        }
        _ledgerJournalTrans.ExchRate = deferralScheduleTable.ExchRate;
    }
}

The result

…is simple: balances clear cleanly not only in accounting currency, but also in transaction currency. There are no residual amounts, no inconsistencies, and no need for explanations during reconciliation. A long-standing and widely discussed gap is effectively closed.

You may download the source code here: SubBillDeferral_with2currencies

Uncategorized

Advanced query syntax in Dynamics 365 Finance: an example with joined tables

Advanced query syntax in Dynamics 365 Finance: an example with joined tables

Dynamics 365 tips and tricks

Automate ICO purchase invoice in Dynamics 365 Finance

Automate ICO purchase invoice in Dynamics 365 Finance

Intercompany blog series

Deferred revenue in foreign currency: closing the gap in D365

Deferred revenue in foreign currency: closing the gap in D365

The gap

Customisation: store the historical exchange rate, fix the recognition journal

The result