3 Replies Latest reply on Apr 7, 2015 1:55 PM by nilsw

    when there are multiple external data source options for the host database...

    TomPingel

      Title

      when there are multiple external data source options for the host database...

      Post

      I have a client solution that could be run in any number of different 'host' environments.

      In the client, I have defined the possible hosts as 'External Data Sources'.

      The client has a series of 'import record' script steps to get data from the host to initialize the client each day. If I repeat the list of references in 'External Data Sources' when specifying the file path for the import, all is good. But if and when (as is certain to happen) I need to add another to the list, I would like to simplify the maintenance and do it in as few places as possible - not in every import step - to minimize opportunities for error.

      I thought if I updated the External Data Sources, copied the list,  and had a variable, say $$host, set to the list of sources, it might work. $$host would essentially be a 'Value list' and would the single 'file path' for the import records script step. I would only need to make 2 script step updates for this.

      The single $$host file path entry doesn't appear to work. Should it? The attached shows a list of 4 'external data sources' for the host and what I had hoped would be sufficient for the file path specification. $$host was set to the list of the four sources.

      Is there another way to make the addition of another external data source  in only one or two places when there are a number of import record script steps to be affected?

       

      In a related question, is there a way to know which of the files in the import records script statement the system got a 'hit' on and was able to use?

       

      Slide1.jpg

        • 1. Re: when there are multiple external data source options for the host database...
          TSGal

          Tom Pingel:

          Thank you for your post.

          The variable should work.  This is what I have done:

          1. I created a database file, "NAME.fmp12", with two fields:
               ID (Number)
               Name (Text)

          2. I entered one record with the following values:
               ID = 1
               Name = TSGal

          3. I created another database file, "Test.fmp12", with the same fields with no data (0 records).

          4. I created a script as follows:
               Set Variable [ $$hostFile ; "file:TEST.fmp12" ]
               Import Records [ "file:TEST.fmp12" ; Add; Mac Roman ]

          5. I run the script to make sure the data is imported.

          6. I then change the script to read as follows:
               Set Variable [ $$hostFile ; "file:TEST.fmp12" ]
               Import Records [ "$$hostFile" ; Add; Mac Roman ]

          7. I run the script to make sure the substitution of the variable works.

          8. I change the script again:
               Set Variable [ $$hostFile ; "file:ABCDE.fmp12" & "¶" & "file:FGHIJ.fmp12" & "¶" & "file:TEST.fmp12" ]
               Import Records [ "$$hostFile" ; Add; Mac Roman ]

          9. I run the script, and ABCDE.fmp12 and FGHIJ.fmp12 don't exist, so I'm prompted for the fields form TEST.fmp12.

          Since this is not working for you, do you get an error message?  What is the exact contents of $$hostFile?

          At this time, there is not a function to let you know which file was imported in a multiple line Import Records script step.  You would need to script this into four separate Import steps and when Get (LastError) = 0, you will know which file was imported, but this defeats the purpose of having a variable referencing multiple files.

          TSGal
          FileMaker, Inc.

          • 2. Re: when there are multiple external data source options for the host database...
            TomPingel

            I copied the list from the 'external data source' listing and pasted it into the 'value' for the $$variable. Apparently the format isn't the same as forming the list as you did in your example. I'll try it 'your way'.  Thanks for taking the time to develop the test.

            I'll use the multiple-line import script step, checking for errors. With multiple imports and multiple possible files, it takes a while for each import step to process a non-existent file. Doing it once and using the successful find for subsequent imports will be more efficient. I was hoping for the function but can live with this solution.

            Thanks again.

            • 3. Re: when there are multiple external data source options for the host database...
              nilsw

              Hi Tom,

              just add a table occurrence and a layout of the table you want to import from to the database file you want to import into and change your script so the database actually imports from itself by setting $hostFile = "file:" & Get( FileName ). As the table occurrence uses the external file reference the remote table will always be the one in the matching file and imports are save - even if you rename the target database.

              Nils