Workflow in Microsoft Dynamics CRM...accessing entities at a remove

Perhaps you could help.
Microsoft Dynamics CRM 2015 “on premise” question.

I have a Case entity which has, among other things, a many to one relationship with Users, Categories and Areas.
Essentially I want to assign a particular Case record to a particular User depending on the Category and the Area selected thereon.

To this end I created a configuration entity called Assignments which holds the mappings e.g.

Litter, Dublin, John Butler
Graffiti, Belfast, Laura Garcia
Water pollution, Galway, Joe Smith

The Assignments entity has therefore "many to one" relationships with Categories, Users and Areas.

To update the Case entity with the appropriate user in Transact SQL would be a straightforward matter but how to achieve the same in a workflow, or how to approach it has me baffled.

I also have installed a  “AGUtilities Distribute one to many” plugin if this is of any use.

Any help would be great.

Many thanks
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Feridun KadirPrincipal ConsultantCommented:
Hmm, I don't think you will able to do this with out of the box features of workflows. The problem is that you have two lookups that are used to determine the user.

If it was just say Category, that determined the user, then you could use a dynamic value in the form assistant to select the user associated  with the category. But this is not what you have.

You have two lookups to a third entity (assignment) to determine the user. I don't think a workflow is sophisticated enough to deal with this.

As an aside,  might not the same combination of category and area exist more than once as an assignment? In which case, the workflow couldn't know which one to pick.

This might work as OnSave javascript where when the case is saved, javascript runs to make a call to the CRM database that queries for the user associated with the Category, Area combination on the case. If more than one record is returned the JavaScript can decide what to do in that instance.
TableclothAuthor Commented:
Thanks Feridun,

You asked: ".. might not the same combination of category and area exist more than once as an assignment? In which case, the workflow couldn't know which one to pick."

No. Category and Area would only exist once, as a rule.

I think your suggestion to go the JavaScript route is best...I don't know yet how to get JavaScript to access a database and run a simple select but I am researching it. Possibly JQuery might lighten the load.

Many thanks!
Feridun KadirPrincipal ConsultantCommented:
Good luck! You might his link useful (if you haven't already found it)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.