11 Replies Latest reply on Sep 19, 2012 1:30 PM by philmodjunk

    Importing excel sheet with multiple fields per contact

    Epa

      Title

      Importing excel sheet with multiple fields per contact

      Post

           I am trying to import an excel sheet into my contacts databse.  I'm using my contacts full name as the match field and I am trying to import 1 cell of data to match a feild in my databse.  The problem is: The excel sheet will list my contact (match field), let's say, 10 times with different data (the 1 cell I am importing).  Upon completion of the import, I am only getting 1 cell imported per contact, and the rest of the entries on the excel sheet are ignored.  I think I need to set up a portal so that multiple fields can be created based on the same criteria.  If I do this, I am unable to import into the portal (not an option on the import screen).  Am I going about this all wrong? Thanks

            

           For example: John Doe appears on the excel sheet 10 times in column A.  Column B lists a phone number.  Therefore John Doe has 10 different phone numbers, because he is on the excel sheet 10 times and column B is different each time.  I want my database to recognize John Doe ONCE and import the 10 phone numbers to his record in the databse.

        • 1. Re: Importing excel sheet with multiple fields per contact
          philmodjunk

               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.)

          • 2. Re: Importing excel sheet with multiple fields per contact
            Sorbsbuster

                 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.

                 Or:

                 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.

            • 3. Re: Importing excel sheet with multiple fields per contact
              philmodjunk

                   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.

              • 4. Re: Importing excel sheet with multiple fields per contact
                Epa

                     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!

                • 5. Re: Importing excel sheet with multiple fields per contact
                  Sorbsbuster

                       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?

                  • 6. Re: Importing excel sheet with multiple fields per contact
                    philmodjunk

                         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.

                    • 7. Re: Importing excel sheet with multiple fields per contact
                      Epa

                           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

                      • 8. Re: Importing excel sheet with multiple fields per contact
                        Epa
                        /files/3310067233/Manage_Database_for_%E2%80%9CSCORE.CRM%E2%80%9D-2.jpg 365x371
                        • 9. Re: Importing excel sheet with multiple fields per contact
                          philmodjunk

                               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.

                          • 10. Re: Importing excel sheet with multiple fields per contact
                            Epa

                                 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.

                            • 11. Re: Importing excel sheet with multiple fields per contact
                              philmodjunk

                                   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 )

                                     
                              1.           Put this field on your Phone layout.
                              2.      
                              3.           Enter find mode.
                              4.      
                              5.           Put a 1 in this field
                              6.      
                              7.           Select the omit button
                              8.      
                              9.           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.