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 )
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
Anyone have any ideas??
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)
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.
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.
Instead of If statements the case function may simplify the calculation
Field1 = X; *Do this calculation";
Field1 = Y; "Do a different calc";
You can then expand the arguments and if needed use intermediate calc fields
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
when you say summary of field? do you mean the number of "pass" counted when roundfield = 1?
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.
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.....
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.
Thank you for your post.
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.
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.
1 of 1 people found this helpful
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.