Split large table into smaller one and link

[Gerianne_Chapma]

I imported a large spreadsheet with all the data about customers and their contracts. All the customer data and the related contract data resided in one big spreadsheet, so I have one big table with all the data. I have some customers with more than one contract, so I have duplicate customer info in some rows. I have completed alot of customization on field types already, and now realize I want to break this large master table into a customer table and a contract table so I only have to update customer info in one place. Is there a way to do this so it works - I want to bring over all the customer data to a new table . I have last name, first name, address, town, state, zip, phone fields that would need to be moved to new table for customers. The contract table would remain with contract number, instrument assigned, start date, bill week, status, etc…

[Kamille_Parks · Accepted solution]

The table you currently have will be your Contract Table. To separate your info out, do the following:

1. Create a “Full Name” field which concatenates the “First” and “Last” name fields. If you have more than one “John Smith”, for instance, also concatenate the “Address” field to give you a more unique name ensuring separate contacts aren’t accidentally merged together in the next step.
2. Convert that ^ formula field into a Link to Another Record field pointing to a new table for “Contacts”. This will create a single Contact record for each person, and link that contact to as many Contracts as they have been listed.
3. In your new “Contacts” table, add Rollup fields (use the ARRAYUNIQUE(values) aggregation) for all the contact details still in the “Contracts” table.
4. Convert all of ^those Rollup fields into SingleLineText fields (or whatever field type you feel appropriate).
5. Go back into the “Contracts” table and turn the Name, Address, etc. fields into Lookup fields to pull the information from the “Contacts” table (or delete those fields if you don’t need them shown on each Contract record).
6. Adjust the primary field the “Contacts” field if necessary.

[Gerianne_Chapma]

Works wonderfully - thanks so much. I am used tp primary fields being Account or ID numbers, so the lastname firstname address string is still hard to get used to as primary field- but works beautifully.

[Kamille_Parks]

You don’t have to keep that as your primary column, as Step 6 was meant to imply. If you had an account ID (or any other unique identifier) field ready you could have used that field and skipped step 1. You didn’t mention an account ID field, which is why I had to suggest making a field which would result in a unique string.

You are more than welcome to replace all the data in the First/Last/Address primary field with the account ID.

[Gerianne_Chapma]

Ok -thanks - I didn’t have IDs for customers and the unique string worked to get all customer data into new table quickly - now that I have all that I can easily assign customer IDs to each unique customer record. It will make entering new records much easier.