Excel File into Multiple Tables and Setting Master Address

[Jake Sargent]

Hello!

Thanks for the help last week structuring my file, I'm now importing data (about 5,000 contacts from an Excel file) and have run into a couple complications, mainly relating to the fact that I can't figure out how to import one excel file into multiple tables. I'll start from scratch explaining the setup, bear with me here!

I'm new to FM and have been building on FileMaker's Contact Management Starter Solution to suit my company's needs. Among extra fields here and there, the main two features I've added are:

CODES

We will be giving each contact multiple codes according to their field of work (for example entertainment, media, art, etc.). I've created a Codes table and linked it via a many-to-many relationship to K_ID_Contact, the codes assignments are stored in a join table called Code Assignments. There is a portal on the main contact layout to enter codes. Most of the contacts I will be importing are not already coded, there are a few hundred exceptions that have one code. In the Excel file I need to import, there are columns for "Code 1," Code 2," Code 3," etc. - how can I import these into the Code Assignments table?

MASTER ADDRESS

I may have overcomplicated this one... On the contact layout, the user can enter a primary home address, primary business address, secondary home address, and secondary business address. In addition to these addresses, I need users to be able to select a Master address that will be used for the address label reports, etc. So I created a related table called Master Address and inserted a portal on the Contacts Layout where people can either select the primary home address or primary business address as the Master address (through the Master_BizHome field). The Master_Address, Master_City, etc fields are set as calculation fields so they auto-populate based on the following example:

Case ( Master_BizHome = Business Primary ; Contact Management::Work1_City ; Master_BizHome = Home Primary ; Contact Management::Home1_City )

The Excel file I will be importing will also have a column specifying whether the Master address is "Home Primary" or "Business Primary"

OK - so that's the setup! To my surprise, it's all working great (not sure if it's the most efficient way to do this, though) except now I need to import the data from one Excel file and populate the Codes, and Master Address tables as well. Any thoughts on how to do this? I've attached a clone of my database for reference and also an empty Excel file so you can see the various columns that need to be linked to fields/tables.

Also - given the two functions of the Master Address: It needs to be designated in a radio button or field on the Contact Layout and needs to be referenced in the mailing labels, does it need its own table? Maybe I just create a field in the main contacts table and create a script in the label layout that say (if Master Address = Business Primary, use Business Primary address)? Any thoughts?

Can't say thanks enough and what a great resource this forum has been!

Best,

Jake

[Jake Sargent]

OK - just an update here: I got rid of the Master Address table and just created a field in Contact Management called Master Address, with a radio button field in the layout to select "Business Primary" or "Home Primary" (which I have set up as global field as also in a value list). I've also set up a case calculation for the Master Address field to see if Business Primary is populated, and if so make the Master Address "Business Primary," and next to see if Home Primary is populated, and if so make the Master Address "Home Primary." So this should solve the address importing problem as they'll all be going in the same table.

My only challenge now is creating a layout/script for the address labels, that essentially would ask: Is Master_BizorHome = "Business Address"? If so, display fields Business_Address1, Business_City, Business_Zip, etc. Else, if Master_BizorHome = "Home Primary," display Home_Address1, Home_City, Home_Zip, etc. Any pointers here?

Also still have the challenge of importing codes...

Thanks!

[comment]

Wouldn't it be more convenient to have a table of Addresses related by ContactID, and have a selected PrimaryAddressID field (or two) in the Contacts table?

In the Excel file I need to import, there are columns for "Code 1," Code 2," Code 3," etc. - how can I import these into the Code Assignments table?

You need to do three imports, each time importing the contact ID and one of the code columns.