11 Replies Latest reply on Aug 26, 2014 9:24 AM by philmodjunk

    Assign Customer number

    TonyTurpin

      Title

      Assign Customer number

      Post

           I have a database with 1 Table. I am finally going to a multi table database. I will have a Contacts Table and then other tables that relate to Contacts by Customer Number. Presently the 1 table has the contacts but each contact may have several records. I want to assign a Customer Number to each customer in the present table before I transfer the data to the new file. Sort of a De-dupper script but that will assign a number to set of records that match each other. Any thoughts?

        • 1. Re: Assign Customer number
          philmodjunk

               And how will you transfer that data? Import Records?

               A field can be defined in your new table that auto-enters a serial number. If you are going to import records to populate this table with contacts data, you can select an option in the Import Records dialogs that enables auto-enter field options and then each record created by the import will get a unique ID in this serial number field.

          • 2. Re: Assign Customer number
            RickWhitelaw

            "but each contact may have several records."

            Do you mean that there are possible duplicates of some of the records?

            • 3. Re: Assign Customer number
              TonyTurpin

                   The new Contacts Table is setup to assign a unique number from now on. But before I move the records to the new File I need the number assigned so that it can match up and relate. For example I have 10 records now for Joe Blows Auto repair. All of his information is in 1 table. Each record presently shows his name , address , payments , types of coverage etc etc. When I transfer I will have just 1 contact record instead of 10 for him. I want to transfer only one record of his contacts information into Contacts Table and ten records into his new Policies Table. But I need to assign the Customer Number in advance so they can find each other in the relationship.

              • 4. Re: Assign Customer number
                TonyTurpin

                     "but each contact may have several records"

                      

                     Not duplicates but a new instance of that account. Like Joe has a record for his Auto Insurance, another for his home insurance and another for his boat insurance. All in one table now.

                      

                • 5. Re: Assign Customer number
                  philmodjunk
                       

                            But I need to assign the Customer Number in advance so they can find each other in the relationship.

                       That's not really the case. But any attempt to automate this process will only be as good as your current data allows it to be.

                       You'll need to look at your current data for a lot of different customers and identify as fully as possible what field or group of fields currently identify each customer with as close to 100% reliability as possible. An email address field is often your best option if you have one. You may have to work from a name field or a group of name fields or you may need to combine a name field with an address field before you can correctly identify each customers.

                       Once you have that figured out, if you can figure it out, (Sometimes the data is just too "messy" to get good results), you can set up a temporary relationship that matches by that field or group of fields to the same field or fields in your new table. You can then do two key steps:

                       a) Set up a "unique values|validate always" field option on your single field, or (if it's a group of fields), a new text field that uses an auto-enter calculation to combine all the values of this group field. Then import your records into the contacts table with the "enable auto-enter field options" option selected so that each newly imported record is assigned an ID number. The validation option will automatically filter out your duplicate values.

                       b) set up a temporary relationship matching records in your original table to the new contacts table using the same field or group of fields as match fields. You can then define a foreign key field in your original table and use Replace field contents with the calculation option to copy over the ID value from your new field into this newly added foreign key field.

                       Once step b is accomplished, you can remove the temporary relationship and set up a relationship linking old and new tables by the ID.

                  • 6. Re: Assign Customer number
                    TonyTurpin

                         Your plan seems to have worked. I love this software.

                    • 7. Re: Assign Customer number
                      TonyTurpin

                           Quick secondary question.

                           If you have a relationship set up and you remove that relationship then come back at a later time and redo the same relationship, will the records you had done be re- related again?

                      • 8. Re: Assign Customer number
                        philmodjunk

                             As long as the values in the fields specified as match fields in the relationship have not changed, yes.

                        • 9. Re: Assign Customer number
                          TonyTurpin

                               OK That's what I figured but just want to make sure. Really appreciate your help on this.

                          • 10. Re: Assign Customer number
                            TonyTurpin

                                 One other question.

                                 I have 20 or so layouts that have the present Contact Table fields on them. I want to use the New Contact Table fields in their place on the same layouts.

                                 Is there a magic way of transforming those from one tables fields to another, or do I need to manually go into each layout and change them. Tell me there is a magic way.

                            • 11. Re: Assign Customer number
                              philmodjunk

                                   Try this...

                                   Make a copy of your file as a back up.

                                   Open Manage | Database | Relationships.

                                   Find the table Occurrence for Contacts that is specified as the data source for those field objects on these layouts.

                                   Double click this table occurrence box.

                                   Select the new table to take the place of the original table as the data source table for that table occurrence.

                                   Click OK to close dialogs and check to see what you now have on your layouts. If the field names are the same in both data source tables, I think that your field objects will now reference to the new table in place of the original.