Solved

Import excel file in to multiple filemaker pro tables

Posted on 2012-03-27
4
1,009 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 24

Accepted Solution

by:
Will Loving earned 500 total points
Comment Utility
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
Comment Utility
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 24

Assisted Solution

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 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

10 Experts available now in Live!

Get 1:1 Help Now