Finding unique records based on single field

[BostonBuckeye]

I have a DB of people that came from Excel. I have multiple records for the same person each with differing other fields - e.g., relative. So

Lets say I have 7 records with 5 fields each

First - Last - ContactFirst - ContactLast - Relationship

John - Brown - Paul Brown - brother

John - Brown - Nancy Silver - sister

Ted - Steel - Sue Steel - mother

Ted - Steel - John Steel - father

Jim - White - Carol White - wife

Kevin - Silver - Harry Silver - grandfather

Kevin Silver - Jane Black - sister

So, how do I create a script/ calculation that only returns the 4 unique records based solely on FN-LN

I want only to get these 4 records

John Brown

Ted Steel

Jim White

Kevin Silver

[efen]

Are you sure your skill level - "Advance[d]" - is correct if you need to ask this?

[BostonBuckeye]

OK - Intermedate - so can you help me?

[efen]

Intermediate?

Use your combined first name and last name as a unique ID field if you don't have any other.

Create a global text field - Global

Create a text field - Mark

Create a script

pseudo code

Find all records

Sort by your unique ID field

Go to first record

Set field [Mark, """"]

Set field[Global,firstnamelastnamecombined field]

Loop

Go to next record [Exit after last, Next]

If [Global = firstnamelastnamecombined field]

Set field [Mark, ""X""]

Else

Set field ["Global",firstnamelastnamecombined field]

End if

End loop

Perform find [restore]

Delete all records

[check before you actually do the last step]

[comment]

What skill level is required to realize you cannot mark records in a multi-user solution?

[comment]

Perhaps you should explain what you hope to achieve (one-time cleanup, or display only, or ...?).

[efen]

Have I missed something? Where does it state that it is a multi-user solution?

[comment]

Where does it state it isn't? A matter of good practice.

[BostonBuckeye]

I am fairly proficient in FMpro and in Access. In Access, in a query I can select "Unique Fields", "Unique Values" based on the fields I select. That returns only those unqique records. So, in my example, the query returns only those 4 records

I was hoping something like that was available in FMpro in a find. For some reason, the FMpro developers saw to it to provide a "!" operator ONLY returns duplicate records based on a specific field. Perhaps they can introduce a new operator - say "!!" that returns unique records based on a field.

I understand the script code on how you have to loop through each record (I have over 1200), set a flag and produce that set of unique records. I have done such a thing in VBA but I was really hoping to avoid this - but I guess I can't.

[efen]

OK, thanks - I personally don't work with multi-user files so don't generally consider them in any answer I give unless a poster states that a file/solution is being used in such a situation; I also have little idea or experience of what is good or bad practice in multi-user situations.

[comment]

By "good practice" I meant always assume the solution CAN be multi-user - even if it isn't now.

[winklestork]

The "single-user" code above isn't even correct unless you want to assume Mark doesn't need to be set to empty inside the Else clause.

[BostonBuckeye]

OK...here we go

Start with

John - Brown - Paul Brown - brother

John - Brown - Nancy Silver - sister

Ted - Steel - Sue Steel - mother

Ted - Steel - John Steel - father

Jim - White - Carol White - wife

Kevin - Silver - Harry Silver - grandfather

Kevin Silver - Jane Black – sister

1. Create a calculation field (FNLN) that concatenates these FN LN fields together and produces

JohnBrown

JohnBrown

TedSteel

TedSteel

JimWhite

KevinSilver

KevinSilver

2. Create a UNIQUEFLG field to hold a mark (“x”) that signifies uniqueness

3. Create a Global work variable – WORK to hold FNLN

And now we start coding

Find all records

Sort by FNLN

Go to first record

Set field [uNIQUEFLG, "X"] ‘for first record only

Set field[WORK,FNLN]

Loop

Go to next record [Exit after last, Next]

If [WORK <> FNLN]

Set field [uNIQUEFLG, "X"]

Else

Set field [WORK, FNLN]

End if

End loop

Perform find [uNIQUEFLG, “X”]

Returns

JohnBrown

TedSteel

JimWhite

KevinSilver

[BostonBuckeye]

OK guys... here is the CODE

1) start by creating 2 working fields - UNIQ, WORK

2) create third FNLN field that concatenates the uniq field of interest together (my example is FirstNaame and LastName)

Code is as follows

Show all Records (or whatever set of records of interest)

Sort records by FNLN

Insert text (Uniq, "X")

SetField (Work, = FNLN)

Go To First record

/* Note, at this point we are in the first record of my set so I set the "X" flag. The next record may or may be the same. if it is the same we juct keep looping thru. When it is not, we set the "X" flag for that record and replace our WORK variable with that FNLN field value and we continue thru our set */

Loop

go to Next Record

if Work <> FNLN

setfield (Work, =FNLN)

Insert text (Uniq, "X")

endif

endloop

Findrequests (Uniq="X")

BINGO

2)