I figured it was time to reach out to the community as I'm beginning to tear my hair out over this. I need to be able to count the number of unique values in a portal based on the "participant" field. I've tried a number of solutions I found online but can't get any of them to do what I want.

Example: from the portal rows items below I want to return a count of 3 (Judy, Bette and Jim). It seems like this would be so easy I MUST be making it way more complicated then it is.

Judy

Judy

Bette

Jim

Judy

I'm including a small zip file of my data. Any chance someone could take a look at it for me? Just sign in as a Guest and you should have no problem opening the file. Thank you... mp

UniqueValues.zip

Create a Value List named UniqueParticipants, of services.PEOPLE::cFullName_First_Last (Only related values starting from _EVENTS).

Now UniqueParticipantCount = ValueCount ( ValueListItems ( Get ( FileName ) ; "UniqueParticipants" ) )

Example: from the portal rows items below I want to return a count of 3 (Judy, Bette and Jim).

Try =

Count ( Services.PEOPLE::ID_PEO_pk )

Way cool! I didn't know that Count() would work that way through a join table. But like all your other elegant solutions, so logical once it's pointed out. Thanks.

CountUnique Copy.zipI changed the context from which I need to implement this solution. I tried the above suggestion but am still doing something wrong. For some reason my uniqueParticipantCount = 0 on all the records and the cCountUnique is really funky:

On Bette's record the cCountUnique is showing as 7 and it should be 2 uniques (Connie & Deb)

Bill's result is correct... 3 uniques (Nancy, Mary & Michael)

On Bruce's record the cCountUnique is showing as 2 and it should be 3 uniques (Deb, Lucy and Connie)

Any ideas where I've gone wrong?

See if this makes it clearer:

CountUnique3.fp7.zip

Is there I way that I can do this so I can still have the date that corresponds with the person included the portal?

I take it back (I was thinking about a different post I had sent). This unique count is exactly what I was looking for. Thank you so much for your help with this. I do appreciate!!! :laugh2: