Constrain found set to unique values?

[Toddbob]

I would like to constrain records to only show 1 occurrence of an individual within a found set of records. (see pics for example).I perform a find by date range on table which produces result A. Now I want to constrain that set to get result B.

ie.. If John Smith shows up twice within that date range I want to constrain to only a single record for John Smith.

The idea is produce a report of Unduplicated Individuals Served within a date range.

[comment]

This is a cross-post:
https://community.claris.com/en/s/question/0D5Vy00000G1VPOKA3/constrain-found-set-to-unique-values
and the first answer you received there is a very good one.

I would add that names of individuals are not necessarily unique. You could even have two different people with the same name born on the same day and sharing the same gender. 

 

[Toddbob]

15 minutes ago, comment said:

This is a cross-post:
https://community.claris.com/en/s/question/0D5Vy00000G1VPOKA3/constrain-found-set-to-unique-values
and the first answer you received there is a very good one.

I would add that names of individuals are not necessarily unique. You could even have two different people with the same name born on the same day and sharing the same gender. 

 

I will clarify. Each individual within the table does have their own foreign UUID stored on the table next to the primary UUID for the record. So I would not be basing my constrain on "name" but on the foreign UUID. My example was to demonstrate what I am trying to accomplish in the simplest terms possible.

[comment]

10 minutes ago, Toddbob said:

Each individual within the table does have their own foreign UUID

But is there a parent table of individuals where this UUID is the primary key? There certainly should be, and all the details describing an individual  - name, DOB, gender - should be listed in this table and only in this table. 

If you do have such table then the solution is trivial (as you have been already told in the other thread). Without it, it becomes cumbersome.

 

 

[Søren Dyhr]

On 8/30/2024 at 4:49 PM, Toddbob said:

So I would not be basing my constrain on "name" but on the foreign UUID.

So it seems like being in a portal, somehow - what measures of data are we talking about here? Is it millions of records or could a filtered portals be the thing to apply here? Because Ugo's method comes to mind here... which now exists in a filtered portal version:

Quote

Let(
   [
      indexValues = List( Child::ChildID );
      searchValues = List( Child::Category );
      index =
      ValueCount(
         Left(
            indexValues;
            Position( ¶ & indexValues & ¶; ¶ & Child::ChildID & ¶; 1; 1 )
         )
      );
      prevValues = LeftValues( searchValues; index - 1 )
   ];
   IsEmpty( FilterValues( Child::Category; prevValues ) )
)

 

--sd

Edited September 1 by Søren Dyhr

[Toddbob]

This is an absolute beginner mistake and I should have seen this relationship from go.
My search table is Attendance. I needed to use the People table which should only have a record for a single individual (Primary Key) as my starting table to filter search results on the Attendance table.I was applying the incorrect logic to the issue. Started with a result and trying to work backwards to filter.
A member in Claris Community pointed me in the right direction, I was clearly down a rabbit hole of my own making.
Thank you for your replies.

[comment]

Don't be too hard on yourself. We all hit an occasional blind spot. That's why this forum exists.