9 Replies Latest reply on May 21, 2015 7:18 AM by philmodjunk

    Import into fkey based value list field

    RockSure

      Title

      Import into fkey based value list field

      Post

      FMPAdvanced 13

      I have a excel table that needs to imported into a table with fields that include city, county, state. 
      In the excel file the state value is  TX in my table fkey value its 12. as it the case for city and county different fkey value of course. . 
      Is there a work around for this outside of a find replace in excel prior to import. 
       

        • 1. Re: Import into fkey based value list field
          philmodjunk

          You could import the data into different fields of the same table set up to hold imported names and then set up relationships that match by the name values solely to use for a replace or data lookup on this data within FileMaker to get the correct ID's into the proper fields.

          • 2. Re: Import into fkey based value list field
            RockSure

            I think I got this working OK and have moved on to setting up the import script step.

            1.  I am using a 3rd party hosting service for the server. Will I still be able to set up a import script?

            2. Can I have the script to prompt the user for the file name but use the mapped fields of the script, when I do not specify a file it seems it has noting to map to. This is to accommodate changing file names with new data like orders051515 and orders052015. Same data field maps but different data from different weeks. 

             

            • 3. Re: Import into fkey based value list field
              philmodjunk

              1. yes and there is more than one way to do so, including downloading the file to your computer, doing the import and then replacing the hosted copy with the new. But you can also set up the script and run it right in the hosted file also.

              2. This is possible, but not as simple as you might like as it requires using a container field and a $path variable. Here's a rough outline:

              Put a container field on a layout. This can be a global field. Your script goes to this layout and then uses Insert File with the "store a reference" option enabled to insert a file into the container field. The user sees a dialog where they can find and "open" the file from which they want to import the data. The script then extracts the file path to the file from this container field and uses it with Import Records to import the data.

              For more on $Path variables, Import Records and Container fields, see: Exploring the use of a $Path Variable in Scripts

               

              • 4. Re: Import into fkey based value list field
                RockSure

                So this will be different when hosted then when running the local copy. The reason I ask is when I omitted the file check box in the script step and then still mapped the fields in the order to blank import file fields, then when I ran the script from a button on my form it did prompt me for a file name and then I selected that and it then did map as I had set the order in the script and all came in OK. I am sure its not that easy is it?

                • 5. Re: Import into fkey based value list field
                  philmodjunk

                  No it will not be different. But you may want to take the file offline to do this or do it during times when DB usage is low.

                  The scripted method I described was so that the user only selects the file and is not presented with the field mapping dialog where they might make a change or select an option that they should not select.

                  • 6. Re: Import into fkey based value list field
                    RockSure

                    I don't know anyone that would ever make changes inadvertently, they follow directions don't they. smiley especially if I put in bold letters, in red, large font so that should not be an issue. 

                    What I cant get done is anyone to replace the toilet paper when its out.  Maybe I should work on an import script for that. 
                    Maybe if I put it in bold letters red and large font they would do it but I don't think I will count on it. 

                    Now being serious that makes sense thanks I will give it a try. 

                    • 7. Re: Import into fkey based value list field
                      RockSure

                      The imported text values of my cites are all caps my value table is only 1st letter cap. When I do the match and lookup it wont match is there a workaround for this?  

                      • 8. Re: Import into fkey based value list field
                        RockSure

                        Had nothing to do with caps,  I had a trailing blank space after every city name in the my Value List table.

                        • 9. Re: Import into fkey based value list field
                          philmodjunk

                          I know that you are joking, but:

                          Users will do the darndest things with your DB solution and getting a series of dialogs where they are supposed to only click OK makes even the careful ones nervous.