Go to a layout based on the portal's table (the phone numbers)
Now import name and number there.
I then recommend a follow on script that enables you to update the imported data to link them by an ID number instead of a name. Names are not unique, people change their names and names have very idiosyncratic spelling which can make data entry errors very likely. Linking by an ID number thus avoids many issues. (and you may want your follow on script to check for duplicate names or names that do not match to any record in your contacts table.)
You need to have one record per phone number in your contacts file, so John will be listed on 10 records, each with a different number. Absolutely not recommended.
You need two tables: a contacts table that holds John's record once, and a Phone Numbers table that holds the 10 records belonging to John. You would then create a relationship with the ContactIDbwteen the two table. You could then show all John's numbers in a portal.
I would also strongly recommend you create a Contact ID, a unique FM-controlled serial numkber (or use UUID in FM12) to control the matching - not names.
I would say the import only imports one record for John because the name field is set to valdate as unique only.
Using the matching fields import with Name as the match field is what prevents the import of more than one number for a given name. Each succeeding record with the same name overwrites the data in the existing record rather than adding a new record.
I created a table for the phone numbers. I linked the 2 tables via a contactID (auto enter serial) field from the Contacts table to a number field in the phone number table. I imported the excel sheet with the phone numbers. I created a portal using the phone numbers table. It isn't displaying the information. I am also stuck on how to create the follow on script to link them via ID number.
Thanks for getting me this far guys!
The auto-serial number is for the ContactID in the Contacts table, and the PhoneID in the phone table. Note it is not for the ContactID in the Phone Table.
I assume you mean that the phone numbers don't display in the portal. In the Phone table manually enter a valid ContactID in the ContactID field. Go back to the Contact Table and find that Contact record. Does it show the phone numbers now?
The import you describe will not add the needed contact ID to your new phone numbers records and thus they are not linked to any contact record. This is why I suggested using a script to process the data and link them by ID after they are imported. This would match them by name--the only info you identified in your excel file that can be used to match the numbers to your contacts in order to get the ID number from contacts and then enter this value into the phone number record to link it to the correct contact.
I think I missed something here. By the way, I used the phone numbers example, just for explanation purposes, so we will stick with this scenario to avoid confsion, but my tables may not make perfect sense. Please see the 2 attached pics. Thanks
The relationship is correct for the point where you have correct values entered into the contactID MatchField of your Phone table. But as I have already noted, your import records action is loading the Phone table with phone numbers, but not the needed ID number to link it to a contact record.
In Manage | Database | relationships, make a new table occurrence of Contacts by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as ContactsByName.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
ContactsByName::FullName = Phone::FullName
After importing you can manually, or by using a script, use Replace field contents on the Phone::Contact ID Matching Field to copy the value of ContactsByname::Contact ID Matching Field into this field.
Note that a script can also use Count ( ContactsByname::Contact ID Matching Field ) to see how many contact records match by name to your newly imported phone record. If there are zero matching records, then you either have a misspelled name or it's for a new contact not yet entered in your contact table. If there are 2 or more, then you have more than one contact record with this same name.
Got it! Thanks for all the help. ONly issue I have now is that the data I am importing is inconsistent and not all names are matching up. You mentioned this may happen, but I am uncertain how to go about setting this up so that spelling of names would not matter.
This can be a very, very sticky issue. Even if you review the names manually record by record, it may not be possible in every case to correct the issue.
Add this calculation field to the Phone table:
Count ( ContactsByname::Contact ID Matching Field )
- Put this field on your Phone layout.
- Enter find mode.
- Put a 1 in this field
- Select the omit button
- Perform the find.
The resulting found set will be for all records that do not perfectly match to a single record in contacts. You can then review the data to see if a name was misspelled, is a duplicate name or a new contact that does not yet have a contact record in the contacts table.