9 Replies Latest reply on Jul 1, 2014 8:51 AM by MaxSio

    importing into checkbox field from Excel

    denno

      Title

      importing into checkbox field from Excel

      Post

      I have a field that displays data from a checkbox set. Some users will be collecting data on an Excel spreadsheet and I will be importing into the database. Since this field can have multiple values, is it possible for this data to be captured from the spreadsheet? How should it be entered in Excel?

        • 1. Re: importing into checkbox field from Excel
          philmodjunk

          This will work as long as the return character doesn't get translated into something else during the import. I don't think it will but test to be sure.

          If you enter multiple values into the same cell separated by return characters, this data can be imported into your check box field and as long as the values imported exactly match the values defined for the checkbox field's value list, you will see the appropriate values selected in the checkbox group.

          (In FileMaker, values selected in a checkbox group are stored in the field as a list of values separated by returns and in the order they were selected from the check box group.)

          • 2. Re: importing into checkbox field from Excel
            mgores

            How is the value list for your check box defined?  

            If it is a manually entered set of values, you could set it to allow other values, in which case if someone entered a value not on your list the "other" block would be checked.  

            If the value list is based on that field, an entry from the Excel sheet that doesn't match previous values would add selection to the check box.

            • 3. Re: importing into checkbox field from Excel
              denno

              Thanks for the info.

              One other twist...

              When I create a new record, the following script runs to create the value list choices. This was done in order to facilitate reporting.

              • Set Variable [ $ReviewID; Value:Review::id ]
              • Set Variable [ $ValueList; Value:ValueListItems ( Get (FileName) ; "missing technical factors" ) ]
              • Freeze Window
              • Go to Layout [ “tech review choices” (tech review choices) ]
              • Loop
              • Set Variable [ $I; Value:$I + 1 ]
              • New Record/Request
              • Set Field [ tech review choices::id; $ReviewID ]
              • Set Field [ tech review choices::value; GetValue ( $ValueList ; $I ) ]
              • Exit Loop If [ $I = ValueCount ( $ValueList ) ]
              • End Loop
              • Go to Layout [ original layout ]
              The checkbox set then displays in a portal.
               
               
              With the change in the process to allow for importing from a spreadsheet I made the following changes:
               
              I created a new field (tech review choices ID) so that the Excel users can just enter a letter code rather than the full text of the value list choices.
              I then changed tech review choices to a calc field with the following formula:
               
              Case ( Tech Review Codes = "A" ; "Patient info" ; Tech Review Codes = "B" ; "Labeled images (R or L)" ; Tech Review Codes = "C" ; "Selected operator's initials" ; Tech Review Codes = "D" ; "Used correct application" ; Tech Review Codes = "E" ; "Used correct probe" ; Tech Review Codes = "F" ; "Gain" ; Tech Review Codes = "G" ; "Depth" ; Tech Review Codes = "H" ; "Focus" ; Tech Review Codes = "9" ; "Followed protocol"  )
               
               
              Now I'm hung up on how to display the new calc field and still use the portal method for displaying the value list. Are these 2 methods incompatible?
              • 4. Re: importing into checkbox field from Excel
                philmodjunk

                Not at all what I was imagining here!

                How is the data organized in the spreadsheet. Multiple values in one cell, one value in each cell or one value in each row?

                We just need to import the data into the related table and that depends on how the data is organized in the spread sheet.

                • 5. Re: importing into checkbox field from Excel
                  denno

                  Not at all what I was imagining here!

                   

                  Sorry--I forgot about the related table for the values when I posted my question.

                   

                  Each column in the spreadsheet corresponds to a field. I was going to have multiple values in a cell but can have them do one value per cell if that is easier. I could also have one column per value list choice if that would be easier.

                  • 6. Re: importing into checkbox field from Excel
                    philmodjunk

                    Does this import only add new records or does it need to update existing records?

                    • 7. Re: importing into checkbox field from Excel
                      denno

                      it only imports new records

                      • 8. Re: importing into checkbox field from Excel
                        philmodjunk

                        Ok, then the fact that you have two tables to receive the imported data is a signficant challenge. Is there any column in this spreadsheet that uniquely identifies each row in it?

                        I'm looking at different approaches here and how we go about importing the data so that they are successfully linked in the relationship you've defined for the database is the biggest issue:

                        Most likely, you will need to import into a temp table and then use a script that moves the data into the two related tables while generarting the needed ID value in your main table so that the checkbox records are linked to the correct record in the main table.

                        Hmmm, this sounds like you are in early stages of development, so I'll ask a question that may lead us to an alternative that is easier to set up: Why is the data being collected into a spreadsheet and then imported into FileMaker? Would one of the following scenarios work for you?

                        1) distribute copies of a runtime database file for data entry and use IMport records to import the data from these FileMaker files instead of the spread sheet.

                        2) Publish a Database to the web with IWP to the web and collect the data there via users filling out a form in their Web Browser.

                        Both options will greatly reduce the steps needed to pull this data into your database.

                        • 9. Re: importing into checkbox field from Excel
                          MaxSio

                               I'm new to Filemaker and have a WuFoo form that asks volunteers about their availability by day of the week. The data is collected using a checkbox field (Mon, Tues, Wed...) When I export that data out of WuFoo I get 7 columns each one listing one of the days a volunteer has checked on the form. How can I enter multiple values into a Filemaker checked box field?