1 of 1 people found this helpful
The “brute force” method, when all possible values are known …
$countOfAccepted = PatternCount( “¶” & $myValueList & “¶” , “¶” & “Accepted” & “¶” )
$countOfRejected = PatternCount( “¶” & $myValueList & “¶” , “¶” & “Rejected” & “¶” )
$countOfAllergic = PatternCount( “¶” & $myValueList & “¶” , “¶” & “Allergic” & “¶” )
$countOfNotAtRisk = PatternCount( “¶” & $myValueList & “¶” , “¶” & “Not at Risk” & “¶” )
Of course with a little looping, you can tally whatever values appear in the value list ...
Peace, love & brown rice,
FileMaker + Web: Design, Develop & Deploy
Certifications: FileMaker 9, 10 & 11
One Part Harmony
Austin, Texas • USA
It sounds like a sub-summary report should do the trick.
- Create a field in the same table as the Vaccine Status field called "Vaccine Summary". Make it a Summary field that counts the Vaccine Status field.
- Create a list view layout. Change the body part to a sub-summary when sorted by Vaccine Status.
- On the sub-summary part, put the Vaccine Status field and the Vaccine Summary field.
- Switch to Browse mode. Sort the records by Vaccine Status, descending, check the box "Reorder based on summary field" and choose Vaccine Summary as the field. (This will generate the list ordered as you described in your original posting.)
The only thing this WON'T show is any voids (i.e., any Vaccine Status values where the count is zero).
P.S. I wrote all of this up after I couldn't find a way to attach a file. Then I started poking around in the "Advanced Editor" and found it. See attached file if my explanation isn't clear.
Actually you don't want to count the 'value list' but rather count the field data that contains each of the value list values. You mention using a value list but you do not indicate the Control Style (pop-up, checkbox etc) used and it matters. Are Users only allowed to select one value or multiple for a single record? From your options listed it seems only one value is the case because a person couldn't check off 'accepted' and 'refused' could they? If only one value per field (and not a multiline from using checkbox) then you should be able to use summary fields and count them.
If it is checkbox (multiline list) then it it usually best if those values are in a related table. It is easy to make that change and split them off. As a checkbox or multiline list, you cannot count the same record (for example patient JIM) in more than one group for a sub-summary report like this:
But Jim CAN be in more than one part of the report if the Vaccine Status' were related records and you generate the report from there. If you cannot split them off as related, here is an alternate approach in addition to the approach presented by Morgan. It uses filtered portal (if you use vs 11) and it was created by Comment (Michael Horak); see the last file he presents:
Be aware that filtered portals can be slow but it uses existing primary relationship. Much depends upon your particular solution, number of records etc.
Thanks, this worked perfectly!
The layout of the report is different than I had envisioned but it will
After going through your method I see a couple of places that I had strayed.
Ozarks Community Hospital
P Please don't print this e-mail unless it's necessary. Reduce, reuse, and
CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
and privileged information protected by law. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all copies
of the original message.
thanks this worked perfectly,
the layout of the finished report is different than I had planned, but looks great,