Skip to Content
avatar image
Former Member

Need help with formula to filter based on certain conditions

So I have a fairly specific question. I have a table with 6 columns:

Date, Workcenter, PartNo, UnitPrice, PcsFinished, and Output (UnitPrice x PcsFinished)

If Pcsfinished is equal to 1, I sum the Output to get the "Total Output"

If PcsFinished is equal to 0, I sum the UnitPrice and call it "In Process"

Problem is, in the same day, pieces can go from "In Process" to a part of "Total Output" in the same day. A part may be in process in the morning and have line such as

3/15/17 - Cutting - 10320.1 - $2,550 - 0 - $0

and later in the day it gets completed so it creates another line item in our database that looks like:

3/15/17 - Cutting - 10320.1 - $2,550 - 1 - $2,550

Problem for me is that in my report, it shows total output as $2,550 and total in process of $2,550 as well, which is wrong. Once the part is complete (1), it should no longer show as part of "In Process". I tried this so far in my formula for "In Process" and it didn't work:

{TimeTicketDet.WorkCntr} = 'Cutting' and

{TimeTicketDet.PiecesFinished} = 0 and

Maximum({TimeTicketDet.PiecesFinished}) <> 1

Basically I want to sum if PcsFinished = 0, unless the max PcsFinished for that PartNo is equal to 1. I guess the part I'm missing is somehow associating the PartNo to my formula, no idea how to go about this. Any help is appreciated, thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Mar 16, 2017 at 05:34 PM

    Anyone??????

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 26 at 08:43 PM

    changed tag to CR, you may want to report to Crystal Reports Tag

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 27 at 08:00 AM

    Assuming there is a Time stamp on your data order entries so that completed item appears before In Process.

    Group date by PartNo, suppress Group header and details, move Date, Workcenter, PartNo, UnitPrice into group footer

    Create 3 Formula

    @reset // place this in Partno group header

    Whileprintingrecords;

    Global Booleanvar Complete:=False

    @Eval// place this in details

    Whileprintingrecords;

    Global Booleanvar Complete;

    If Pcsfinished = 1 then Complete:= true

    @Display// place in Group footer

    Whileprintingrecords;

    Global Booleanvar Complete;

    Global numbervar InProcess;

    If Complete = false then InProcess:= InProcess+PcsFinished;

    InProcess;

    This will provide a Running total of Pcsfinished

    Ian

    Add comment
    10|10000 characters needed characters exceeded