Solved

Import excel file in to multiple filemaker pro tables

Posted on 2012-03-27
4
1,072 Views
Last Modified: 2012-03-31
I need to import excel files in to a Filemaker 10 database.

How do I go about importing the excel file so the data relates to the proper tables?

the database has numerous tables for managing an art collection.
A table for the artwork, another for artist info, another for history, another for images and so on...

I am okay doing multiple imports linking the fields in the excel file imported to the appropriate tables, but what is the best way to do this without extensive scripting and to ensure that the data from the excel file will relate properly to the existing data?

Would it be best to create a temporary database and then merge the two together?
0
Comment
Question by:madcat2020
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 37773360
As long as the first row of your spreadsheet contains the field names exactly matching the fields you wish to import them into in FileMaker, you can use the Arrange By: "Matching Name" import option. When importing FileMaker to FileMaker the "Matching Names" option is always available, but when importing from Excel or other sources, you must first tick the checkbox that says: "Don't Import first record (contains field names)"

Import matching on first row
You can script some or all of the multiple imports most easily by being consistent about naming conventions, both the fields but also the spreadsheets. If you consistently name the spreadsheets the same name and put them in the same location - for example a folder name "File to Import", then you can setup a script with multiple import into different tables.

You can actually go further if you want and not just import but also "Update" existing records with new data as long as you have at least one field to match on between the FM Database and the spreadsheet. If you use the "Update" option, keep in mind that the "Matching Names" option no longer is available because you have to specify one specific field to match on. As long as you don't delete fields in FM or Columns in your spreadsheet you'll be fine with the "Last Order" Arrange option.
0
 

Author Comment

by:madcat2020
ID: 37773840
Thank your for the quick response.
This makes sense so far.
But it is only solving part of my issue.

When importing, I can send data from my Excel file in to different tables.
The problem is nothing is related.

So in my example, I import data in to the artwork table, creating/updating records and then import the data about each artist in to the artist table creating/updating records, repeated for each table for the financial info and so forth. But nothing seems to be connected or related. So the database cannot tell what artwork belongs to which artists or what financial info goes to which artwork and so on.


In the existing database structure there are auto generated key fields and serial#'s that relate everything and all the records seems to be relating to each other fine. I can modify or add new records through filemaker no problems, except when importing from external sources.
These key fields in general do not exist in the Excel data because it was created outside the system.

I guess my more specific question is how do I get the imported data to relate to the existing tables? Do I need to generate or add key fields in to the Excel document or do i need to add, modify existing key fields in the database?

Thank you for your help.
0
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 500 total points
ID: 37774220
You last statement is exactly correct. In order to Update data in your existing records via Import, your Excel documents must have a key field - usually a unique serial number - for each record in FM that you are trying to update. So for the artworks, one of the columns must be the actual serial number of the artwork record to be updated and similarly for artists, it must include the serial number or unique key field for each artist.

When you perform the import, you select "Update Matching records in found set" and then set at least one key matching field in the field matching dialog by clicking on the operator between them so that you have a double-headed arrow pointing between the two fields, the serial number or key field in FM and the matching column in Excel.

How you get the key fields into your Excel document depends on how the changes in Excel are being created and edited. Sometimes people will do an Excel export from FM of specific records and include the unique identifier. They then edit the spreadsheet and re-import.

By the way, if you doing University level image management, I've actually worked on and helped revise a system called Snapdragon for doing just that.
0
 

Author Closing Comment

by:madcat2020
ID: 37792470
This solved most of my issues I was having I still need to tweak some of my tables to work correctly but the advice gave me enough to started. Thanks!<br /><br />I wish Filemaker itself had an easier process for importing into multiple tables from an external source. To me this seems like it would be a common thing people do. Its way to complicated and too many steps no matter how i do it now.
0

Featured Post

Industry Leaders: 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!

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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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