Love the DISTINCT function of the SQL but trying to figure out how to get the correct set of records.

In a normalized file

member ----< email

there could be one or more duplicate emails in that table.

suppose I am already on the FOUND set in Members, i want a unique list of email addresses - or rather its ID's - i want to go to the related email table keeping the first instance of an email and omitting subsequent duplicates.

I need to end up on the email table so it can export the desired unique found set, that relate to the FOUND set in members.

Savvy?

Thanks

Stephen

Stephen, you would first a script that you capture your IDs for the found set of members. ( or copy all technique, CF, calc, etc )

SEt Variable [ $$foundset; "" ]

Go to Record [ First ]

Loop

Set Variable [ $$foundset; List ( $$foundset; contact::ID ) ]

Go to Record [ Next; Exit after last ]

End Loop

Set Variable [ $$foundset; Substitute ( $$foundset; ¶; "," ) ]

Then you could use something like the following:

ExecuteSQL("SELECT MIN(ID) from email WHERE contactID IN( " & $$foundset & " ) GROUP BY emailaddress"; ""; ""; "" )

that won't work because my ID is actually is using get ( UUID )

I think it should still work but if using UUID, since it is text, you would need to to slightly alter the loop script.

SEt Variable [ $$foundset; "" ]

Go to Record [ First ]

Loop

Set Variable [ $$foundset; List ( $$foundset; "'" & contact::ID&"'" ) ]

Go to Record [ Next; Exit after last ]

End Loop

Set Variable [ $$foundset; Substitute ( $$foundset; ¶; "," ) ]

Yeah it should work. Here is a demo file.

contacts.zip

Kevin Frank"s latest BLOG has a demo file that includes some custom functions which will produce a string that is properly formatted for use with the IN clause. This might solve your problem.

Thanks Guys - this did work. I will review Kevin's CF to. however i know his has that iteration limit.

However I doubt i'd ever reach 10k iterations.