Splitting one table into two

Question

HiI want to "create a new table" from a field in one table and want to move the address field across to the new table but it doesn't show as an additional field I can move. Currently have one table with companies and contacts - want to split it so one company(parent) has many contacts(child). So the data I split out should be the company info (I think).- ie parent info) And address is one of the company fields I want to move....but it wont let me. Any help would be appreciated.------------------------------Charmaine Silverman------------------------------

markshnier__you · Answer

np,Change the name of your first table to Companies.Copy the Table but call it Contacts. &nbsp;In Contacts, Delete the all the fields which are about the company, leaving only fields which are specific to the contact. &nbsp;For example, if there &nbsp;an address field, but it is actually specific to the company and not the specific contact, the delete it.Make a Relationship between Company and Contacts where One Company has Many Contacts.Get into the import export menu off say the Dashboard or any Table Home Page and set up a saved table to table copy from company to contacts. &nbsp;Match up the fields to copy and for the field [Related Company] which got created when you built the relationship, use the [Record ID#] of the Companies table. &nbsp;I am assuming here that the Key field of Companies is till the regular [Record ID#] field.Run the save table to table import.you should now be able to go back to a company record and adjust the form to have an embedded report of contacts. &nbsp;You should also have an Add Contacts button available to add a contact and have it connect to the Company when saved.Once you are sure that everything is working perfectly you can add any look up field you want from companies down to contacts. For example you probably want to look up the company name back to contact and you probably want to set the field for related company to use the company name as the proxy. That company name field which is the proxy field should now be put on the contact form so the user has a nice hyperlink to get back to the company if they are sitting on a contact record. &nbsp;&nbsp;once you are sure that everything is working perfectly you can add any look up field you want from companies down to contacts. For example you probably want to look up the company name back to contact and you probably want to set the field for related company to use the company name as the proxy. That company name field which is the proxy field should now be put on the contact form so the user has a nice hyperlink to get back to the company if they are sitting on a contact record.&nbsp;Once you were sure everything is working nicely you can delete the original contact fields on the company table &nbsp;as they will now continue to live out their life in the separate Contacts table.&nbsp;------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

adamkeever1 · Answer

To my knowledge, your best bet is to start fresh. If you are familiar with Excel or Google Sheets you can do this in a rather short amount of time. There are several steps, but they are pretty simple and you only have to do this once.

Export the data
Copy &amp; paste the company fields to a new sheet
Delete duplicates
Add a key field to the right of the primary company field
Populate the key field with ascending numbers starting at 1
Add a key lookup field to the contacts sheet on the right and next to the company field
vlookup the key from the company field in the company sheet
Copy the key lookup column and paste values
Delete the company field from the contacts sheet
Delete the key field from the company sheet
Upload the two sheets to two Quick Base tables creating new fields and verifying the fields are of the correct type
Create a relationship where one company has many contacts and use the key lookup field from the contacts table as the reference field and add any other lookup fields you may have in the company table

A key field must be unique and so you remove the duplicates of your company field. Quick Base generates Record ID#'s and assigns them to records in ascending order as they are located within the upload file. That is why you added the key field and populated it with ascending numbers so that the key lookup field in the child table will match the correct Record ID# in Quick Base when you upload the data and create the relationship.&nbsp;This will give you the parent/child relationship you are wanting and will add the company info to the contacts records for you.Here is a post that shows step by step details of creating the relationship; it was for a different use case, but the relationship set-up is the same:Building Relationships Using KeysYou should notice that the data in this example has the key lookup field on the far left and is titled ID; this field is what allows the child table to lookup to the parent table and also allows the parent table to summarize the child table when the relationship is created.Hope this helps you out Charmaine.------------------------------Adam Keever------------------------------

markshnier__you · Answer

CharmaineThis can be done natively with a few clicks.You can copy the table to create the new table and then hack away all the fields that you do not need. Just run a checkbox down all the fields you don't need. There will be a button to Copy Table on the Table Home page.&nbsp;Just set up a saved table to table import and copy across the records into the contacts table.&nbsp; You will find it lets you map across the address field components. In fact, the fields will auto line up correctly as they will have the identical spelling if you create the table as a copy&nbsp; Be sure to map the [Record ID#] field into the field for [Related Company] in Contacts.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

charmainesilver · Answer

Thank you - please can you confirm which records I should delete from the copied table - the contact details or the company details?
And then copy the contacts details from the original table ? or copied table to a new table? Sorry I am getting myself confused. Can you just add which tables I am using in your above answer and I should then be able to do it.

------------------------------
Charmaine Silverman
------------------------------

adamkeever1 · Answer

Thanks for sharing.------------------------------Adam Keever------------------------------

Forum Discussion

Splitting one table into two

5 Replies

Related Content

Filter records based on table connection

Filter out some records on related table

Do unused tables related to currently used tables cause functionality problems?

Can I use quickbase actions within the same table?

Summary Table

Recent Discussions

Save & Redirect in a Formula URL Using API_GenAddRecordForm

Many to Many too Many...

Selecting which fields to display from Formula URL searches

Bulk create child records from selection list

(Pipelines) Get Users in Role / Process XML from HTTP API