Were Moving !!

I plan to keep this blog up for a while, but I'm moving everything over to a new Wordpress site at www.informaquest.com

Thanks!

Join Entities in Dynamics 365 and Why they are awesome

If you have ever worked with many-to-many (N:N) relationships in Dynamics 365 (the product formerly known as Dynamics CRM), you may have at some point created a N:N relationship between entities.  It is a useful relationship type for sure, but it has some serious ‘out of the box’ limitations.

The main issue I have always had with it is the complete lack of capability to execute a workflow process when the relationship is created, or any audit record of who and when the relationship was created or modified.

Example 1:

Contact has a N:N relationship between itself and a custom entity called ‘Web Roles’.  You assign new Web Roles to the Contact record to allow them access to pages on a custom portal.  But you need to know who added the role, and when the role was added.  Say you have delegated the web role assignment to customers that have an admin role to manage their own users on the portal?  How would you know who added the role and when?

Example 2:

Contact has a N:N relationship to Account.  For each Contact, they have a regular parental N:1 relationship to an Account, but they might also have a relationship to several other Accounts.  Perhaps they are a distributor of your products and have a company that they work for, but also work with several of your other Accounts to sell them products.  And each Contact may have a different role that they have in relationship to the other Accounts.

But Wait??

If you have worked with Dynamics 365 for any amount of time, you might be thinking “Hey, you can use the built-in Connections entity for this”. And you would be correct.  But only if you are only going to use Connections for just one type of N:N relationship.  Since Connections are basically available for ANY record to be linked to any other record, it’s a lot more generic than it needs to be.

Solution:

The solution is what I like to call a ‘Join Entity’.  If you have ever done traditional database or application development that worked directly with a SQL database, you should already be familiar with this concept.  It’s basically a table that sits between two other tables and stores the primary key for records in each table that require a join.

In Dynamics 365 parlance, we create a Join Entity that works just like a join table.

Step 1: Create a new Custom Entity

When you create this entity, give a good name that reflects what you are joining.  In this example, we are going to create a join between Account and Contact to allow for multiple Contacts to be associated with multiple Accounts.  I’m going to call this one Account To Contact.

For the Ownership option, this one is up to you.  In most scenarios, it is safe to set this to Organization since we are just using this for joining up other entities and we don’t need all the overhead associated with User or Team ownership.  If the relationship needs it and perhaps some user or team needs to own the relationship then by all means, set it that way. 

Most likely you won’t need any of the Communication & Collaboration options enabled, and you can always enable most of them later anyway.  As a rule, I like to keep them all OFF until I know I need them.

For Data Services options, I would Allow Quick Create, and Enable Auditing.

For Primary Field, you can leave the default name as Name, or give it something else more appropriate if you prefer.  We’ll talk about how to deal with this field in a later step. 

IMPORTANT: You need to set the Field Requirement for the Primary Field to Optional at this step.  If you forget, don’t worry it can be changed by editing the field directly, but it’s best to do it at this time.

Side Note: For the color setting, I like to set all my custom entities color to plain white (#ffffff) and then get some nice flat black icons from Icons 8 (http://www.icons8.com).  It’s an awesome site with thousands of icons and I highly recommend it.

Click Save to create your new entity.

Step 2: Add the Relationship Lookups

Now we need to add the appropriate N:1 lookups to the entities we are trying to join on.

Click the Fields option and then click New in the toolbar.

Display Name should be something that makes sense to the user that will be adding this new record/relationship.  For our example, we are joining to Account so we will call it Account.

Tip: This will also default the Name field to Account, but it makes sense to add a suffix of Id to this SDK name field.  This will be helpful later if you have to write any JavaScript or .NET code to reference the field. You should be able to quickly recognize it as a GUID to lookup to another entity.

Field Requirement should be set to Business Required to avoid any orphaned join records.

Select a Data Type of Lookup and a Target Record Type of Account. 

Save this new field and repeat these steps to add a lookup to the Contact entity.

• Display Name = Contact
• Name = new_ContactId
• Field Requirement = Business Required
• Data Type = Lookup
• Target Record Type = Contact

Step 3: Edit Default Form

Click Forms in the left navigation to review the list of built in system forms.  Click the Information form listed first with a Form Type of Main.

Customize this form and add your newly created Account and Contact fields.  Click Save and Close to save your form changes.

Tip: Since you only get one section by default, the Name (and possibly Owner) field(s) will fill up the entire width of the form.  I find this annoying and completely impractical, but that’s just me.  I will usually edit the General Tab and set the Formatting option to use Two or Three Columns.

Step 4: Create Quick Create Form

While still looking at the list of Forms, click New on the toolbar and select Quick Create Form

Modify the form by adding your 2 lookup fields to the form.  Save and Close the new form.

Optional:  Probably a good idea to go ahead and publish your changes now if you haven’t already done so.

Tip: If you know what Source entity will be used a majority of the time, put the other entity lookup first on the form.  In this case, we are assuming that contacts will be added from the Account record, so we are showing the Contact lookup field first.  The Account field will already be filled in when the join record is quick created.

Step 5: Build a workflow to set the Name Field

Now that we have the basics setup, we need to set a Name for these new records.  This is the name that will show in any lookups to this entity, which the system uses by default.  We’ll build some views that will be used on forms, but we can’t just leave this field blank.  This workflow will set the Name to a combination of the Account and Contact names.

Note: Recall that in Step 1 we set the primary field requirement to Optional.  If you did not do that, now is the time.

Navigate in your Solution (or All Customizations) to Processes and click New.

Process Name: This should reflect the Entity Name, RT for ‘Real-Time’, and some description.  I like to call this one New to show that it runs when a new record is created.

Category = Workflow

Entity = Account To Contact

Run this workflow in the background should be Unchecked/Off

Click OK to create the new process.

Options for Automatic Processes

• Scope = Organization
• Start When =
• After Record is Created
• After Record Fields Change
• Select Account and Contact (the custom join lookup fields added earlier)

In the logic area, add a step to Update Record 

Click the Set Properties button next to the Update Record.

Click in the Name Field an add the dynamic values of Account and Contact.  

Tip: Put some kind of delimiter like a dash, asterisk, or colons to separate the values.

Note:  This can be whatever dynamic values you like, but try to make it unique to this join record.  Remember that this will show up in all the lookups that may reference this join entity.

Save and Close the Properties

Activate the Workflow Process

Step 6: Customize a Form View

We are going to want to add a sub-grid to both entities we are joining to, so we need a view that shows the values.

At this point we haven’t modified any of the default views, but we will at least modify the Active Account to Contact view, then do a save as and modify that one for a form view.

• Navigate in your solution (or All Customizations) to the Account to Contact entity.
• Select Views from the left navigation tree
• Select to edit the Active Account to Contact view
• Add the Account and Contact lookups columns to the view, and order them as you prefer.
• Save the view, but DO NOT CLOSE the window
• Click Save As and enter Form View for the new view name
• Remove the Name column from the view
• Move the Contact name and the first column.  Note: This is because we will use this view on the Account Form, so it makes sense for the Contacts to be listed first.

·        Option: If you like, you can do another Save As, call it Contact Form View, and set the Account as the first column.

Step 7: Add it as a sub-grid to a form

Now that we have our entity, we set the fields correctly, and we built a form view, we can put this all together on the Account form and see how it works!

• Navigate in your solution (or All Customizations) to the Account entity.
• Select Forms from the left navigation tree
• Select the Account form of Form Type Main from the view
• Scroll to the appropriate spot on the form where you want to display the new entity
• Insert a Section to contain the Sub-grid
• With the Section selected, insert a Sub-Grid

The key fields here are in the Data Source area.

• Records = Only Related Records
• Entity = Account to Contact (Account)
• Default View = Form View

Note:  If/when you add this to Contact, select Contact Form View instead of Form View.
Save, Publish, and Save and Close the form changes.

Step 8: Test it!

Tip: If you haven’t recently done so, now is a good time to Publish All Customizations.  We’ve made a lot of changes so we want to be sure everything shows up for testing.

Navigate to an Account record

Scroll down to where you added the sub-grid and it should look something like this

To add a new Contact to relate to this account, click the + button.  You should see the Quick Create form we created earlier.

Note: If you don’t see this + button, or you get the regular entity form instead of the quick create form, you missed a step somewhere in setting up the entity and lookups. Check the following:

• Does the Account to Contact Entity set to Allow Quick Create
• Are the Account and Contact lookup fields set to Business Required

Enter a Contact name, and click save.  You should now see the new relationship added!

Step 9: Extend it!

What we’ve done so far has really been pretty much what you get out of the box for N:N relationships, with the exception of step 8 where we added the new relationship with a quick create form.  Adding those with the out of the box functionality is painful (just my opinion…).  Now we can get to extending this new entity to do something that the out of the box functionality does not.

For our example, we are going to add a Role attribute to the entity.

• Navigate in your solution (or All Customizations) to the Account to Contact entity.
• Select Fields from the left navigation tree
• Click New to add a new Field
• Display Name: Contact Role
• Data Type: Option Set
• Add Options for Role 1, 2,3,4

Note:  This is just for example purposes, you can add any field type as required by your needs.

Save and Close the new field

Select Forms from the left navigation

Select the Quick Create form type

Drag the new Contact Role field on to the form

Save and Close

Also edit the main Information form and add the Contact Role field, Save and Close

Select Views from the left navigation

Select the Form View(s)

Add the new Contact Role field to the view(s)

Save and Close

Publish your changes

Now when you view the data on your Account entity, you’ll see the new Role value assigned to each record.

Wrapup

We have covered a lot of different topics, but you should have a good grasp of the power of using this Join Entity concept instead of the out of the box many to many (N:N) relationship.  You gain auditing capability to see who, what, when.  You gain the ability to extend the relationship and add additional fields to define the relationship (e.g. roles, etc.).

You can also extend the entity to run workflow processes when records are created or modified.  We did a simple name update, but this could perform many other tasks if needed (e.g. send an email, update a related record, etc.)