8 Replies Latest reply on Jan 22, 2016 10:52 AM by disabled_JackRodgers

    Import from Excel to a Value List

    hanabi

      When I import Excel files into my database, the value list fields (drop-down menus) in the records remain empty after import, even when the values from the Excel fields are exact matches of the words in the value lists.

       

      To be clear, the fields in the Excel docs are not value lists – just simple text fields.

       

      How can I get my Excel fields to import and complete the drop-down menus in my records?

        • 1. Re: Import from Excel to a Value List
          siplus

          Defining some value lists could definitively bring you some added value.

           

          File -> Manage -> Value Lists.

          • 2. Re: Import from Excel to a Value List
            beverly

            What do you see if you put the field on the layout and do not format as Drop-down list? What if you format as Pop-up menu? or other 'style'?

             

            Could the values imported possibly have space-padding and "appear" to be the same, but really have invisible characters (such as space or other)?

             

            If you put the field on the layout with no formatting and then click in the field (to select all) you may then see if additional characters may be preventing the "match". I've seen this with a return after the value imported.

             

            beverly

            • 3. Re: Import from Excel to a Value List
              hanabi

              The only formatting that accepts imported values is an edit field. However, for sorting and reporting purposes, there can only be three options in that field for users to choose. It looks like I will just have to import my records and manually change that field.

              • 4. Re: Import from Excel to a Value List
                beverly

                I understand, I'm trying to help you troubleshoot. The tips were for that end and not for actual entry (or final display). If your import does not appear to be "matching" the value list items, then let's try and find out why.

                beverly

                • 5. Re: Import from Excel to a Value List

                  One reason that your popups might be empty is that you haven't created a proper value list, are using the wrong field, etc.

                   

                  To make a minor correction, you import into record fields and not edit fields on a layout... However, if you are already importing then that detail is irrelevant.

                   

                  Value lists are not a part of a record field or an edit field on a layout. They are created in the File:Manage:Value List... editor.

                   

                  The value list can be filled in manually or pointed at the contents of a field in a table. To start you should point it at the field you are importing just to check what the values are. You may need to clean up a few records.

                   

                  After you assign the field for your value list you then select a field on a layout (you'll have to do this for every layout with that field) and then use the Inspector and the appropriate selection to assign that value list to the field. You can then format it as a popup, checkbox or radio buttons or even a calendar if it is a date field.

                   

                  If you follow these steps, then whatever data is being imported will show up as an option in the popup menu and you can clean that up.

                  • 6. Re: Import from Excel to a Value List

                    If you are trying to use a value list for sorting or then create a global field and assign the popup to it and then script the sort. In find mode, the popup will fill in the value of the field.

                     

                    If

                    $_variable = xxx, perform sort x

                    $_variable = yyy, perform sort y

                     

                    If you are scripting a find you must use a global and then set a variable to the global and use the variable in your scripted find.

                     

                    Perform find(field; $variable)  Perform Find won't accept a field or global field.

                    • 7. Re: Import from Excel to a Value List
                      beverly

                      I will try one more time.

                       

                      * you have a field into which you import from another source.

                      * you already a value list with pre-defined values (sample:)

                           red

                           blue

                           green

                      * the field on the layout is _formatted_ as drop-down  to use the value list.

                      * if the external file from which you import has this value:

                           red       <-- there is a TAB & a SPACE character here

                      then the import  of this record will NOT show the value in the field that has been formatted to show from a value list.

                       

                      * one way to verify the import is to place the same field on the layout and do not format to show from any value list; click INTO the field and use the arrow key to step through the characters. If the cursor goes beyond the imported word (or phrase that might match) then you have invisible characters that would prevent the imported value to match your value list. - remove the field after testing

                       

                      Have you verified the values that are imported?

                       

                      * another way to verify is to set up validation on the field to ONLY allow values from the value list. You will get an error report after the import if any of the values do not match _exactly_. These records will have blanks because the validation failed for that field.

                       

                      A tip for frequent imports that might have space-padded values:

                      Import into temporary table and Trim() the values.

                       

                      beverly

                      1 of 1 people found this helpful
                      • 8. Re: Import from Excel to a Value List

                        Beverly, you are correct...importing from Excel can be a nightmare.

                         

                        I would recommend, in fact I did, creating a value list based on the values

                        in a field (this can be just for checking the import) and then using that

                        value list to find the unwanted values and replace them.

                         

                        One other idea, open the excel file with Filemaker rather than importing.

                        This will create a one table file and you can see the unfiltered data. and

                        take steps to clean it.

                         

                        After the table is 100% FileMaker Pure you can import that file into your

                        base file's table.

                         

                        Another point, do a find, with the cursor in a field type Command+I or

                        Control+I and FileMaker will present a list of every word in the table or

                        every phrase. I found 12 unique spellings for guitar in a client's file. 12

                        searches and replaces and clean...

                        1 of 1 people found this helpful