Jump to content

Parse values from a list then count


This topic is 2852 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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 by LaRetta
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2852 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.