web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Flow field with SUM an...
Small and medium business | Business Central, N...
Answered

Flow field with SUM and MIN

(0) ShareShare
ReportReport
Posted on by Anita75 1,165

Hi All,

I want to add a flowfield to Item table to calculate quantity of earlier planned purchase order but below doesn't work;

            CalcFormula = sum("Purchase Line"."Outstanding Quantity"
                                                            WHERE("Document Type" = const(Order),
                                                                    "No." = field("No."),
                                                                    "Outstanding Quantity" = FILTER(<> 0),
                                                                    "Planned Receipt Date" = min("Purchase Line"."Planned Receipt Date")


                                                            ));
            Caption = 'Earliest PO Qty';
            DecimalPlaces = 0 : 5;
            Editable = false;
            FieldClass = FlowField;
Not sure how best to achieve this or if there an entirely different solution. Maybe flow field on another flowfield but this doesn't i suppose
Thanks
I have the same question (0)
  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    Hello,

    There is no option called Min when you apply the filter either you can reference the Field or Filter you cannot use Min. If you want that value only for display purposes then you can add a calculated field on the page which is a variable you will calculate the value based on your requirement in OnAfterGetCurrRecord of the page.

  • Anita75 Profile Picture
    Anita75 1,165 on at

    Thanks I worked on a function but the last bit of setting MIN filter has trouble ..not sure GETRANGEMIN works as below:

        begin

            PurchaseLine.Reset();

            PurchaseLine.SETRANGE("No.", Rec."No.");

            PurchaseLine.SetRange("Document Type", PurchaseLine."Document Type"::Order);

            if PurchaseLine.FindSet() then

                begin

                PurchaseLine.GetRangeMin("Planned Receipt Date");

                PurchaseLine.CalcSums(PurchaseLine."Outstanding Quantity");

                TotQty := PurchaseLine."Outstanding Quantity";

            end;

            exit(TotQty);

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    GetRangeMin is used to get the filter, what is your actual requirement can you define it in detail

  • Anita75 Profile Picture
    Anita75 1,165 on at

    Ok thanks so GetRangeMin is not the correct approach ..Here's the actual scenario..

    On the Item List Page I want to display the Quantity Outstanding on purchase order but only for the earliest planned receipt date on open purchase order lines. So instead of going through a list purchase order lines, item list will show quantity planned to receipt on next available PO (can have more than one on same date)..hope this make sense

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    So you want to know the quantity on purchase lines where the Planned Receipt Date is greater than today or earlier then today? if not, provide me an example

  • Anita75 Profile Picture
    Anita75 1,165 on at

    I'll try to give data as below example:

    PO Line Table

    Item   Qty  Plan Receipt Date (YYYY-MM-DD)

    A         10      2020-01-30

    A          20     2020-01-20

    B          5        2020-02-12

    B          1        2020-03-23

    A          1       2020-01-20

    Now in Item List page we want to show

    Item     Earliest Plan Receipt Qty

    A           21

    B             5

  • Verified answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    So basically the Planned Receipt Date is less than today's date, so you can apply that filter and count the quantity. Something like the below should work for your scenario.

    local procedure GetOutstandingQty(ItemNo: Code[20]): Decimal
    var
        PurchLine: Record "Purchase Line";
        OutStdQty: Decimal;
    begin
        PurchLine.Reset;
        PurchLine.SetRange("No.",ItemNo);
        PurchLine.SetFilter("Planned Receipt Date",'
  • Anita75 Profile Picture
    Anita75 1,165 on at

    Thanks this helped me to find a temporary solution as I created a flow field in Item table with Min date and applied it as below but I'm sure there is a better solution without creating field in item table:

       begin

           PurchLine.Reset;

           PurchLine.SetRange("Document Type", PurchLine."Document Type"::Order);

           PurchLine.SetRange("No.", ItemNo);

           //PurchLine.SetFilter("Planned Receipt Date", '<%1', Today);

           PurchLine.SetFilter("Planned Receipt Date", '=%1', Rec.EarliestPOArriveDate);

           if PurchLine.FindSet() then

               repeat

                   OutStdQty += PurchLine."Outstanding Quantity";

               until PurchLine.Next() = 0;

           exit(OutStdQty);

    result

    Earliest-PO-date.jpg

    I don't think adding flowfields just for one report is a good idea except when you want to drill down

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    Great, as long as it meets your needs you should be good, and there is no hard rule for creating flow field that  you need to add the flow field only when you want to use multiple times.

  • Rupali Profile Picture
    Rupali 335 on at

    Is there any way we can compare two columns here calcFormula?For example if the Planned Receipt Date is equal to Promised Receipt data under Purchase Line Table?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 3,229

#2
Jainam M. Kothari Profile Picture

Jainam M. Kothari 1,867 Super User 2025 Season 2

#3
YUN ZHU Profile Picture

YUN ZHU 1,153 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans