AnsweredAssumed Answered

Matching fields with reocurring Excel import

Question asked by ChadGist on Jan 24, 2014
Latest reply on Jan 24, 2014 by ChadGist

Like • Show 0 Likes0
Comment • 5

Title

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!

philmodjunk Jan 24, 2014 3:34 PM

Correct Answer

     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.

See the reply in context

No one else had this question

Outcomes

philmodjunk Jan 24, 2014 1:57 PM

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.

Actions

davidanders Jan 24, 2014 2:12 PM

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?

Actions

ChadGist Jan 24, 2014 2:29 PM

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?

Actions

philmodjunk Jan 24, 2014 3:34 PM

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

Actions

ChadGist Jan 24, 2014 4:06 PM

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

Actions

5 Replies

philmodjunk Jan 24, 2014 1:57 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
davidanders Jan 24, 2014 2:12 PM
Mark Correct
Correct Answer
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?
Like • Show 0 Likes0
Actions
ChadGist Jan 24, 2014 2:29 PM
Mark Correct
Correct Answer
     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?
Like • Show 0 Likes0
Actions
philmodjunk Jan 24, 2014 3:34 PM
Unmark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions
ChadGist Jan 24, 2014 4:06 PM
Mark Correct
Correct Answer
YES, thank you Phil! That was all I needed, works great.
Like • Show 0 Likes0
Actions

Matching fields with reocurring Excel import

Title

Post

Outcomes

Related Content

Recommended Content