9 Replies Latest reply on Jul 11, 2016 8:44 AM by LabsRock

    Upload data to check box field

    Cécile

      I am new to FM and working with a DB that was created a long while ago. I figured how to do mass import but I have trouble with one of the fields.

       

      The field name is Keywords2005. In the record layout view, it displays in an edit box (which is not editable because the box acts as a button to fire the script Go to keywords2005 which opens keywords2005 field's layout view) the values checked in keywords2005 field's layout view (each record can have several boxes/values checked for that field).

       

      In Excel I have one column named Keywords2005 and for each record, I enter in that cell all the values (value, Alt+enter, value, etc.) that should appear in the display edit box on the record layout view and checked in the keywords2005 field's layout view).

       

      When I import the records, I can see the values properly displayed in the record layout view Keywords2005 edit box. However, if I click the Keywords2005 edit box to access the field layout view, none of the check boxes are checked.

       

      I suspect that I cannot upload from Excel one cell with multiple data to a field containing multiple check boxes. How to do this properly? Thank you!

       

      Database Values:

      Value List Name= Keywords2005Source=From FieldValues= Field: "Files::Keywords2005"
      Value List Name= Kewordsnew    Source=From FieldValue="Green","Blue","Red"

       

      Layout Keywords2005, Keywords2005 field setup:

      Display as: Checkbox Set

      Display values from: Keywordsnew

       

      Script: Go to Keywords2005

      Set Field[Graphics::Layout Number; Get(LayoutNumber)]

      Go to Related Record [Show only related records; From table: "Files"; Using layout: "Keywords2005"(Files)]

        • 1. Re: Upload data to check box field
          mikebeargie

          I'd imagine the raw data you import from excel does NOT match the format for checkbox fields in filemaker.

           

          Filemaker checkbox fields are just return-delimited text lists.

           

          EG if red and green was checked in filemaker, the text field would store:

          red

          green

          Formatting a field as a checkbox in filemaker essentially masks that text value into a checkbox set.

           

          So if your excel data has something like:

          red, green

          It will NOT work, since that is not a return delimited list.

           

          Fortunately, the calc you need to run is fairly simple:

          Substitute( Keywordsnew ; "," ; "" )

          will give you a return delimited list. Just run that as a "replace field contents" calculation on the raw data of your table.

           

          Note that if the values also have the quotes wrapped around from excel, you can strip those out too:

          Substitute( Keywordsnew ; [ "," ; "" ] ; [ "\"" ; "" ] )
          1 of 1 people found this helpful
          • 2. Re: Upload data to check box field
            LabsRock

            The way you've indicated the values for the new Value List Keywordsnew ("Green", "Blue", "Red") suggests that it is a Custom list, not Values from a field as the old list was. If this is the case, the values in the field need to match one of the values in the list exactly or they will not show.  For example, with a custom list of Green, Blue, and Red, Grean or  Green (that's Green with an extra space in front of it) won't show.

             

            Craig

            1 of 1 people found this helpful
            • 3. Re: Upload data to check box field
              Cécile

              My data was manual line break delimited   ^|    which took me a while to figure how to search in Excel (Alt+0010 in the find box... you don't see the character but it is there).

               

              Now, my question is, if my check boxes are

              Blue

              Green

              Red

              Magenta

              and the value for a record is Blue and Magenta, do I need to put pilcrows to mark the rank of the unchecked values?

              EX: Blue¶¶Magenta

              Or, as Craig's comment seems to suggest, FM does matches entries to values provided that they are identical, regardless of the order they come in?

              • 4. Re: Upload data to check box field
                LabsRock

                No need to mark the rank or add extra returns.  If you're a visual type, you might want to try putting the same field twice on the same layout.  Set one field with the check boxes and leave the other as an edit box (large enough to see multiple lines of text).  Then check/uncheck boxes and watch what happens to the text in the other field.

                 

                Craig

                • 5. Re: Upload data to check box field
                  mikebeargie

                  However, filemaker will mask empty values if you have extra returns in your data.

                   

                  When you apply a checkbox style, it will not show any empty checkboxes in between values.

                  • 6. Re: Upload data to check box field
                    Cécile

                    Thank you all for such helpful information. I have left for the day so I will try that tomorrow morning and let you know!

                    • 7. Re: Upload data to check box field
                      Cécile

                      Hello Craig and Mike,

                       

                      Based on the information you provided me, I eventually solved the problem. Hereinafter the conclusions:

                       

                      Filemaker checkbox fields are just return-delimited text lists. --> true but the return is a manual line break delimited   ^| which must be typed in ASCII in Excel (Alt+0010 in the find box... you don't see the character but it is there). The pilcrow does not work: it actually appears inline in the text box.

                       

                      When you apply a checkbox style, it will not show any empty checkboxes in between values --> Correct

                       

                      The way you've indicated the values for the new Value List Keywordsnew ("Green", "Blue", "Red") suggests that it is a Custom list, not Values from a field as the old list was. If this is the case, the values in the field need to match one of the values in the list exactly or they will not show. --> Crucial! In my particular case, I finally realized that there were extra blank spaces AFTER the words, once those accounted for, boxes got checked properly.

                       

                      Since you both provided clues to reach the solutions I am putting the response here as it summarizes key elements. Thank you so much again!!

                      • 8. Re: Upload data to check box field
                        mikebeargie

                        Good to hear our hints were on the right track.

                        • 9. Re: Upload data to check box field
                          LabsRock

                          Hi Cecile,

                          Thanks for the summary/recap - it undoubtedly will be helpful to anyone in a similar situation who comes across this thread.  Glad to hear you got things sorted out.