In a project table, I have an unstored calculation field that returns the name of all the employees who have entered timesheets related to a specific project.

The names are separated by comas and are gathered together with a custom function that makes use of the new GetNthRecord function via a relationship (thanks to John Mark Osborne for this one).

Because employees could have entered several timesheets for the same project, their name can appear several time. The result of the calculation looks like : Robert Smith, Mark Lambert, Louise Mitchell, Dave Richards, Robert Smith, Dave Richards, Mark Lambert, Dave Richard.

I would like to eliminate duplicate names and end up with only one occurrence for each employee. Ex: Robert Smith, Mark Lambert, Louise Mitchell, Dave Richards.

I would like to do it without using a script, only with calculations.

I initially thought I could add an IF statement in my custom function in order to return the name of an employee only if it is not already there, but I have struggled to do it so far.

My second option would be to build a another calculation that parses the first calculation and get rid of duplicates, with the use of Substitute and PatternCount function... But I'm also struggling to do it...

If someone understands what I'm trying to do, I would appreciate a little help !

Thanks

I'm out of my depth here, but I've read in other threads that you can use the ValueListItems function to get a list of unique entries in a field.

HTH,

David

Yes, a value list eliminates duplicates.

Alternatively, if I am guessing correctly that your Timesheets table is joining Employees to Projects, you can simply point your exisiting function to the Employees table rather than to Timesheets.

Sorry for the delay, but I just wanted you to know that I used the Value List option and it works just fine. I don't know why I didn't think of using it in the first place. I guess I wanted to use the new GetNthRecord so badly that I forgot to look at other more simpler options !

Thanks again