are all the variables manually selected or are they calculations? i.e. How is an enemy of XYZ determined?
if you are just pulling information from a field you can add a text box to the layout and the import the related field.value
Menu Bar > Insert > Merge Field > select related table/field An example output would produce something to the effect of
<<name>> Owns Company <<companyname>>
It is a calculation that determines field relationship (not records) then imagine it is more complex and depends on structure
I should have been more clear on that.
my contactCompany table obviously has a primary key and a name field
my contactCompany Relationship table has a primary key and a text field where a user describes the relationship.
It also has a id_relatedFrom and id_relatedTo field like any normal join table but if i do it that way, I cannot see the relationships in the same portal from the related to and related from records. in this example if i join dave as the contactCompanyFrom and ABC as the contactCompanyTo in the join table, I cannot see that the relationship from the ABC record because the current tables are setup
I am viewing the ContactCompanyRelationship portal on the ContactCompanyFrom record but what to see the relationship bi directionally in the same portal as described in the OP
Seems like you need this data model:
Contacts::__pkContactID = Relationship::_fkParentContactID
RelatedContacts::__pkContactID = Relationship::_fkChildContactID
Where Contacts and RelatedContacts are TO's of the same data source table, your table of individual and company contacts.
To get a "two way relationship" so that you can see what companies an individual is linked to and also see what individuals a company is linked to, Create two records in Relationship that are identical except that the two _Fk field values are swapped.
SO if contact #1 needs to be linked to contact #234, you create two records that would look like this in table view:
You can use a script to "back link" a pair of records by creating the second record in the relationships table as part of linking the two contacts in a relationship.
2 of 2 people found this helpful
Here's (a grossly simplified version of) how I do it:
CONTACT ––––< RELATIONSHIP ––––– recip RELATIONSHIP >–––– recip CONTACT
The relationship between CONTACT and RELATIONSHIP is CONTACT::ID = RELATIONSHIP::ContactID
The relationship between RELATIONSHIP and recip RELATIONSHIP is RELATIONSHIP::ID = recip RELATIONSHIP::reciprocalID and RELATIONSHIP::reciprocalID = recip RELATIONSHIP::ID
The relationship between recip RELATIONSHIP and recip CONTACT is recip RELATIONSHIP::ContactID = recip CONTACT::ID
The RELATIONSHIP table contains the "Role" field which describes the role played by the CONTACT associated with it. For example:
Malia Obama ––––< Daughter ––––– Father >–––– Barack Obama
Rex Tillerson ––––< Employee ––––– Employer >–––– US Department of State
Brigitte Macron ––––< Wife ––––– Husband >–––– Emnanuel Macron
This model means you can see relationships from any direction which is what it sounds like you're looking for. It also means you don't have the possibility of the relationship records being out of sync as you do with the
CONTACT ––––< RELATIONSHIP >–––– recip CONTACT
model since there is inherently no directionality in the relationships and the role is only stored once.
I like that!
You still have to create two relationship records so I do not see that it has much advantage when it comes to keeping things "in synch", but it's definitely clearer and easier to understand when you find it in a relationships graph.
1 of 1 people found this helpful
By "in sync" I mean if you need to adjust the relationship for whatever reason (sometimes mom fills out the form but accidentally lists herself as dad). We used to do it the other way, but didn't have the scripting controls in place to make sure both records were updated when a user changes "father" to "mother" so you'd get different results depending on which direction you looked:
Malia Obama ––––< Daughter/Father >–––– Barack Obama
Barack Obama ––––< Mother/Daughter >–––– Malia Obama
Both relationship records are created simultaneously. If either is missing, the relationship doesn't exist for all practical purposes. We use the "magic key" method for most record creation, so we end up creating them transactionally:
Set Field [ Relationship Creator by ID::ContactID ; Value: "1" ]
Set Field [ Relationship Creator by ID::roleKey ; Value: "Father" ]
Set Field [ recip Relationship Creator by reciprocalID::ContactID ; Value: "234" ]
Set Field [ recip Relationship Creator by reciprocalID::roleKey ; Value: "Daughter" ]
Set Field [ Relationship Creator by ID::reciprocalID ; Value: recip Relationship Creator by reciprocalID::ID ]
Commit Records/Requests [ With dialog: Off ]
Note that the third step there populates both the recip Relationship Creator by reciprocalID::ID field (via auto-enter value) and the recip Relationship Creator by reciprocalID::reciprocalID field (via the magic key relationship) at the same time. We then complete the double-link in the fifth step and commit both records in a single transaction.