Tip #163: Create a 1:1 relationship

Creating a 1:N  or N:1 relationship between two entities is very straightforward, but what if you want to do a 1:1 relationship?
For example, say you have a subprocess that needs to be owned by a different user than the account owner, or you have a child relationship where only one record should be associated with the parent?

  1. Create a 1:N relationship and a N:1 relationship between the two entities.
  2. On the relationship definition, set the related records grid not to display in the navigation area
  3. Create a workflow that upon create of the secondary record updates the lookup field on the primary entity record.

For example: I have a custom project entity and I am adding a related technical audit entity for technical review of projects. Sure I could just add additional fields to the project entity, but this process is owned by a different user than the owner of the project, and adding all the fields will push me over the 75 field limit for CRM for tablets.

I create the technical audit entity and create a 1:N and N:1 relationship between projects and technical audit. I then add both lookup fields to their respective entities.

In this example, I want a technical audit record created every time a project reaches the approved stage. So I create a workflow that upon update of the stage to approved, creates the project technical audit linked to the project, then updates the project technical audit lookup field with a link to the technical audit

The result is a two-way relationship with a lookup field on both sides. Benefits include:

1.      I can look at a project and see that a tech audit has been created for the project.

2.      Give that it is a 1:1, nobody can create multiple related tech audits by mistake

3.      This gives me the flexibility to show technical audit fields on project forms and views and project fields and technical audit forms and views via quick view forms.

7 thoughts on “Tip #163: Create a 1:1 relationship

  1. Andre Margono says:

    This also really useful to create application process, e.g: Application > License

  2. Henrik says:

    This has been very useful to do in the past, and it really works as advertised, however let me point out that you can end up in inconsistent situations if you don’t spend some effort making it “waterproof” for create, update, delete situations.

    i. Entity 1 points to entity 2 and that points back to entity 1.
    ii. Introduce an entity 3 and change the look-up to point from entity 2 to entity 3.
    iii. Entity 1 will still point to entity 2 and your data is now inconsistent.

    • Joel Lindstrom says:

      Great point, and one thing you have now that we didn’t in the past is real time workflow. You could make a real time workflow that runs before the change of the lookup field on record 2 that clears the lookup field on record 1.

  3. Martijn Vermunt says:

    The key in “Create a workflow that upon create of the secondary record updates the lookup field on the primary entity record.” is the referring to the newly created child record.

    This is listed under “Local Values” in the “Look For” dropdown of the Form Assistance.”

    I’ve spent hours looking for this. Hope it helps someone.

  4. Axel says:

    excellent post, just what I was looking for.
    I was looking for creating a parent record from child record in CRM 2016 onPrem.

    This is a simple workflow where the first step creates a new record (where parent entity is selected).
    Next step is updating the child record using the information from Martijn Vermunt. This builds the relation between both records.
    I have only a 1:N relation between parent and child.

  5. […] between tables (no “real” 1:1 relationship exists in Dataverse, but there are ways to fake it), one option would be to automatically create the related parental record behind the scenes. With […]

  6. Artem Grunin says:

    I guess I’m late to the party, but still have to notice that 1:1 is definitely not two 1:N cross-references. 1-to-1 mean that Child record DOESN’T have is own ID column and use FOREIGN KEY to Parent record as it’s own PRIMARY KEY.

Leave a Reply

Your email address will not be published. Required fields are marked *