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.
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.
Or just RTFM and then post back if you get stuck.