Solved

Importing Records From Excel FM11

Posted on 2013-12-03
3
428 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 24

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 24

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now