3 Replies Latest reply on Jun 2, 2015 11:43 AM by philmodjunk

    Multiple Checkbox Selections export with FMG to excel and import into FMP

    TravisHeckford

      Title

      Multiple Checkbox Selections export with FMG to excel and import into FMP

      Post

      Hi there,

      I have a rookie question

      I use a form created in FMP on my iPad in FMG to collect biological field data. I used checkbox fields to select, if necessary, multiple boxes (options), of values derived from a value list. In the value list, values are listed vertically with a space at the end for easy reading in excel. When I export data from FMG to excel I can read the checkbox selection (easily because of the space) as a horizontal list structure but also if I double click the cell it will list the values vertically. When I import these records the checkbox fields do not display the values selected, instead, they display only 1 of the values selected or none. 

      All the values are there, I can tell this by copying the field and setting it to an edit box where it will display all the values selected. However I want to see these values populate the checkboxes on the form. I suspect this is happening due to the order in which the values were entered, is it possible to get around this? and if not, is it possible to have the values be displayed as a linear (horizontal) structure in the edit box (ie it displays as a vertical list)? If I can get it to be displayed as a vertical list, it might be better for import into ArcGIS.

      Thank you in advance for your help.

        • 1. Re: Multiple Checkbox Selections export with FMG to excel and import into FMP
          philmodjunk

          Those check box values are separated by a return character in FileMaker so I don't quite see the need for a space as well. I would expect this data to import back into filemaker just fine, but why import it from Excel when it's already in an FMP file on your iPad? why not import from the orginal Filemaker File into this second FileMaker file? why use the excel file as an intermediate file? (My guess is that the Return character has a different character such as a vertical tab substituted at some point in the export to or import from Excel.)

          If you want to get the values listed horizontally, the following calculation can produce that list:

          Substitute ( checkBoxFieldHere ; ¶ ; ", " )

          This replaces the return characters with a comma and space. You can select other text as the replacement text by putting different characters between the quotes.

          • 2. Re: Multiple Checkbox Selections export with FMG to excel and import into FMP
            TravisHeckford

            So in the FMP database the checkboxes in the table and the form are empty and look like the attached photo. However if I copy and paste the field and change it to and edit box I can see all the values selected - so the data is there but not displaying correctly. Perhaps there is a difference in the Return Character in excel to FMP but nonetheless the data is still present and it should in theory display correctly. 

            Unfortunately Filemaker Go does not export to a FMP file format... why is this?

            If all else fails to have this data display correctly, what I will do it create a new field, set to edit box and calculate the horizontal display of the values, its a round about way of having it work, but ideally I would like to solve this problem for future use.

            I have read that the order of the values entered in the checkboxes may have something to do with the incorrect displaying the the values in the table view. As well I would like to not see the unchecked boxes, is this possible? I only want to see what was selected in the field.

             

            • 3. Re: Multiple Checkbox Selections export with FMG to excel and import into FMP
              philmodjunk

              Unfortunately Filemaker Go does not export to a FMP file format... why is this?

              You don't need to. You can save a copy and email it or you can transfer a copy of the file via iTunes or Drop Box. Then you have your FMP file for import.

              And Excel should not be the only export option as you should be able to do a tab or csv file export as an option that works also.

              If the data doesn't exactly match the value list, the boxes won't show as selected. A change in characters separating the values would do that. It is also possible to identify the character code of the separating character in order to use a substitute function call to put back the returns.

              But one more possibility comes to mind: Open Manage | Database | Fields and check to be sure that CanopyCloList is a field of type text and not a field of type number.