1 2 Previous Next 16 Replies Latest reply on Jul 17, 2017 12:26 PM by ljungholms

# Need help on a calculation

I am looking to create a series of calculations using IF statements. I have 2 field in my related table. Round # and Pass/Fail.

I need to calculate the pass rate as a percentage for RD1, RD2 etc.

So

if round 1, summary of field/total round 1 records

if round 2, summary of field/total round 2 records

Kind of stuck on this one.

Thanks

• ###### 1. Re: Need help on a calculation

By the way, this is what I currently have.

Status Flag   -- 1 is pass 0 is fail

Status Flag Summary - Summary of Status Flag

QC Round Count - Count (QC Data::Status Flag Number )

Pass percentage

QC Data::Status Flag Summary/QC Round Count

This works as a start. Now I need to figure out how to only make it work if Round=1, then another calc if Round=2

• ###### 2. Re: Need help on a calculation

Anyone have any ideas??

• ###### 3. Re: Need help on a calculation

There are a couple of different options I can think of here:

1). Create additional table occurrences where the relationships are hard-coded to round 1 and round 2 (gets a little convoluted if you have a variable number of rounds) and then base the calculation on those table occurrences

2). Create additional table occurrences but instead of hard-coding the relationships, use a single-line filtered portal to show a summary field (again this becomes convoluted if you have a variable number of rounds)

3). Use ExecuteSQL

4). Change your layout structure so that you can use subsummary parts sorted by Round. (The simplest solution, but may not meet your UI requirements)

• ###### 4. Re: Need help on a calculation

I am looking to create a series of calculations using IF statements. I have 2 field in my related table. Round # and Pass/Fail.

I need to calculate the pass rate as a percentage for RD1, RD2 etc.

So

if round 1, summary of field/total round 1 records

if round 2, summary of field/total round 2 records

Kind of stuck on this one.

Thanks

Instead of If statements  the case function may simplify the calculation

case (

Field1 = X; *Do this calculation";

Field1 = Y; "Do a different calc";

)

You can then expand the arguments and if needed use intermediate calc fields

• ###### 5. Re: Need help on a calculation

Thanks. I still can't get it working. I have a feeling it is due to the summary field.

What I really need is a summary of a field if Round=1

Another summary if Round=2

• ###### 6. Re: Need help on a calculation

when you say summary of field?   do you mean the number of "pass"   counted when roundfield = 1?

• ###### 7. Re: Need help on a calculation

If you really want to go that route, you can create additional calc fields in your related table:

Round1Count: If ( Round = 1 ; Status Flag ; 0 )

Round2Count: If ( Round = 2 ; Status Flag ; 0 )

and then summary fields of each of those. Again this becomes convoluted if you have a lot of rounds.

• ###### 8. Re: Need help on a calculation

Yes, the rounds do vary, usually not more than 7. I just hate creating that many fields when a calculation might work. May be the OCD in me.....

• ###### 9. Re: Need help on a calculation

I agree that creating extra fields for that is not the best approach.

Using one of the other options I mentioned is probably a better approach.

If you can restructure your layout to use a list view with sub-summary part that would probably be the simplest.

Otherwise, ExecuteSQL is a good option, but, if that is overwhelming, using additional table occurrences (either hard-coded or through a single-line filtered portal) would also work.

• ###### 10. Re: Need help on a calculation

ljungholms:

Since "Status Flag" has a value 100% (1) or 0% (0), set your Summary field to be an Average of Status Flag.

Find all records for Round 1, and if there are 5 records, 2 passing, you will get an average of .4 or 40%.

Set up a Sub-summary by Round, sort by Round, and include the Summary field in the Sub-summary part.

TSGal

FileMaker, Inc.

• ###### 11. Re: Need help on a calculation

Thanks for the help everyone. I am attaching a dummy file I created to show everyone what I am trying to do.

• ###### 12. Re: Need help on a calculation

Can't look at your file with my smart phone and my wife will shoot me if I dig out a computer while on vacation, but you might need the getSummary function if you need sub totals of the type you see in subsummary parts.

• ###### 13. Re: Need help on a calculation

Phil! Shut off your email. Some may not like to hear it, but the Community will still be here when you return. There's plenty of time to solve all the FileMaker world's problems AFTER you've had a good relaxing vacation. Just do it.

1 of 1 people found this helpful
• ###### 14. Re: Need help on a calculation

ljungholms:

Enclosed is your updated file.  I used Average Summary fields to get the values you wanted for each Round.  I left your original fields in place.

TSGal

FileMaker, Inc.

1 2 Previous Next