excel flags to many-to-many relationship

[hpw]

In http://fmforums.com/forum/showtopic.php?tid/179213/post/216866/#216866 David gave me great idea how to organize the interests of my contacts in our service tools.

I have a question now: I have Excel-sheets with contact data where the interest of each contact in our tools is marked. How can I import this information into my Filemaker database?

thanks for your help

hans peter

[Fenton]

From reading your earlier post and its answer I see that you're going for a proper relational solution, with a join table. This presents a bit of a puzzle to get the data from an older "flat" structure, as you've found.

The solution is to first convert your Excel sheets to a FileMaker file. This is fairly easily done; FileMaker can import only named ranges also, if your data is spread around the sheet.

You must have a Contact table, with an auto-entered serial ID.

Once you have the converted file, you need to establish a temporary relational link on the contact name, to your real Contacts table. Use that relationship to populate a foreign Contact ID field in the temporary file. Because that's what you need, the ID.

This would be done, in the temporary file's ContactID field, with the Replace Contents step (in the Records menu). Replace with calculation;

Name relationship::ContactID

Show All Records first. Have a backup. Because there is NO Undo.

Do the same process with the tools (which should have their real table somewhere, with a record for each, with an ID.

Now the temporary file has both a Contact ID and a Tool ID. These can then be imported into the "join" table, along with any other relevant data (such as date). Do not import the "names" of the contacts and tools however, only the IDs. That's what relational databases are all about; maintaining links while minimizing redundant data.

[hpw]

thanks fenton

seems to be quite complicated..

would you have an easier idea to solve the problem of flagging the interest in products? I tried it with a simple checkbox list but in iwp checkboxes can't be properly controlled.

hans peter

[T-Square]

Hans--

I don't know what problem you're having with checkboxes in IWP; they've always worked for me. What can't you control?

When I use checkboxes, I typically create a checkbox on my form that uses the field as its data source, but a single-entry value list with the number 1 in it as the value list source. The checkbox then behaves as a simple Boolean toggle. I arrange the checkbox so that the value ("1", which is essentially meaningless to the user) does not show, and add whatever text label I want separately.

If your User/Potential User field is only the two options, you could set this field up as a Boolean (Yes/No) field, with a default to whichever term you want.

HTH,

David