Skip to main content
Post a question

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id : rnK0GIPWx/xTYqC8xkdCt+
Small and medium business | Business Central, N...
Answered

Flow field with SUM and MIN

Like (0) ShareShare
ReportReport
Posted on 16 Jan 2021 00:01:42 by 1,159

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
  • Rupali  Profile Picture
    Rupali 335 on 02 Feb 2021 at 22:30:11
    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 44,507 on 17 Jan 2021 at 01:32:57
    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,159 on 17 Jan 2021 at 00:32:06
    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 44,507 on 16 Jan 2021 at 20:38:03
    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,159 on 16 Jan 2021 at 20:03:40
    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 44,507 on 16 Jan 2021 at 19:15:39
    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,159 on 16 Jan 2021 at 18:59:11
    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 44,507 on 16 Jan 2021 at 12:08:39
    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,159 on 16 Jan 2021 at 12:04:27
    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 44,507 on 16 Jan 2021 at 01:53:16
    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

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,468 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,287 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans
Loading started