Import from a single Excel file into many FM tables?

[gbatch]

I'm a beginner at FM and am learning how powerful this program can be. I'm trying to develop a relational FM contact application that, when complete, will replace a now existing Excel flat file. All of the data I need to import is in a single Excel file. I've elected to place some of the data (Activities) into a separate table, while the bulk of the contact info is in a Contact's table. I've created a relationship and portal so that the Activity info can be inputted and viewed in the Contact layout.

My dilemma is how to import the Contact info into the Contact table and then to import the Activity data into the Activity table. If this isn't clear, let me know and I'll try to explain it more thoroughly.

[Matthew F]

I'm not sure where the difficulty lies. FM allows you to import data from Excel files.

From the menu bar select 'File > Import Records > File ...') to choose the excel file to import from. The import wizard will then ask you to specify the sheet (if your excel file has multiple sheets). You will then be prompted to identify the columns of data to import.

Do this to choose the correct data to import into the 'contacts' table. Then repeat the process for the relevant fields for the 'activity' table (you specify the table at the same time that you are specifying target fields).

[Fenton]

You can also define a Range within the Excel file if you just want to import 1 block of data at a time from the same worksheet. As mfero mentioned, you don't say whether you have multiple sheets in your Excel file.

The main problem is that when you import the Activities you must include a field that can be used to link each to its Contact.

[gbatch]

Aha - you guys have ID'd my problem. First of all, all of my data to import is in one flat Excel file approx 3,000 rows/records. In Excel, each of the 14 Activities is a column and for each Contact entry, each activity has either yes or no entered.

In FM, I've set up the Activity table with its own fields - Activity and Y/N. This table is linked to the Contact table. In my design stage, during data entry, I set up a portal between the Contact layout/table and the Activity layout/table. In the portal, as I enter data, I choose y/n for each activity. Importing this data is where I am stuck.

I can see how to import the pure contact data fields (name, address etc.) but am not clear on how to 'link' the activity data in Excel to the Contact data already imported into FM.

[Fenton]

Quick Fix:

Add a column to the Excel file, at the far left.

Add another column to the Excel file, next to the Activities columns.

In both, Fill Down, with serial numbers:

1

2

3

etc..

Define the "contact" columns as one named Range; include the serial# column. Define the "activities" columns as a named Range, including its serial# column.

Import the contacts range into one FileMaker table, the activities range into another.

Assuming each row is a Contact, you will now have a serial# field to tie them together.

The serial# should be defined as a auto-enter by serial number field in FileMaker. Either have it update as you import, or update it manually afterwards; check it in either case. It is NOT an auto-enter field in Activities (in case you're tempted :-).

[gbatch]

Fenton:

You're a pure genius. What you suggested works, as I'm certain you knew, else you wouldn't have suggested it. I can use it but was thinking about another option, slightly more elegant, I think.

Another challenge.

Assume I have the Activity table set up with a key field and a single Activity field. There are 14 activities and in the portal, I want to be able to have the activities show up as a drop-down or pop-up list, and then beside each activity I can choose 'yes' or 'no'. As I create these entries for a contact, I am populating the Activity table with a separate record for each activity contact. Each entry will be linked to the contact via the key.

How would I get the Activities imported from Excel so they are NOT each a separate field in the Activity table, but a value list AND they also import the appropriate yes or no for each contact?