Dynamics CRM (TCS Tools): Use a workflow to set a lookup

By | April 21, 2017

It happens sometimes that we have a Parent and Child entities in Dynamics, and we need to link a child record to a parent record. Which seems to be a simple task unless, of course, we need to do it automatically. That’s exactly when we’ll know that we have an unexpected problem – if we wanted to use a workflow, there would be no out-of-the-box functionality that would allow us to do that.

Things can quickly get worse if it turns out we need to apply more complex rules when looking for a parent record. And what if there are duplicates? Arghh..

Here are just a few examples of that kind of relationship:

  • Account and Parent Account
  • Account and Primary Contact
  • Case and Customer

Every now and then we would have some unusual situation where we would have to put a few Child records to Dynamics, we would know that there are Parent records, and, yet, the relationship would be more complicated.

For instance, we might have a few parent accounts with the same name, and we would have to choose the most recent one. Out-of-the-box Dynamics workflow are not offering this kind of lookup functionality, but this can be accomplished using a custom workflow activity.

If that’s why you are reading this post, my TCS Tools solution for Dynamics CRM might be of some help. Here is how it works.

1. Import the solution to Dynamics. It is an managed solution, so you will be able to remove it.

2. Use advanced find to create a Lookup Configuration record (see complete walk-through below)

3. Create a worfklow on the Child entity that will be using TCS Tools:Lookup Setter activity to set parent lookup attribute based on the Lookup Configuration you just defined

 

Let’s imagine the following scenario for an Account and Contact entities:

in our Dynamics organization, we have account and contact records. There is “Account Number” field in the Account entity that’s been populated already:

That’s an out-of-the-box “accountnumber” field, although it is not available on the form by default, so I just added it there.

Also, let’s say there is a similar field in the contact Entity, and I actually had to create such a field since it’s not there out-of-the-box:

The schema name of this field is “tcs_accountnumber”. We’ll need it a bit later.

In our scenario the requirement is:

when adding a new contact, we will know account number, but we won’t know account name. What we need is to create a workflow that will correctly populate “Account Name” lookup in this situation.

So, first, let’s create our Lookup Configuration record. In order to do that, we will need to prepare Fetch Xml first. It’s easy to do using Advanced Find – I will open Advanced Find, define the filter, and will use “Download FetchXML” button to get the xml:

Notice how I put tcs_accountnumber in the filter there – you can put anything, it’s just for convenience(you’ll see in a second).

Here is my downloaded FetchXML:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
  <entity name=”account”>
    <attribute name=”name” />
    <attribute name=”primarycontactid” />
    <attribute name=”telephone1″ />
    <attribute name=”accountid” />
    <order attribute=”name” descending=”false” />
    <filter type=”and”>
         <condition attribute=”accountnumber” operator=”eq” value=”tcs_accountnumber” />  
    </filter>
 </entity>
</fetch>

I have higlighted the part I’ll need to modify. “Lookup Setter” custom action will be running for contact record in this scenario. It will go over all the attributes of the current contact record, and it will try to find the following tags in the FetchXML:

#contact attribute schema name#

If it finds a tag, it will replace it with the actual value of the attribute.

Therefore, here is what we should use for the Lookup Configuration:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
  <entity name=”account”>
    <attribute name=”name” />
    <attribute name=”primarycontactid” />
    <attribute name=”telephone1″ />
    <attribute name=”accountid” />
    <order attribute=”name” descending=”false” />
    <filter type=”and”>
         <condition attribute=”accountnumber” operator=”eq” value=”#tcs_accountnumber#” />  
    </filter>
 </entity>
</fetch>

From here, there are only a few steps left.

Let’s create our Lookup Configuration record (I’ll be using Advanced Find for this):

 

 

There are a few things on the last screenshot you need to be aware of:

  • Give that record any name you want, just make sure you know what it means
  • Use FetchXml created earlier fort he Fetch Xml field
  • Fetch Result Attribute is the schema name of the attribute that will be used to populate the target field
  • Target Attribute is self-explanatory. That’s what this whole thing is for, really

Once I have Lookup Configuration record, I can proceed to create a workflow:

This is a real-tie workflow – it does not have to be, but it’s more convenient when parent account is set right away.

It will run when a contact record is created or when Account Number field is modified on the contact.

And it won’t do anything (there is a condition in the first line) if Account Number field is empty.

Finally, if it runs and if it gets through that first condition to the TCS Tools:Lookup Setter custom workflow activity, it will need to know which Lookup Configuration to use, so I’m going to set the properties of that step:

That’s it.. Now I’ll save everything and activate the worfklow.

Let’s see what happens when I create a new contact – remember the very first screenshot in this post? I’ll be using the same 20170101 account number for my contact, but I’ll leave “Parent Account” field empty:

 

And, with that, I’ll just click “Save”.

Here we go.. I knew contact’s account number, and that’s what I put into the contact record – that workflow has done all the rest. It found the account record and populated “Account Name” field on the newly created contact.

From here, I might add more conditions to the Fetch XML if I wanted to define more elaborate rules. I might also use it in combination with the data import jobs or to fine-tune various data migration jobs. All of that without having to write any more code. Mission accomplished!

27 thoughts on “Dynamics CRM (TCS Tools): Use a workflow to set a lookup

  1. Karuna

    How do I download and try your managed solution for the lookup setter

    Reply
  2. Karuna

    Hi Alex,

    I have a requirement and I’d like to know how i can achieve it via your tool. Here it goes..

    First IOT Alert is created > there is no Parent IOT Alert so system creates a Work Order for that Asset.
    Second IOT Alert is created > Now system should check is there any Work Order of Status=Open + Work Order Asset = ERMS123 + Work Order Incident Type = App Hang. If all the 3 conditions are met, then it should get the WO’s related IOT Alert and set that Alert as the parent to the Second IOT Alert. If no record found it will not set any IOT Alert as parent to the second.

    Your solution would be a great help.

    Regards
    Karuna

    Reply
    1. Alex Shlega Post author

      Hi Karuna,

      if there us a relationship between those IOT alerts and work orders, you can probably use this kind of fetch to get the parent alert, and, then, you can run the workflow on the “Create” of the IOT alert record:

      <fetch…
      <entity name=”IOT Alert”>
      <attribute name=”iotalertid”/>
      <link-entity name=”Work Order” from … to…>
      <filter type=”and”>
      <condition attribute=”statecode”… />
      <condition attribute=”OrderAsset”… />
      <condition attribute=”IncidentType”… />
      </filter>
      </link-entity>
      </entity>
      </entity>

      Your fetch result attribute will be “iotalertid” (or whatever the name of the alert id attribute is – must be added to fetchXml then)
      Your target attribute will be “parentiotalertid” (or whatever the name of the parent lookup attribute is)

      Reply
  3. BK

    When You setup quick search view including Account Number field in account entity, and when you go to different entity and write account number of any account, CRM automatically will find this account and put it in Lookup.
    Why you did this development I didn’t understand.

    Reply
    1. Alex Shlega Post author

      The blog post shows you how to use this feature, not necessarily what to use it for. Have a look at what Karuna was trying to do above. That’s a good real-life scenario that requires more complex conditions and workflow automation.

      Reply
  4. James

    Hi Alex, First of all – amazing blog/resources – this Lookup is exactly what I’ve wanted for ages but haven’t worked out how to code for myself – your a legend!

    However, I imported your TCS Tools solution today and in the workflow builder I’m not seeing the “Lookup Setter” as an option at all – I have a screen grab but can’t post it here. I tried “Attribute Setter” – is that the same, or is it not working because I’m doing something wrong?

    I also noticed that the “Lookup Configurations” Entity is now called “TCS Lookup Configurations and has a new field called “Update Direction”, which I’ve tried both set to “Entity” and “Fetch Result”, but neither seems to work.

    I’m guessing you’ve just updated and not had time to update the blog post – I’m sure you’re a busy person!

    Thanks for any help!

    James

    Reply
  5. James

    Hi All – As per my comment above, for anyone looking for the “Lookup Setter” within the TCS Tools section of the workflow UI, it is now “Attribute Setter” and the TCS Lookup Configurations field that’s not mentioned above needs to be set to “Entity” so that it updates the form. Looks like the other setting “Fetch Result” is for setting child records…

    Alex – these tools are brilliant – really appreciate your work here!

    James

    Reply
    1. Alex Shlega Post author

      Hi James,

      Thanks.. and yes, you’ve figured it out:) Used to be a look-up setter, but you can really use it to set other attributes types, too, so got renamed along the way.

      Reply
  6. Joe

    Hey there.

    This looks to be just what I need, but I have a question.

    I have a record called “Telepsych”. It is not a direct child of an “Account”, but they are connected in a way.

    I have a lookup on the “Telepsych” Record back to the account called “Located Within”. When I set the Account association to the “Located Within” field on the Telepsych, I need this to update the Account “Telepsych Lookup” lookup field with the value for the Telepsych record I just added the account on. Users who work with the Telepsych records do not visit Account records often, so I need to update the Account value automatically somehow.

    Right now, it errors because it cannot seem to find the right entity to update. It automatically looks at the wrong one. That might be my issue, and part might be because the schema name of that field is the same on mutiple different entities.

    Basically, I want to use this to update a lookup on the Account record when I update a lookup on the Telepsych record.

    Any idea what I can do, or do you need more information?

    Thanks!

    Reply
    1. Alex Shlega Post author

      Hi Joe,

      can’t you use a simple workflow (on update of the Telepsych record)? If I understand you correctly, you have too lookups:

      there is a lookup from Telepsych to Account
      there is a lookup from Account to Telepsych

      So you can probably set up a workflow to run on update of that first lookup (on the Telepsych entity) – just add an update step to update the account and set Telepsych lookup on that account record.

      What am I missing?

      Reply
      1. Joe

        Alex-

        Thanks for your help.

        I re-looked into this with fresh eyes and found my mistake. I was looking at it the wrong way. I ended up getting what I needed. I over-thought it.

        Thanks!

        Reply
  7. Donal

    Hi – thanks for this – really useful.

    Is there an easy way to get this to create a new record in the target entity if it does not find a match?
    If not, I’m thinking:
    1. Create a workflow that watches for changes in the search source field
    2. Have it wait a minute.
    3. If the lookup field is blank, create a new record.

    Reply
    1. Alex Shlega Post author

      Hi,

      you might just add that extra step directly to the workflow – have “Attribute setter” step run first, then check if the field is still empty, and, if yes, create the lookup and update your record with that lookup

      Reply
  8. Jojo

    Wow Alex,
    it’s about time someone got up with these goodies, way to go!
    But, we won’t be upgrading to 8.2 for some time. Could you pretty please send me the 8.1 version of TCS Tools?

    Thanks a million!
    Regards,
    Roy

    Reply
      1. Jojo

        Hey Alex,
        Thanks for responding this quick. I managed to download (ofcourse), but import fails because of the absense of msdyn_/Images/AppModule_Default_Icon.png, so I can’t really test it.

        Any chance you’d provide/share that one webresource?

        Really, really thanks!

        Reply
        1. Jojo

          nah, got that part working. Import was succesful.
          Haven’t got it working yet, but I’ll keep trying.

          So far, thank you very much, Alex!

          Reply
  9. Jojo

    Hey Alex,
    I’ve encountered a rather weird thing. I got your fantastic solution up and running. Now ‘all of the sudden’ (yeah, I know, there’s no such thing) it stopped working.
    I recreated a very straight forward situation, but it still doesn’t do a thing at all.

    I’ve tested the XML by copy/paste in XML Builder, works as a charm. Fields exist, but the target attribute just doesn’t get set.
    Do you have any idea what I can do to debug this any further?

    thank you,
    Roy

    Reply
    1. Jojo

      shoot! Recreated it in a brand new vanilla environment and works as a charm again…

      Reply
  10. Mark Louies

    Hi guy,

    Anyone of you encountered. Not able to find the “Lookup configurations” in the dropdown on step 2?

    Regards,
    Mark

    Reply
  11. Mark Louies

    Haha found it ! entity name was changed into “TCS Lookup Configurations”

    Reply
  12. Andrei

    Hi!
    Sorry, I don’t really get it. Could you help me?
    I have an salesorder entity and custom salesorderdetail. I need to calculate total price for all records in salesorderdetail. The field “order_total_price” is placed at the salesorder entity. Is it possible to do using TCS Tools?

    Reply
    1. Alex Shlega Post author

      Hi Andrei,

      did you see this link? http://www.itaintboring.com/dynamics-crm/lets-roll-up/

      In the example there, you would replace “case” with “salesorder” and “notes” with “salesorderdetail”. You would need an on-demand workflow on the “salesorder” entity that would be using fetch (instead of “count”, use “sum”) to calculate the total, and you would need an on-update/on-create/on-delete workflow on the salesorderentity to call that on-demand from the salesorder. For the aggregations in FetchXml, have a look here: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/org-service/use-fetchxml-aggregation

      Reply
    1. Andrei

      Could you please help me to build the right Fetch XML? It seems like it’s the final step to rich the goal.

      Reply
      1. Alex Shlega Post author

        Hi Andrei,

        you need this kind of fetch(it’s one of the screenshots from the link above): http://www.itaintboring.com/wp-content/uploads/2017/08/image-10.png

        The idea is that you need to select all salesorderdetail records for that salesorder and calculate the sum. Notice how “count” is an aggregate function on the screenshot above, and it’s also an alias for the attribute (can give it a different alias name, but that’s what goes to the fetch result attribute). In your case, it’ll be “sum”. Literally you just need to take the fetchxml there and replace annotation (note) with salesorderdetails, incidentid with #salesorderid#(or whatever the lookup field name is in your case), count with sum..

        Reply

Leave a Reply

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