Solved

Importing Records From Excel FM11

Posted on 2013-12-03
3
436 Views
Last Modified: 2013-12-04
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
Comment
Question by:PachecoPrimo
  • 2
3 Comments
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 500 total points
ID: 39695728
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
 

Author Comment

by:PachecoPrimo
ID: 39696393
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
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 39696552
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question