web
You’re offline. This is a read only version of the page.
close
Skip to main content
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,161

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)
  • Rupali Profile Picture
    Rupali 335 on at
    RE: Flow field with SUM and MIN

    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?

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,080 Super User 2025 Season 2 on at
    RE: Flow field with SUM and MIN

    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.

  • Anita75 Profile Picture
    Anita75 1,161 on at
    RE: Flow field with SUM and MIN

    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

  • Verified answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,080 Super User 2025 Season 2 on at
    RE: Flow field with SUM and MIN

    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,161 on at
    RE: Flow field with SUM and MIN

    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

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,080 Super User 2025 Season 2 on at
    RE: Flow field with SUM and MIN

    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,161 on at
    RE: Flow field with SUM and MIN

    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,080 Super User 2025 Season 2 on at
    RE: Flow field with SUM and MIN

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

  • Anita75 Profile Picture
    Anita75 1,161 on at
    RE: Flow field with SUM and MIN

    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,080 Super User 2025 Season 2 on at
    RE: Flow field with SUM and MIN

    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.

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

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

#1
Nimsara Jayathilaka. Profile Picture

Nimsara Jayathilaka. 4,220

#2
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 3,958

#3
Sumit Singh Profile Picture

Sumit Singh 2,961

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans