Calculated field for checking name match with other objects

[tibormolnar]

Hi All,

I'm in need of a calculated custom field on the Task object that indicates (y/n) if the Name of the Task matches the Name of any (active) Job Role that exists in the system, but I'm not sure how to do this (or if it's doable at all). - For clarity: I not only need to look at the Job Roles assigned to the Task, but at all Roles that exist.

I appreciate if anyone can help with any ideas.

Thank you,

Tibor

[JustinRenteria2 · Accepted solution]

It's not possible for the calculated field to reference the collection of roles but there is a workaround that I just tested.  

1. Create a multiselect field on the task form containing each role as a value. 

2. Configure the field so that all roles are selected by default. 

3. Create a calculated field on the task form that checks to see if the task name is selected in the mulitselect field. I used the following: IF(CONTAINS({name}, {your multi select field}), "Yes", "No")

Be warned, the name string must match exactly to the corresponding value in the multiselect field (this includes letter capitalization). 

[JustinRenteria2]

You'll also need to make sure that the field is updated everytime you add/remove a job role! 

[tibormolnar]

Thank you for the idea, I'm going to play with it a bit.

The challenge is that we have 1000+ Job Roles and a zillion Tasks.