Linking tables from different applications based on a field that is not the primary key in either table.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have Application One with Table A (primary key is Record ID) and Application Two with Table B (primary key is Record ID) - but the primary keys in each table are unrelated.

I want to build a relationship and link the tables based on a text field called in "incident number" in Application One Table A and "tfs number" in Application Two Table B. 

Then I want to share information from Table A with Table B and vice/versa. 

How do I build the relationship, change the link field to the text field, and pull information into/out of both tables. 

Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,774 Points 50k badge 2x thumb
Anne,

If you have never changed a Key field before, you may want to try this on a Copy of your app first.

Basically all you do is to look at the field list for each respective table and change the Key field to be the Incident number  in Table A and the tfs number in table B.  

(If there no existing relationships, then just make those changes in your live app.)


Once you have made the change, then you simply make a Relationship and when the screen comes up to chose the reference field on the right side of the relationship, do not accept the default field offered, but instead choose the field you know is the correct reference field.  For example on the relationship where 1 Table A has Many table B's, the right hand side would be the tfs number.




When you first create the relationship, I suggest not adding any lookup fields at first. then edit the relationship and as many lookup fields as you like.  



Then make the second relationship the other way around where 1 Table B has Many Table A's.  
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
I don't think I want to change my primary key in either table because this is filed is used in many relationships within their respective applications.   It is possible to have multiple primary keys?  One used within the application (like record ID) - and one that might link to tables housed in a totally different application (incident number).
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,774 Points 50k badge 2x thumb
There is no concept of a Secondary Key in QuickBase.  There is just One Key field per table.

A work around would be to do a pair of Synched tables using native QuickBase Sync which will update every hour and then I think that you can have an alternative Key field there.
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
How do you do that?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,774 Points 50k badge 2x thumb
It's probably more than I can explain in the "free" time I have. Its not actually complicated one you have done it once. The help is here http://www.quickbase.com/user-assistance/Default.html#about_connected_tables.html  Feel Free to contact me off line for one on one assistance in setting up a Synched table.

Or just RTFM and then post back if you get stuck.