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!
How do I download and try your managed solution for the lookup setter
Hi Karuna,
it seems you’ve found the link.. you can just follow the instructions on this page:
http://www.itaintboring.com/tcs-tools/solution-summary/
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
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)
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.
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.
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
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
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.
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!
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?
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!
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.
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
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
Hi Roy,
there is a year-old build for 8.1, but, right now, I don’t even have a way of testing it. You might want to give it a try: http://itaintboring.com/downloads/TCSTools_target_CRM_8.1_managed.zip
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!
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!
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
shoot! Recreated it in a brand new vanilla environment and works as a charm again…
Hi guy,
Anyone of you encountered. Not able to find the “Lookup configurations” in the dropdown on step 2?
Regards,
Mark
Haha found it ! entity name was changed into “TCS Lookup Configurations”
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?
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
I did everything exactly as you described but I have issues with building FetchXML.
I have doubts about “condition attribute” and “value”.
Here is my Fetch:
https://drive.google.com/open?id=1twxYSQc_4nce0SwAEvOPRGZ8BN6DqK5Y
Could you please help me to build the right Fetch XML? It seems like it’s the final step to rich the goal.
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..
Hi, Alex.
I need to automatically populate the Required field on the appointment form with the Primary Contact from the Account used in the Regarding field. Google guided me to your blog and I found it quite useful, thank you.
However. I need to better understand the workings of your lookup configuration tool. For my issue what would I for the Fetch Result Attribute? Also, I don’t have the “Target Attribute” field just a “Entity Attribute” one, what do I fill in there?
Kind regards,
Gerrie
Hi Gerrie,
Target Attribute is an older name for the “Entity Attribute”, so, basically, it’s the same. I am wondering if you really need to use TCS Tools, though.. You should be able to create a regular workflow to set “required” from “regarding”.. in the “set properties” window of the update step, select Regarding(Account) in the “look for” dropdown, then select “Contact” field. Unless I misunderstood what you are trying to achieve.
Hi Alex.
I use this to match Company Name to existing Accounts, and 99% of the time it works great.
The only time it seems to fail is when there are special characters such as & in the search string.
Is there any way around this?
Thanks,
Donal
Hi Donal, I think this may require an update since what’s probably happening is that, once the string replacement is done in the FetchXml, that XML becomes invalid because of the “&” character (which should have been xml-encoded). I’ll see if I can update TCS Tools (although, just to confirm, you are on v9, right?)
Hi Alex – yep – we are v9!
Thanks
Hi Alex,
We are still experiencing issues with the special characters. Ou lookup is coming back with “Invalid XML” if the company name contains an ambersand.
Was this resolved in an update? We have version 1.0.21.0 installed.
Dynamics online, completely up to date.
Thanks
Will
Hi Will,
I think I know what was the problem – try downloading a new version (same link, should be 1.0.23.0 now)
Hi Alex,
I am facing “*Invalid type for entity id value*” issue when on record update of Attribute Setter. I have verified that fetchXML is working fine and I doubt that the error is thrown on updating the record or setting the guid value to Entity Attribute.
Here is the Plugin Trace:
Plugin Trace:
[TreeCatSoftware.Dynamics.TCSTools: TreeCatSoftware.Dynamics.TCSTools.Activities.CustomActivity]
[TCS Tools: Attribute Setter]
Error Message:
Unhandled Exception: Microsoft.Xrm.Sdk.InvalidPluginExecutionException: Invalid type for entity id value.
Thanks,
Kron
Hi Alex,
Sorry for bothering you. After I read the samples for Attribute Setter, I understood how Update Direction works and my workflow is successfully working.
http://www.itaintboring.com/dynamics-crm/dynamics-crm-tcs-tools-user-a-workflow-to-update-related-entities/
Thank you and really appreciate your great tool.
?
Hi Alex,
I’m getting an error when i have a parameter that is a Date.
in the error log The date is shown in the wrong format “dd.mm.yyyy” instead of what is required for fetchxml “YYYY-MM-DD’
Plugin Trace:
[TreeCatSoftware.Dynamics.TCSTools: TreeCatSoftware.Dynamics.TCSTools.Activities.CustomActivity]
[TCS Tools: Attribute Setter]
Error Message:
Unhandled Exception: Microsoft.Xrm.Sdk.InvalidPluginExecutionException: The date-time format for 30.09.2019 is invalid, or value is outside the supported range.
I’m using 8.1 version on 8.2 system because I get an error when I try to add the latest solution form Your download http://www.itaintboring.com/downloads/TCSTools_managed.zip
Thank You
Alex, I’m having trouble getting TCS Tools to work, so I ‘m obviously misunderstanding how this is supposed to work. I have an entity called “Attendance” with a lookup attribute called “Billing Quarter Lookup” that looks up a value from the “Billing Information” entity. On the “Attendance” entity, I have a text field (“Billing Quarter Text”) that gets populated via a workflow with a text value that will match a name of a record in the “Billing Information” entity; for instance “Q1 2019-2020”. Using that text value, I want to populate the “Billing Quarter Lookup” field with the appropriate “Billing Information” Record. I created the FetchXML to find the correct “Billing Information” record- here it is:
I created the TCS Lookup Congfiguration:
Fetch Result Attribute = eol_name
Entity Attribute = eol_billingquarterlookup
Update Direction = Fetch Result
I thought that when I run the workflow on an “Attencance” record that references the TCS Lookup Configuration, the eol_billingquarterlookup would get populated with the record from the “Billing Information” entity that has a name value equal to the eol_billingquartertext attribute, but that doesn’t happen. Instead I get this error message:
eol_attendance With Id = 048d611c-f4cf-e911-8104-005056a53559 Does Not Exist If you contact support, please provide the technical details.
For some reason, the process is taking the GUID of the value from the FetchXML, and applying it to the name of the Attendance record, and not the lookup field for the Billing Information entity.
Sorry for bothering you , but your input would be greatly appreciated.
Hi Sean,
do you mind sending me your fetchXml ([email protected]) ? Off the top of my head, I think you may need to change “update direction” to “entity” (since you want to update the attendance record), but, for that, you may also need to use eol_billingquarterid attribute from the fetch for the Fetch Result Attribute (assuming your fetch is looking for that billing quarter record). So you would be updating your attendance record with the eol_billingquarterid value from the fetch result
Thanks for getting back to me, Alex. I’ll send you an email with the FetchXML now.
TCS tools installed but not able to see “Lookup Setter” as an option when I try to configure my workflow. Only “setter” I can see is the attribute setter.
Apologies for the confusion, Fredrik. Just use attribute setter instead. Originally, I called it lookup setter since it was supposed to work with the lookups, but, at some point, the solution was updated to work with other attribute types… Hence, it’s now called “attribute setter”.
Hi
Alex, I’m having trouble getting TCS Tools to work, so I ‘m obviously misunderstanding how this is supposed to work.
I have a custom entity with name Pulse. I have two fields, textofoperator1(text) and operator 1(lookup Contact).
I insert a name in textofoperator1 and run the workflow that I created with TCS Tools.
The workflow is working excellent, updates the lookup, but it finished as Failed. The error message is “Error setting attribute value for contacted. Invalid type for entity id value”. when I am changing the FetchXML, then the message changes to “new_pulse with id = XXXXXXX does not exist”.
please, can you help me. Sorry for bothering you , but your input would be greatly appreciated.
Hi Stelios, not sure what’s happening there – do you think you could email me some screenshots (to [email protected]) of how everything is configured? (the workflow, fetch, attribute setter)
LOVE this tool. Any chance you have an unmanaged solution available? and source code on GitHub?
Hi Alex~
I have your TCS Tools solution downloaded, and have followed the steps above, but I believe that I’ve missed something in the setup. I feel like this would be fairly simple, but I’ve got to have something wrong because I’m not getting the result.
I am trying to use a custom field on Leads called “Influence Campaign Code” where our 3rd party tool will populate the code.
I would like to lookup the Campaign name by matching the “Influence Campaign Code” to the OOB Campaign Code field on the OOB Campaign entity.
I’ve tried a number of ways to configure, but am not getting any results.
I can send you screenshots of how I have it configured to your email [email protected], if that is ok?
Thanks for any help you can offer!
Elaine
Alex, thank you so much for making this available to us, and for your clear instructions! I’m not a programmer and yet I was able to populate a field with a user name based on a nickname lookup field. I feel I’ve really accomplished something thanks to you!