Input validation and messaging in the Process Guide Framework

Input validation and messaging in the Process Guide Framework

A few words of introduction: Process Guide framework is THE framework for new Warehouse management mobile screens and menu items in “Dynamics 365 Supply Chain Management”:
…/warehousing/process-guide-framework

I find that the topic of the validation of data entered or scanned by the user is very superficially covered. When you read the above shallow documentation you may think the ProcessGuideStep.isComplete() method is a good place to give an error, but it is NOT. It would be too late: by the time the execution reaches isComplete() the user input has already been processed, accepted and serialized in the session state aka “pass” WHSRFPassthrough.

I figured out that the true validation may be programmed at 2 places: in the process() method of a class derived from the WHSControl or in the ProcessGuideStep.validateControls() method. The 2 ways require slightly different techniques and have distinct flavours:

WHSControl.process()

Inheriting the WHSControl class offers an object-oriented alternative to extending the WHSRFControlData class (this spaghetti code has been recently refactored and moved to WHSRFControlData.processLegacyControl). This technique is briefly described in the blog …/mfp/posts/extending-whs-adding-a-new-control-type .
There are pros and cons. The class is going to be used across the whole warehouse mobile app whenever you add a control with this name. This can be both good and bad:

  • The major disadvantage is that the WHSControl.process() is not triggered if the user has not entered anything into the field on the mobile device. E.g. one cannot check for an empty value, and this may lead to the “xxx is not found in the map” error message if one does not check existence of a parameter before fetching it from the “pass” WHSRFPassthrough.
  • The same validation logic is used everywhere. The code re-use is innate. This can be however too rough; for example, the below code snippet was written because the standard logic of the ProdID control was too broad: it was populating the item number and validating if it had a BOM.
  • The control is not fully aware of the context. It cannot reach the current Process Guide step object and call the Process Guide controller object back.
  • It is not fully aware of the surrounding controls either: the controls placed on the page after the current one have left no trace yet.
  • The control class can leverage the WHSRControlData instance with all its perks and shortcuts.

In WHSControl.process() you take the user entry from the parmData() and return a false result with the this.fail() method:

				
					#WHSRF [WhsControlFactory(#ProdIdLabel)]
public class WHSControlProdId extends WhsControl
{
    public boolean process()
    {
        boolean ok = super();
        ProdId prodId = this.parmData();
        fieldValues.insert(ProcessGuideDataTypeNames::ProdId, prodId);
        if (! ProdTable::exist(prodId))
        {
            ok = this.fail("@WAX1162"); // The production or batch order number is not valid
        }
        return ok;
    }
}
				
			

Note that you have to care about parsing the entry and storing it in the fieldValues container. On an error, the processing stops, the page reloads and the error message(s) is shown:
WHS warning

ProcessGuideStep.validateControls()

The validation in the ProcessGuideStep class has a different quality:

  • One can check for empty (mandatory) data.
  • The logic can only be re-used in a menu item made in the Process Guide framework. Such menu items are too few. You re-use the logic by adding the whole step = screen to the process flow.
  • The step has a full access to the process controller and to the preceding step results.
  • All controls are validated at once and can be checked for mutual consistency.
  • The WHSRControlData instance with all its useful methods has already been disposed of by the time execution reaches the validateControls() method. You have to write most of the business logic from scratch.

The validateControls() method is executed before the fieldValues container is merged with the “pass” container (WHSRFPassthrough) and becomes available to the mobile application. This is why you take the user entry safely from the processingResult.fieldValues structure. If you encounter an error, you throw a warning:

				
					[ProcessGuideStepName(classStr(ProcessGuideItemConsumpPromptQtyStep))]
class ProcessGuideItemConsumpPromptQtyStep extends ProcessGuideStep
{
    protected void validateControls()
    {
        WhsrfPassthrough pass = controller.parmSessionState().parmPass();
        super();
        qty = processingResult.fieldValues.lookup(ProcessGuideDataTypeNames::Qty);
        if (qty * pass.parmQty() < 0)
        {
            throw warning("@SYS25506"); // Quantity may not change sign
        }
    }
    protected ProcessGuidePageBuilderName pageBuilderName()
    {
        return classStr(ProcessGuideItemConsumpPromptQtyPageBuilder);
    }
}
				
			

The warning thrown interrupts the flow, re-loads the page and presents the warning.

Success message

A related topic is an ability to present a success message to the user at the end of the process execution. You do it at the end of the doExecute() method in the process guide step:

				
					protected void doExecute()
{
    super();
    // do the work
    this.addProcessCompletionMessage();
}
				
			

This implementation is very limited, however: it cannot uptake a custom message and always shows the same text “Work Completed”.

The below snippet is smarter:

				
					
ProcessGuideMessageData messageData = ProcessGuideMessageData::construct();
messageData.message = strFmt("@SYS24802", journalId); // Journal cannot be posted...
messageData.level = WHSRFColorText::Warning;
navigationParametersFrom = navigationParametersFrom ?
                              navigationParametersFrom :
                              ProcessGuideNavigationParameters::construct();
navigationParametersFrom.messageData = messageData;
				
			

Electronic Reporting (ER) Cookbook 3: Working with dates

Electronic Reporting (ER) Cookbook 3: Working with dates

Following the parts 1 and 2 of my hints and tips collection for Electronic reporting, let me shed some light on working with dates and date formatting.

Cut-off date

If a classic Excel transaction report must be implemented in the Electronic Reporting module, then a cut-off date is a common requirement: show all transactions up to and including a certain date, or show all transactions if the user hasn’t selected any date. Classically, a parameter in the dialog is presented to the user, this requires a User parameter of the DATE type in the format or – better – in the date model mapping (see also Electronic reporting in depth).

This parameter must be used in a Calculated field of the Record List type. Below is a snippet from one of my very first reports dated back to 2016; it extracts ledger transactions from the current company for the German GDPdU dump file:
WHERE(GLaccountEntry,
AND(
NOT(GLaccountEntry.AccountingCurrencyAmount=0),
GLaccountEntry.'>Relations'.GeneralJournalEntry.Ledger = Ledger.'current()',
GLaccountEntry.'>Relations'.GeneralJournalEntry.'>Relations'.FiscalCalendarPeriod.Type = FiscalPeriodType.Operating,
GLaccountEntry.'>Relations'.GeneralJournalEntry.AccountingDate >= FromDate,
OR(GLaccountEntry.'>Relations'.GeneralJournalEntry.AccountingDate <= ToDate, ToDate=NULLDATE())))

In hindsight, this was a terrible implementation. The WHERE operator works with in-memory lists. In essence, it loads all transactions from all companies and all periods and years into an internal XML container, and THEN starts filtering it by company and date. Not surprisingly, the performance degrades rapidly and the report execution time grows exponentially.

The FILTER is a better function as it compiles to a direct SQL statement and returns a reduced dataset; however, it has limited capabilities and does not support cross joins as above.

A mature solution would be to perform the filtering in 2 steps: one Calculated list fetches a maximally pre-filtered list from the SQL database, and subsequent Calculated field variables apply additional dynamic filters to this subset.
The below expression returns a list of all cost project transactions up to a certain date:
FILTER(ProjTransPosting,
AND(ProjTransPosting.PostingType=Enums.LedgerPostingType.ProjCost,
OR(ProjTransPosting.LedgerTransDate <= $ToDate, $ToDate=NULLDATE())))

where $ToDate is the user parameter.

To simplify and reuse the classic pattern WHERE LedgerTransDate <= $ToDate OR $ToDate=NULLDATE(), an interim variable (Calculated field) $ToDateOrInfinity may be declared (I could not find any other way to pass a date literal into an ER expression, that’s why the crude DATEVALUE(“31-12-2154″,”dd-MM-yyyy”)):
IF('$ToDate'>NULLDATE(), '$ToDate', DATEVALUE("31-12-2154","dd-MM-yyyy"))
and the above query reduces to just
FILTER(ProjTransPosting, AND(ProjTransPosting.PostingType=Enums.LedgerPostingType.ProjCost,
ProjTransPosting.LedgerTransDate <= $ToDateOrInfinity))

ToDate user parameterThis led to an issue on the UI, however. User parameters from the [default] Mapping designer propagate to the format, are merged with the user parameters defined in the Format designer and displayed together in the common dialog window. According to Maxim B. from the Microsoft R&D Center in Moscow, a user parameter is shown at the run time if 2 conditions have been met:

  • the visibility is not turned off;
  • in the mapping, the user parameter is bound to the model either directly or indirectly.

Seemingly, the above indirect usage obscures the parameter from the ER format and it disappears from the dialog. The solution was to bind $ToDate directly to an unused field in the model.

Date formatting: DATETIME to DATE

I spent quite some time trying to find an embedded function to convert a DATETIME type and bind to a DATE cell in Excel. Obviously, you can declare the format node a STRING and apply the DATETIMEFORMAT() function, but this circumvents the locale settings.

The solution was dumb, but it worked:
DATEVALUE(DATETIMEFORMAT(@.'Created date', "dd-MM-yyyy"), "dd-MM-yyyy")

First, Last date of the month

The below formulas work in model mappings. Assume the parmPeriodDate a user parameter. The first formula is easy and trivial:
DATEVALUE("01-"&DATEFORMAT(parmPeriodDate, "MM-yyyy"), "dd-MM-yyyy")

The second formula is crazy. It needs a declaration of the system class DateTimeUtil nearby in the model mapping data source section.
DATEVALUE(DATETIMEFORMAT(ADDDAYS(DATETODATETIME(DATEVALUE("01-"&
DATETIMEFORMAT(DateTimeUtil.addMonths(DATETODATETIME(parmPeriodDate),1),"MM-yyyy"), "dd-MM-yyyy")),-1),"dd-MM-yyyy"),"dd-MM-yyyy")

It takes the parameter (e.g. 15.02.2023), adds a month (15.03.2023), makes the 1st of that month (01.03.2023), and subtracts 1 day (28.02.2023).

Null date transformation

Date formatting: Show an empty date in Excel as blank

An empty date in the format data source is printed in Excel as 02.01.1900. This is not good and very distracting. Again, an obvious solution is DATEFORMAT() in a string type cell, but it just doesn’t feel right.
An elegant approach is a generic Transformation in the Format designer: Formula transformation NoNullDate =
IF(parameter<=NULLDATE()+1, "", DATEFORMAT(parameter, "dd-MM-yyyy"))
This can be re-used and quickly applied to every node:

On currency in credit card expense transactions – Part 2

On currency in credit card expense transactions – Part 2

In the previous blog On currency in credit card expense transactions – Part 1 we have explored a case where the credit card currency and the local accounting currency in D365FO did match. But what if the card is issued in a currency different from the accounting currency of the legal entity?

Case 2: credit card currency <> accounting currency

For example, Hungarian employees (the official currency of Hungary is the Forint, abbreviated as HUF) may be given credit cards billed in Euro, since they are surrounded by the Euro zone. If we follow the same logic as before, if such a credit card statement is expressed in EUR, then it must be posted in EUR into the AP subledger:
  • 01.06.2019 Paid a hotel in Bratislava 500 EUR (the credit card provider applied the cross rate of 1.00)
  • 04.06.2019 Posted in AP subledger as 500 EUR (the daily rate was 322 HUF/EUR)
  • 30.06.2019 The credit card balance of June was withdrawn by the bank from the employee’s daily account in HUF @323,39 = 161 695 HUF. This is not recorded in D365FO for personal credit cards.
  • 30.06.2019 An AP revaluation is performed in D365FO. The liability is now worth 161 450 HUF at the current exchange rate of 322,9 HUF/EUR
  • 01.07.2019 The employee is reimbursed in the local currency by the employer with 161 450 HUF @322,9 HUF/EUR.
As a result, the employee will suffer a loss of 245 HUF but have to arrange himself/herself, as the benefits of travelling with an Euro card should overweigh any potential losses from the imperfect execution in D365FO by far.

Case 3: credit card currency <> accounting currency <> local currency

What if the trip was not to Slovakia (EUR) but to the Czech Republic who refused to adopt the Euro and retained their own Czech koruna (CZK)? Now all 3 currencies in the transaction are different:
  • The legal entity Accounting currency = HUF
  • The transaction Local currency = CZK
  • The Credit card Currency = EUR
The equation is still the same, the credit card balance is posted as a Forex liability in EUR. In the General Ledger voucher / accounting distribution the cross rate CZK -> HUF is kind of triangulated through the Euro: CZK -> EUR -> HUF where CZK -> EUR is the rate applied by the credit card institution. With that in mind, let us summarize the different business cases with the 3rd being the most generic and sound:
Case 1 Case 2 Case 3
Credit card Currency CHF EUR EUR
CC “Local” currency THB EUR CZK
Accounting currency CHF HUF HUF
Expense line THB EUR CZK
Line itemizations THB EUR CZK
Acc. distribution THB EUR CZK*
GL voucher Dr THB – Cr THB** Dr EUR – Cr EUR Dr CZK – Cr CZK**
AP subledger CHF EUR EUR
(*) The accounting currency HUF equivalent i.e. ‘amount MST’ is evaluated through the rate EUR -> HUF (**) Note the currency mismatch between the AP and GL ledgers. This spoils the AP ledger account with transactions in foreign currencies and may potentially kick the subledgers off balance.