Parse values from a list then count

[Rich S]

Good morning, all! I have a brain hernia for you:

 

I've been tasked with creating summary reports and I'm stuck with how to best go about parsing discrete values out of each "checkbox'd" field so each value can be counted then summarized. (Mind you, the file resides on our FMP server so I know there may be some limitations when it comes to scripting.)

 

So, say there's a checkbox'd field named Fruit and it has the following ticked values out of a total of say, ten different different values:

 

Apple

Grape

Peach

Orange

 

The found set has thousands of records; each record has either none, some, or all of the values available. I take it what I need to do is parse the field into separate records (in a newly-made child table) using a loop, but I just don't know how (using GetValue? PatternCount?) to go about scripting this.

 

Your guidance is much appreciated!

[Kris M]

You could use find in list custom function to populate calculations for counting purposes.

 

http://www.briandunning.com/cf/989

 

With this you might not have to restructure.

 

Calc field = FindInList( checkbox field ; "Apple" ) would return 0 if "Apple" was not in the checked in the checkbox field

[LaRetta]

Whenever there is a possibility that you will need to generate reports based upon counts of checkbox values, it indicates that those values should be records in a related table and eliminate the checkbox as the selection tool in this instance (using portal instead).  So to help you switch over, here is one way (script fired from parent layout):

Show All Records
Go to Record/Request/Page [ First ]
Loop
If [ not IsEmpty ( Parent::Checkbox ) ]
Set Variable [ $id; Value:Parent::ID ]
Set Variable [ $values; Value:Parent::Checkbox ] 
Freeze Window
Go to Layout [ “Child” (Child) ]
Loop
ExitLoopIf [ Let( $i = $i + 1 ; $i > ValueCount( $values )) ] 
New Record/Request
Set Field [ Child::ParentID; $id ]
Set Field [ Child::Value; GetValue ( $values ; $i ) ]
End Loop
Go to Layout [ original layout ] 
Set Variable [ $i; Value:"" ]
End If
Go to Record/Request/Page[ Next; Exit after last ]
End Loop

After this migration, create a relationship from Parent::id = Child::ParentID

Once the migration is complete, generate a regular report in the child table.  Create a summary field in child table called sCountKids (smile) which is count of child id.

 

Seems I had system problems.  To finish, you would have a report in the child table sorted by value.

Edited February 11, 2015 by LaRetta

[comment]

I agree that you should very likely split the values into separate records in a related table (I would be more decisive with a less abstract example).

 

 

Still, as a quick fix, you could define a repeating calculation field with 10 repetitions (result is Number) =

not IsEmpty ( FilterValues ( GetValue ( ValueListItems ( Get (FileName) ; "YourValueList" ) ; Get ( CalculationRepetitionNumber ) )  ; Extend ( YourField ) ) )

and summarize (total) it using a summary field set to summarize repetitions individually.

[Rich S]

Thank you all!

 

When I took this job a few months ago, I found that nearly all of the databases were built by a couple of staff members who were novice-level users of FileMaker Pro so I'm doing a lot of "damage control" to try to produce reports requested by the Dean of our division. Ugh. Methinks it would be best just to rebuild all of them from scratch and use proper separation modeling and techniques, but it'll take y-e-a-r-s to make things right. Talk about "technological debt"... :S