Spouse/Partner relationship between Contacts error

[Treebys]

Hi there

I'm try to link contact records to records spouse/partners. Each record has a lookup field 'Spouse/Partner' looking up the Contact table. The relationship properties are set to Referential - Remove Link. I linked the spouse on the first record ok but when I went to link the the other back I git an error message:

"This lookup creates a parental relationship between records and you attempted to link this record to itself. This is not allowed because it would create a circular reference. You can link this record to other records of the same type, but not the same record."

This odd, because as I say the relationship is set to Referential not Parental and neither am I linking to the same record.

What am I missing?

TIA

[rfainter123 · Accepted solution]

The issue is less about the type of relationship (i.e. parental v. referential), and more about trying to leverage a 1:N relationship as a 1:1 relationship, which doesn't exist in Dataverse. When you have a lookup, the system assumes the record you are looking up from is the many side of the relationship, and the lookup value is the 1. This means the lookup value becomes the parent of the other, regardless of whether or not you define it as a parental relationship. For instance, many contacts could look up the same spouse. There is not a way, that I'm aware of, to force a 1:1 relationship. Because you've already defined the relationship between the two records on one of your contact tables, the system knows that you already have it defined as N:1. I would suggest using the "Connections" table instead of a self-referencing relationship and create a Connection Role as "Spouse." This will create the reciprocal relationship you are looking for.

[Treebys]

Thanks for taking the time to reply. I looked briefly at Connections earlier and came across this:

"There are other less formal kinds of relationships between rows that are called connections. For example, it may be useful to know if two contacts are married, or perhaps they are friends outside of work, or perhaps a contact used to work for another account. Most businesses won't generate reports using this kind of information or require that it is entered, so it's probably not worthwhile to create table relationships."

This almost seems to suggest that Connections are as useful as table relationships, it implies that you only need table relationships if you plans on generating reports using this relationship ... and I do. Or am I interpreting that incorrectly?

TIA

[rfainter123]

A connection is a record that sits between the two sides of the relationship, in your case both are contacts. The connection table allows you to define the relationship, whereas in a N:N relationship you otherwise cannot. In a 1:N relationship, the lookup column defines that relationship, but as you've already seen, you cannot leverage the 1:N reciprocally as you are trying to do. And you absolutely can report on relationships in the connections table, you just have to think about it a bit differently.

I believe what the statement above is trying to say is, because the relationships between two records are flexible when you use connections, you might not want to use them in lieu of creating a 1:N or N:N relationship between two tables, when you might need that relationship to be a requirement or critical to your data structure. You can absolutely report on relationships formed with connections, you just have to query the connections table instead of contact.