Question: Does the data field that you want to add to the primary also appear in the foreign table? And what is the hope here. . . to narrow down the related records by the second field? I"m just trying to get a better, more detailed picture. There are several ways to approach this and they can boarder on the difficult to the easy. It depends on the numbers of records to be related to your key fields.
Great question though.
1 of 1 people found this helpful
In the relationship graph, you can double-click on the "lines" between two TOs and the dialog to change the relationship will appear. You can add the other field from that dialog.
Hey Jim thanks for the reply
I have messed up and used a field for the key that I wished I hadn't used.
primary is BatchID foreign is BatchID both currently exist is the db, I want to add another field for the key like
primary _pkBatchID foreign _fkBatchID
jimahopkins already speculated re your motivation for trying to do this.
If you want to simply display a serial number from the parent (“one”) record on a layout of the “many“, then simply display it as a related field. If you want to use it in a calculation or script from the child context, likewise refer to the related field.
Adding it to the relationship doesn't add anything, except complexity.
Phil, if you're wanting to change the relationship completely from the current related fields to the two new fields that you mentioned, you can do that in the relationship graph as beverly suggested. But remember, the records that you may have seen in your relationship will totally disappear if the related fields do not hold the information that you're drawing from. In other words, both fields, foreign and primary have to have the data that keys together in order to use the data from one table to another.
youll have to write a scripted process that goes thru each child record and sets the new foreign key to the correct value
I know that I can add another related field in the TO. the only data that will be in the 'new' child will be when i add a new record.
I need to "create/add/copy" the related data from one to the many side as is not there yet, then I can remove my old foreign key field and only have the _fkBatchID in that many side.
The reason for this is if I change my current 'one' key it will not change my 'many' key. I only get the 'many' key(data) when a new record is created.
Maybe I not be clear enough here.
Thats what I mean, I lose that connection or the relationship if I do that, thats why I want to convert over to a new relationship (with the correct data in place as not to lose that).
youll have to write a scripted process that goes thru each child record
Replace Field Contents utilizing the (still) existing relationship should work just fine.
1 of 1 people found this helpful
If I understand your question, you have two tables who are related by some fields and you want this relation changed to dedicated keyfields, which you can protect and hide from the enduser.
1. Create the new primary keyfield (_pkBatchID) in your primary table. Type number. Populate this with a serial number. The possibility given in the field definitions is OK. Don't change the realtionship yet.
2. Now create a foreign keyfield (_fkBatchID) in your related table. Also type number.
3. Go to a layout based on the related table, put the _fkBatchID field in there and choose Records > Replace Field Contents from the menu bar. Choose the bottom option 'Replace with calculated result'.
4. In the calculation area type "<mainTableName>::_pkBatchID". Of course use the name of the main table.
5. Click 'Replace'.
6. Check if the numbers are calculated as expected.
7. Now go to the Relationship Graph, locate the relation and change it to the new fields.
That's it thanks so much,
I do this for such temporary keys. Pull through the existing relationship or use ExecuteSQL() in a script to "set" what you need.
Sent from miPhone