5 Replies Latest reply on Jan 24, 2014 4:06 PM by ChadGist

    Matching fields with reocurring Excel import

    ChadGist
    ChadGist Jan 24, 2014 1:38 PM

      Title

      Matching fields with reocurring Excel import

      Post

           We have a single Excel file we are going to re-import on a daily basis into FMP. Prior to each import our accounting software will be updating the Excel file with new data. Is there a way to streamline the import process so we don't have to remap and select the match fields every time.

           I'm not sure if setting up a reoccuring import would solve this, but Filemaker documentation says that reoccurring import data can not be edited in Filemaker pro. We need to routinely edit this data in Filemaker pro, as we are using FMP to track progress of records in a manufacturing environment.

           Thanks for any suggestions!

      • 149 Views
      • Tags:
        • 1. Re: Matching fields with reocurring Excel import
          philmodjunk
          philmodjunk Jan 24, 2014 1:57 PM (in response to ChadGist)

               IF you edit the data, the next import will overwrite the changes that you have made. That would seem a potential issue no matter how you set up your recurring import unless your import always adds new records to those already present in the table. (Recurring imports delete all records before deleting new data..)

               A script can automate the importing and the field mapping that you set up will be preserved in the import records script step. The only case where this does not happen is if you use a $Path variable to the file and do not include a second data source reference after the first that is valid at the time you define this script step.

          • 2. Re: Matching fields with reocurring Excel import
            davidanders
            davidanders Jan 24, 2014 2:12 PM (in response to ChadGist)

            http://www.youtube.com/watch?v=zmYLh72di0A
                 Day 07 - Recurring Import in FileMaker - MightyData

                  

            http://filemaker.com/11help/html/import_export.16.15.html
            Home > Sharing data > Saving, importing, and exporting data > Importing data into FileMaker Pro > Setting up recurring imports

            http://help.filemaker.com/app/answers/detail/a_id/7532
            Setting up recurring imports in FileMaker Pro
                 How do I set up a recurring import in FileMaker Pro?

            • 3. Re: Matching fields with reocurring Excel import
              ChadGist
              ChadGist Jan 24, 2014 2:29 PM (in response to ChadGist)

                   Thanks for the quick reply. The fields we are changing in Filemaker do not exist in the Excel spreadsheet. For instance we get information about orders from Excel file like Order date, order #, customer name, Due date; we edit additional fields in filemaker related to manufacturing progress that is input with Filemaker Go, etc.

                   Please see the attached screenshot. This is what my field mapping looks like after rearranging the order every time. Notice that I am using two match fields. The settings shown work absolutely perfect for me, but it is troublesome to have to rearrange to this order every day (or more), and leaves too much room for human error.

                   Do you have a recommendation for a script I could refer to do automate this function?

              • 4. Re: Matching fields with reocurring Excel import
                philmodjunk
                philmodjunk Jan 24, 2014 3:34 PM (in response to ChadGist)

                     Go to layout ["WIP List" (WIP List) ]
                     Show All Records
                     Import Records

                     May be all that you need. When you add Import records there are buttons in the lower right corner of the script editor for setting up your import. One dialog is for selecting the file that you want to import. As long as the file is always named the same and placed in the same folder before you run this script, you can click "Add file" and add a reference to the XLSX file. The otherbutton opens exactly the same dialog that you have posted here and you complete it the same way.

                     Note: It IS possible to set up the script such that a dialog box opens asking the user to find and select the file to import, but I'd get the above script working first as that method requires using a number of additional script steps to script the file selection process while not losing the column to field mapping that you've specified.

                • 5. Re: Matching fields with reocurring Excel import
                  ChadGist
                  ChadGist Jan 24, 2014 4:06 PM (in response to ChadGist)

                       YES, thank you Phil! That was all I needed, works great.