Solved

Importing Records From Excel FM11

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 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