A script could be used to make a second join table record with the _fk field values swapped each time you create a record linking Client 1 to Client 2.
So if a given record is created by you to document Client 1 as the employer of Client 5, the fields in your first join table records would be:
_fkClientIDa = 1
_fkClientIDb = 5
Relatioship: employs (client 1 employs client5)
The script can produce:
_fkClientIDa = 5
_fkClientIDb = 1
Relationship: employed by (Client 5 is employed by client1)
Thanks Phil. I had hoped for a relationship answer mainly cause I wanted to make sure if one side was deleted in the portal the other would be as well. While I could script a delete button that did such a thing, I'd be worried that they'd find another way to delete it that didn't activate the script and left the reciprocal relationship intact. Any ideas? If not I think I'll just have a pop up when they delete a relationship that the other clients relationship has to be deleted also or something.
You can use a third match field to link the pair of records to each other and then enable both delete options in the relationship that links them. Set up this third field as a match field and when your script creates the second join table record, it can copy the value of this field from the first record to a variable and then after creating the second record, it can update this field with the value from the variable.
Wow, so let me see if I'm understanding this right: In addition to the three table occurrences I described and the create-new-join-record script you suggested (which flips the foreign keys), I will include a third match field. Then, i created a new Table Occurence (let's call it join_Delete) based on original join_Clients table. This occurrence is a self join based on the new match field with delete turned on. Now, when a record is deleted in the join_Clients table occurence from the portal on the contact record, the delete sort of cascades down to the join_Delete table occurrence. First the record is deleted in the join_clients- this will then delete the matching record in join_Delete?
Much thanks for your help Phil!
That's the idea.
Everything is working fine now with one slight quirk. The script that duplicates the record and switches the values- it's activate by a button press. The button is in a pop up. After i press the button, both records display in the portal. However, when the popup closes, the portal displays the single correct record. I assume this for 2 reasons:
- My script is switching the values not on the duplicated record, but on the original
- The portal is refreshed (or something) when the pop up closes
Therefore I'm guessing my script if faulty in that it's not operating on the duplicated record. Just my guess. I've attached an image of my script if anyone has any ideas. It just set my variables, does the duplicate script, then set fields to the variables. I've been googling for at least an hour, but have had no luck in educating myself in how one makes the duplicates record the active record.
Phil- I can't thank you enough for your assistance.
If the relationships are set up like I would expect them to be set up, one of the two records will not be linked to your current layout record as it is linked to the record from the other side of the join table. Thus, when the layout refreshes, it drops from view as it's foreign key won't match to the primary key of the current record as the key field values were switched.
Thus, this would appear to be functioning correctly.
What you should do is after linking Record A to Record B, pull up Record B and see if it is already linked to A as that is the intent of this method.