### Title

Produce List of UniqueIDs in found set.

### Post

I'm looking for a function that can do the equivalent of SQL's Select Distinct.

For example, I have a table with SalesLineItems. If I want to produce a summary table for sales by product for a given week. In SQL I would start from selecting the distinct Part IDs where date range is between A and B and then joining the sum of qty. I might want to store the return separated list as a variable of global field and then work from there.

This is only one of the things I want to use it for, and while there might be a better way to achieve this in filemaker (input appreciated), I really want to know how to produce the equivalent of Select Distinct.

I found a Custom Function that can produce the list!!!!!!!!!!!

I'm one happy camper!

The link is http://www.briandunning.com/cf/1093

The function is called GetFoundSet.

Just in case the link breaks sometime, here is the function:

/* GetFoundSet ( FieldName )

NOTES:

1) Fieldname MUST be in quotes

2) For related fields, use entire field path (i.e. "TO::fieldname")

3) For local fields (current found set), use field name only (i.e. "fieldname")

Author: Howard Schlossberg, FM Professional Solutions

*/

Case (

IsEmpty ( $$RecCount ) ; Let ([

$$RecCount = 1;

$$TotalCount = Case(PatternCount(fieldname; "::"); Count(Evaluate(fieldname)); Get(FoundCount));

result = GetNthRecord ( Evaluate(fieldname) ; $$RecCount )];

Result & "¶" & GetFoundSet(FieldName)) ;

$$RecCount >= $$TotalCount ; Let ( $$RecCount = "" ; "") ;

Let ( [

$$RecCount = $$RecCount + 1;

result = GetNthRecord ( Evaluate(fieldname) ; $$RecCount )

];

Result & "¶" & GetFoundSet(FieldName) )

)