2 Replies Latest reply on Dec 8, 2013 3:21 PM by JeanneM

    Quick way to translate checkbox items into different columns in Excel export?

    JeanneM

      Title

      Quick way to translate checkbox items into different columns in Excel export?

      Post

           When using Excel to deal with some FMP data, it is sometimes very helpful to easily and quickly sort by some checkbox items. However, this doesn't work too well with the standard Excel export because everything from one checkbox set comes out in one column. This means, you can't sort quickly and checkbox items that are checked can actually be in a different order, depending on when you checked them.

            

           Does anyone have a suggestion for how to quickly convert the checkbox items into their columns in Excel?

            

           I have thought about discontinuing the use of checkboxes (since it seems they were not made for this), but it would be such a pain to create separate fields for every option...

            

           Thank you! (I am on FileMaker Pro 8)

        • 1. Re: Quick way to translate checkbox items into different columns in Excel export?
          philmodjunk

               I suggest using a separate field with one checkbox for each such value in your set of check boxes. Then they will export cleanly to Excel, the order clicked won't matter and if you group the fields together, they will still have the same "look and feel" of a single field with a group of check boxes.

               You can use a bunch of replace field contents operations to do a "one time fix" to move the data from your existing check box field into the newly added separate fields.

               In some cases, it even pays to go one step further and make your set of check boxes indivdiual records in a related table, but I don't see a reason for this in what little I can deduce in this post.

          • 2. Re: Quick way to translate checkbox items into different columns in Excel export?
            JeanneM

                 I did come up with a solution for this (sorry, again, a year later)...

                 One of the uses I had for translating one field's values into separate columns was to make a class roll.

                 I was able to keep all the class enrollments in one field.

                 Then, I had to create separate fields for EACH class and calculate their value as such:

            If ( PatternCount (— 1Fall Classes; "FRI - Bal 3-6;") ≥ 1; "X" ; "" )

                  

                 Then I made a layout that made a matrix of the enrolled students, those calculated fields above for each class, in each row.

                 It worked for me, and I think I might be able to adapt this to other uses for separating out a check box's contents.

                  

                 However, this was very time-consuming to set-up. It was a huge help, though, once it was set-up.