Custom field to get next record id in the same recordtype

Question

hi, 
 I'm trying to create the custome field as screenshot below. 
 my goal is for the same task, preparer cannot be the reviewer. so I need the ID so I can filter the record out based on loggedInUser() 
 the criteria: same date, same task, different Stage 
 also it would be easier to change the status from Preparer to Reviewer. 
 Thanks,

Andrew Hickingbotham · Answer

If I've understood correctly you have a task table (In this example, taskId equals 1) and then off these tasks there are stages. The preparation and review stages need to be completed by different people but it sounds like the same group of people can both be preparers or reviewers but they can both prepare and review the same record. 
 Your objective is to not show the review stage to someone who has completed the preparation stage. 
 Assuming there can only be one preparer (ie. there aren't multiple preparation stages that can be completed by different people) then I *think* you could use the Aggregate Related Record Fields. I'd really need to play around with it to make sure but you could have a look. 
 
 The idea is you're saying give me the minimum of the stage ids associated with this task given the stage name is "Preparation". If it's guaranteed that there is only one preparation stage then you simple get the primary id of the preparation stage. 
 To reiterate, I'd really need to test if this works (ie. Now that you have the stage id of the preparation stage how do you translate into getting the username of who completed it. You can't aggregate in the same way to get the text value) and I'd question how this performs but it's a potential path to go down. I'd need to know much more to give advice on what a good way of doing this is!

Mathieu Drouin · Answer

I'm pretty certain there is a simpler way to achieve what you want (to ensure the preparer cannot be the reviewer). Why not just put a rule in place that gets all the tasks for that task ID and ensures that the user in the task is not in the list of previous assignees?

Konduru Chaitanya · Answer

I don't think this is something you should be doing on the custom record field. This should be handled while you are creating the records and you can simply check the value against the Reviewer. Also I would like to know if for a different task number(Expample: Task column has the value 2) can the Reviewer mentioned here for the Preparation stage can be the reviewer for Review stage?