Find unique !

[Anh_Tran]

Dear All, Do you see below how find unique does not work?

enter find mode[]

Set[field,!]

show omit

Perform Find

let'say I have

1

2

3

2

3

...I want to to find

1

2

3 ...

since it is only unique value

I want o show only unique value. Someone has any ideas?

Thanks

[danjacoby]

And with 1,2,3,2,3 it's probably just showing 1, 'cause what you're doing is showing only records that are not duplicated.

This is almost it:

ShowAllRecords

Sort

GoToRecord (first)

SetField (gTest, Field)

// where "gTest" is a global field

GoToRecord (next)

Loop

If Field=gTest

Omit record

Else

SetField (gTest, Field)

GoToRecord (next)

EndIf

EndLoop

The only problem is that it omits the last "unique" record; there is a way to stop the script before it does that, but I'm too tired to figure it out right now -- perhaps someone more awake than I can finish this.

[falkaholic]

There is way to optimize this, before you go through the loop. Do this.

Make a self-relation -- a relation to the same field in the same file.

Then you can use something like this in a calculation

if [ count( the_self_relation::the_field ) = 1 ]

show message [ "this is unique!"]

End if

Then don't bother with the loop if its not already unique.

Idea #2

Useing the above self relation calculation (say that 10 times fast), have a calcuations field that does the above calc like this.

uniquecalc = if ( count(self_relation::field) = 1, "Unique", "Not")

then run a find for "unque" on the calculation field.

You might not want to do this because if you have alot of records in your database, it will be slow.

[kennedy]

let'say I have

1

2

3

2

3

...I want to to find

1

2

3 ...

since it is only unique value

I want o show only unique value. Someone has any ideas?

In other words, you want to effectively remove all duplicates.

Do a search through these forums on "remove duplicate"... you'll find

dozens of posts discussing different approaches for different situations.

If you just want a listing of the unique values in a particular field, make sure the field can be indexed and simply generate a value list from the field.

[Anh_Tran]

In reponse to danjacoby , I found the solution, create the summary field to calculate the total record number

says totalrecord (summary of count)

ShowAllRecords

Sort

GoToRecord (first)

SetField (gTest, Field)

// where "gTest" is a global field

GoToRecord (next)

Loop

If Field=gTest

Omit record

exit loop if (status(currentrecordID ) = totalrecord)

Else

SetField (gTest, Field)

GoToRecord (next)

EndIf

EndLoop

I works fine

Thanks All

[Anh_Tran]

Dear All,

The code above works only for numbers but not for text ...I try on text fields does not work and also

Falkaholic, I really don't get your point ...do you have example I could see ...since your logic is for unique only but I mean duplicate value appears once also.

If u can have a file, I love to see it

Thanks all

[Anh_Tran]

I know why my code does not work ..that is currentrecordnumber ...not currentrecordID

take me too much time to trach error

Thanks all ...THe idea of danjacoby is good but I don't really understand ...

[CobaltSky]

I know why my code does not work ..that is currentrecordnumber ...not currentrecordID

You can use:

If ["Status(CurrentRecordNumber) = Status(CurrentFoundCount)"]

however:

If ["Status(CurrentRecordID) = Status(CurrentRecordCount)"]

will not work if any records have been deleted, nor if the total number of records exceeds 127 (ref FMI Kbase article #104663).

Usually, a solution for exiting a loop when the last record is reached, is to enable the "Exit after last" option which is available for the Go to Record/Request/Page [Next] script step (it appears as a checkbox at the left of the options panel at the bottom of the ScriptMaker window when the next option is selected for the Go to Record/Request/Page command).

That will only work when moving to the record with the Go To Record Step however, and though suitable for the second part of the If [ ] sequence proposed by Dan, will not solve the problem which he alluded to which can occur if the loop arrives at a record through the action of an Omit Record step rather than through a Go To step.

Thus a watertight version of Dan's suggested script would run along the following lines:

ShowAllRecords

Sort [Restore, No Dialog]

Go to Record/Request/Page [First]

Set Field ["gTemp.txt", "Field"]

Go to Record/Request/Page [Exit after last, Next]

Loop

If ["NumToText(Field) = gTest"]

If["Status(CurrentRecordNumber) = Status(CurrentFoundCount)"]

Set Field ["gTemp.txt", "Field"]

Go to Record/Request/Page [Previous]

If ["NumToText(Field) = gTest"]

Go to Record/Request/Page [Next]

Omit Record

EndIf

Unsort

Go to Record/Request/Page [First]

Exit Script

EndIf

Omit record

Else

SetField ["gTest", "Field"]

Go to Record/Request/Page [Exit after last, Next]

EndIf

EndLoop

Unsort

Go to Record/Request/Page [First]

This will work for both number and text fields.

With a few minor adjustments, plus inclusion of the GetField( ) function, the same script can be used to locate unique values for either of two or more fields (selectable by the user).

If you are interested, I am attaching a working demo which shows this functionality, where you can select either a text or a number field (from a pair of radio buttons) and then run the script - and when you do, the script locates the first occurrence of each unique value in the field specified.

The demo is unlocked so you can pull it apart for a look see if you wish. It is also available for download from my website at:

http://www.nightwing.com.au/FileMaker/demos/FindUnique.zip

FindUnique.zip