Solved

Importing Records From Excel FM11

Posted on 2013-12-03
3
455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

615 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