9 Replies Latest reply on Sep 22, 2016 4:14 PM by slokenny

    How do I create a list showing a new line for each record with multiple Value List checkboxes selected?

    slokenny

      I have a table with one of the fields being a Value List of 41 different checkboxes. I want to run a report that lists all records with a new line for each of the value list selected.

       

      If Part #10001 has Option A selected (but not B or C), it would show as:

       

      Part     Option

      10001     A

       

      If Part #10002 has Option A and B and C selected it would show a separate line for each of the selected options:

       

      Part     Option

      10002     A

      10002     B

      10002     C

       

      How do I create this list?

       

      SloKenny

        • 1. Re: How do I create a list showing a new line for each record with multiple Value List checkboxes selected?
          longball22

          Is this the exact setup? The field the checkbox is displaying data from holds ALL checkbox items.

           

          In your example, the 'Option' field would look like "A B C"  if A, B and C were checked IN THAT ORDER. If you check C, then B, then A, the value of 'Option' would be "C B A". For some reason Filemaker puts each value on a different line within that field.

           

          What I would do, if you have one letter and only one letter for each checkbox item, is find the length of the 'Option' field and create that many new records, copying the Part number to each new field and going to the next value by means of using the Left or Right functions.

          • 2. Re: How do I create a list showing a new line for each record with multiple Value List checkboxes selected?
            Mike_Mitchell

            Building on what longball22 has said, the fact that you have 41 different (potential) values in a single field is a sign of poor database normalization. This should probably be set up as a related table, where each record corresponds to a choice attached to the parent record. Then reporting becomes much easier.

             

            Otherwise, you wind up with having to parse out several return delimited lists to assemble your report. It can be done - FileMaker has a number of very useful functions for working with lists - but if it were me, I'd back up and rework the data model.

             

            Mike

             

            P. S. FileMaker does it that way (return delimited list in a checkbox field) for a few different reasons. First, it allows something called a "multi-key" - where any value in the list can match any value in another table's field. It's a way of implementing a many-to-many join (although not the preferred way), and has a lot of utility in other situations, too. Second, there are a lot of functions that deal with lists. Third, and probably more basic, it allows each value to be indexed independently.

            • 3. Re: How do I create a list showing a new line for each record with multiple Value List checkboxes selected?
              longball22

              I understand that if you have all 41 values selected, you would have 41 records in the related table. How would you automate adding new records to the related table when you make new selections to the original table?

              • 5. Re: How do I create a list showing a new line for each record with multiple Value List checkboxes selected?
                philmodjunk

                You would not make selections in the original table, you'd achieve the same result by adding new records to the related table.

                 

                Think of a sales invoice. One sales transaction, one Receipt, but a flexible number of items purchased in that one sales transaction. In a system such as FileMaker, we set up one table for the Invoice records another table for the line items--the items listed on each invoice and link them in a relationship. A portal to line items can be used to create the list of items purchased on an invoice layout. Reports can then be created from layouts based on the Line items table, to show how many items of each product in inventory has been sold, for example.

                 

                That's the basic concept. There are more sophisticated interface design options possible--including one that I use from time to time that looks and acts just like a set of check boxes, but "under the hood", you find that table of related records where selecting an item creates a record in that table and deselecting an item deletes it from that table.

                • 6. Re: How do I create a list showing a new line for each record with multiple Value List checkboxes selected?
                  Mike_Mitchell

                  Phil has given you the basics: If you have the related table, you don't really need the checkboxes. You can just use a portal and allow the user to enter the records. (And, as Phil says, there are plenty of more sophisticated interfaces you can build.)

                   

                  But to answer the direct question, given a list of values, how do I create related records based on that list? Here's a basic script for a simple method:

                   

                  Set Variable [ $theList ; Value: yourTable::yourCheckboxField ]

                  Set Variable [ $parentID ; Value: yourTable::primaryKeyField ]

                  Go to Layout [ yourRelatedTableLayout ]

                  Set Variable [ $count ; Value: 1 ]

                  Loop

                       Set Field [ relatedTable::valueInList ; Value: GetValue ( $theList ; $count ) ]

                       Set Field [ relatedTable::foreignKeyToParent ; Value: $parentID ]

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

                       Exit Loop If [ $count > ValueCount ( $theList )

                  End Loop

                  Go to Layout [ original layout]

                   

                  There are plenty of other methods you can use to create the related records - through a portal with "Allow creation" checked on the relationship, through the "Magic Key" approach using a relationship based on a global field, or something like Selector-Connector (which is a fancy setup for using the Magic Key approach). But this basic version will get the job done.

                   

                  HTH

                   

                  Mike

                  • 7. Re: How do I create a list showing a new line for each record with multiple Value List checkboxes selected?
                    philmodjunk

                    Didn't you miss a "new record/request" step inside that loop?

                    • 8. Re: How do I create a list showing a new line for each record with multiple Value List checkboxes selected?
                      Mike_Mitchell

                      Uh ... yeah. Oops!

                       

                      Set Variable [ $theList ; Value: yourTable::yourCheckboxField ]

                      Set Variable [ $parentID ; Value: yourTable::primaryKeyField ]

                      Go to Layout [ yourRelatedTableLayout ]

                      Set Variable [ $count ; Value: 1 ]

                      Loop

                           New Record / Request

                           Set Field [ relatedTable::valueInList ; Value: GetValue ( $theList ; $count ) ]

                           Set Field [ relatedTable::foreignKeyToParent ; Value: $parentID ]

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

                           Exit Loop If [ $count > ValueCount ( $theList )

                      End Loop

                      Go to Layout [ original layout]

                       

                      Thanks for the catch.

                      • 9. Re: How do I create a list showing a new line for each record with multiple Value List checkboxes selected?
                        slokenny

                        I downloaded a trial copy today and am using this tool for the first time. I'm taking a 23k record file with over 1,400 different categories mapped to it and condensing it to just 41 categories. Excel wan't the right tool for this big data migration project.

                         

                        Thanks everyone for your help. I think I got a plan of attack now.

                         

                        Kenny