3 Replies Latest reply on Dec 22, 2015 3:15 PM by BowdenData

    Splitting Check Box field into multiple sub-records

    BrianKeller

      I am working on a project and need to be able to take the text within a checkbox field and create multiple records in a sub-table.  For example, when we have an issue it gets logged in Filemaker and the impacted departments are marked via checkboxes.  Sometimes issues impact 3 departments and sometimes they can impact all of our departments at the University (over 25). 

       

      We know the checkboxes are just attached to a text field which is populated with the department name as they are checked.  We would like to read that field and populate a sub-table with one record for each department showing the outage day and time.  This data then is uploaded into a reporting tool which creates the charts and graphs we need that is reported to our Executives. 

       

      Is there a way to read the checkbox field and create one record for each entry in it?

       

      Thank you,

       

      Brian Keller

        • 1. Re: Splitting Check Box field into multiple sub-records
          BowdenData

          You just need a script that loops through the values in the checkbox field pulling out each department name. Here is an example of the basic code to do this. Make sure and test this code to ensure that all values in the checkbox field are being grabbed during the loop!

           

          Doug

           

          Set variable [$deptList; CheckBoxField ]

          Loop

            Exit Loop If [ Let $i = $i + 1 ; $i > ValueCount ( $deptList ) ) ]

            Set variable [ $theDept ; getValue( $deptList; $i )]

             // $theDept will hold the actual department text

             // now go and create the child record however you want and populate values as needed

             // this could be by calling a script with parameters, using Set Field with a relationship that has create turned on, etc.

            Commit Record

          End loop

          • 2. Re: Splitting Check Box field into multiple sub-records
            BrianKeller

            Thanks Doug,

             

            I did figure out the below which works but am going to look at yours as using "Let" will streamline it.

             

            Set Variable [$tmpDepCount; Value: ValueCount ( Outages::Departments)]

            Set Variable [$tmpCounter; Value: If ( $tmpDepCount > 0; 1 ; 0)]

            Loop

               Set Variable [$tmpDepName; Value: GetValue ( List ( Outages::Departments ) ; $tmpCounter)]

               Exit Loop If  [$tmpCounter = $tmpDepCount]

               Set Variable [$tmpCounter; Value:  $tmpCounter + 1]

            End Loop

            • 3. Re: Splitting Check Box field into multiple sub-records
              BowdenData

              Almost always more than one way to do things in FileMaker! I like using the "Let" for exactly the reason you stated - it makes the code a few less lines and more readable.

               

              One thing to note on this though. If you were using a List that had a lot of entries, I would alter my code a bit. I would define a variable before the loop that is the count of the values in the field. This is where you have $tmpDepCount in your code. Then in the Let statement, test against this variable instead doing the ValueCount each time.

               

              The reason for this is to speed up the loop by NOT having to execute the ValueCount command every time it loops. Although the speed difference might be minor, this kind of thing adds up.

               

              It would probably be a good thing to do this all the time, then I would not have to worry about optimizing this loop if the List contents were more than "X" lines, etc.

               

              Doug