Validate a FlowField Field. Wait? What?

Validate a FlowField Field. Wait? What?

There are not many things in the AL Language that surprised me. However, last week, I found one such thing – I reviewed customizations made by another partner and had to analyze the OOTB code of the Demand Forecast matrix.

I run a debugger and end up in the “Production Forecast Entry” table in one of the OnAfterValidate triggers added by the custom extension. However, from the static code review, I could not figure out where exactly the code accessed the “Production Forecast Entry” table. When I analyzed the stack trace when the system ran the Validate function against the Item table, it ran the validations against a completely different table – “Production Forecast Entry”).

My first thought was that it was probably too late and I was hallucinating 🙂

So, what is in the base? Everything starts with the following procedure on page 2900 “Demand Forecast Variant Matrix”

Let’s look at the field – the field is a FlowField (a calculated field not stored in the database).

So, what will happen when the code tries to validate a new value against the field that is not stored anywhere? This is where the fun begins.

Undocumented, but has existed since the very first version of Navision

I tried to search for some documentation, but I did not find anything… I would expect at least a few words about this behaviour in one of the following overviews.

• FlowFields overview – Business Central | Microsoft Learn
• Creating FlowFields and FlowFilters – Business Central | Microsoft Learn

However, I remembered reading something on Mibuso about flowfields and data modification a few years back. I could not find the original discussion, but fortunately, I found another discussion with a good explanation from one of our dinosaurs – David Singleton (thanks, David!).

This is standard NAV behaviour, and has been part of Navision since the early DOS versions.

Basically what happens is that NAV looks at the current filters applied to the underlying source Table. It then looks at the sum of the records, and compares this to the number that you just entered. Now NAV creates a new Record in the source table, with a value that will then add up to the new total that you want. It then adds a new record.

For this to work, the Primary key of the source table must end in an integer.

This feature is mainly used for Budgets, in the Base App, but can I use it a lot for many other types of functions.

David Singleton, 10/2007 (yes, it’s really 16 years ago – Validating a flow field?? – Dynamics 365 Business Central/NAV / Developers Forum – Dynamics User Group

So, what if we don’t want to use this approach? How would the explicit code look, which will have the same output as when we validate a flowfield field? Probably similarly to the code below:

procedure ReplaceFlowFieldValidate(ProductionForecastName: Code[20]; ItemNo: Code[20]; VariantCode: Code[10]; ForecastDate: Date; LocationCode: Code[10]; ComponentForecast: Boolean; NewQuantity: Decimal)
var
    ProductionForecastEntry: Record "Production Forecast Entry";
begin
    ProductionForecastEntry.SetRange("Item No.", ItemNo);
    if VariantCode <> '' then
        ProductionForecastEntry.SetRange("Variant Code", VariantCode);
    if ProductionForecastName <> '' then
        ProductionForecastEntry.SetRange("Production Forecast Name", ProductionForecastName);
    if ForecastDate <> 0D then
        ProductionForecastEntry.SetRange("Forecast Date", ForecastDate);
    if LocationCode <> '' then
        ProductionForecastEntry.SetRange("Location Code", LocationCode);
    ProductionForecastEntry.SetRange("Component Forecast", ComponentForecast);

    if not ProductionForecastEntry.IsEmpty() then begin
        ProductionForecastEntry.CalcSums("Forecast Quantity (Base)");
        NewQuantity -= ProductionForecastEntry."Forecast Quantity (Base)";
    end;

    if NewQuantity = 0 then
        exit;

    Clear(ProductionForecastEntry);
    ProductionForecastEntry.Validate(ProductionForecastEntry."Production Forecast Name", ProductionForecastName);
    ProductionForecastEntry.Validate(ProductionForecastEntry."Item No.", ItemNo);
    ProductionForecastEntry.Validate(ProductionForecastEntry."Forecast Date", ForecastDate);
    ProductionForecastEntry.Validate(ProductionForecastEntry."Forecast Quantity", NewQuantity);
    ProductionForecastEntry.Validate(ProductionForecastEntry."Location Code", LocationCode);
    ProductionForecastEntry.Validate(ProductionForecastEntry."Forecast Quantity (Base)", NewQuantity);
    ProductionForecastEntry.Validate(ProductionForecastEntry."Component Forecast", ComponentForecast);
    ProductionForecastEntry.Insert(true);
end;

I believe this can be useful, but it should be described in Microsoft Learn posts about flowfields.

What is your opinion? Have you known about this behaviour? Have you ever used it?