• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

Importing Records From Excel FM11

I have a script that imports records from an excel spreadsheet. I do not want the user to have to go through the mapping of the fields every time they want to import records. Is there something that I can do to ensure the scripts line up correctly, and that no headings are imported.
Any ideas will be greatly appreciated.
0
PachecoPrimo
Asked:
PachecoPrimo
  • 2
2 Solutions
 
Will LovingPresidentCommented:
Set the first row of the spreadsheet to have the field names exactly as they are in FileMaker. To setup the "Import Records" script step, select a test file for importing and  in the "Import Field Mapping" dialog set the correct table on the right for importing into.

In this dialog you'll notice that the default "Arrange by:" setting is "last order" and that "Matching Names" is grayed out. Click the checkbox that says "Don't import first record (contains field names)" and "Matching Names" become available as an option under the "Arrange By:" popup menu.

With these settings, the data should import into the correct fields as long as the header row has the correctly spelled field names, matching the fields already in FileMaker.
Import Dialog
0
 
PachecoPrimoAuthor Commented:
Hi willmcn,
If I check the "Don't import first record" checkbox on my script, will it stay that way forever?
Also, I am concerned about someone my try to import a different file (different fields or out of order) than the one expected, is there anything that I can do to prevent that from happening?
I don't want user to have to map things out manually, but at the same time I want to prevent mistakes.

Thanks!
0
 
Will LovingPresidentCommented:
The "Don't Import first record (contains field names)" along with "Matching Names" will stay set if you set them in the script step and then save the script, which normally happens when you close the script up if you haven't already hit Control/Command-S. If the script step is set to matching fields then it will only import those fields that have matching names.

You can choose to let the user see the Import Matching dialog or not depending on whether you check "Perform without Dialog". There is no easy way that I know of (though I can think of some harder ways that use plugins) to verify that the spreadsheet field names are correct before import. Showing the Matching dialog will at least allow the user to see whether the fields are lining up but it also allows them to change settingsā€¦so it's up to you with regard to what works best.

In general, when a regular import routine is required, the developer can test that the name of the selected file matches a preset standard. This requires a plugin like the Troi File plugin to allow the user to select the name of the file they want to use and then test it's name against the required name before importing. You could also do something like import the data into a separate Import table and then run tests on it (via script) after the initial import before then importing it (via script) into the actual table.

Mostly it usually comes down to educating users that they must always start with a supplied Excel template - one that already has the correct field names - and then making sure they understand to never edit the first row with the field names (aka the "header" row).
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now