Splitting one table into two
Hi I 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 ...
Forum Discussion
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.
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 Keys
You 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
------------------------------
- Export the data
- Copy & 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
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 Keys
You 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
Qrew Legend
Charmaine
This 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.
Just set up a saved table to table import and copy across the records into the contacts table. 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 Be sure to map the [Record ID#] field into the field for [Related Company] in Contacts.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------- 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
------------------------------- MarkShnier__Younp,
Change the name of your first table to Companies.
Copy the Table but call it Contacts.
In Contacts, Delete the all the fields which are about the company, leaving only fields which are specific to the contact. For example, if there 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. 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. 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. 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.
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.
Once you were sure everything is working nicely you can delete the original contact fields on the company table as they will now continue to live out their life in the separate Contacts table.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
