Calculated field as Unique

[bbaliner]

Hi all,

How do I specify a calculated field as Unique Primary Key? Is there a formula or a function?

To acomplish this, I wrote an embarassingly long script, that:

1. Sets a global field to equal my key field

2. Goes to next record, compares the key field with previous global field

3. If they're equal puts 0 in Unique column, and if they're not puts 1.

The problem is, this does not work, if the key field is not unique, but not sequential either. There has to be a simple way to do this, but I don't know how. Please, help.

Thanks

[ June 21, 2001: Message edited by: BikeBoy ]

[Chuck]

Try sorting the records by the key field before you loop through them.

Chuck

[bbaliner]

quote:

---

Originally posted by Chuck:

Try sorting the records by the key field before you loop through them.

Chuck

---

Thanks for your response, Chuck. By the way, your advise on the subject "Accosiate button with the field" really helped a lot, thank you!!

From your response I asume, that in FM there is no other way to ensure uniqueness of composite Primary Key (calculated field), than writing a script. I have such a field named "StudyID".

I did sort before the loop, in fact here's the script:

Enter Browse Mode[]

Sort [Restore, No Dialog]

Go to field ["StudyID"]

Go to Record [First]

# Here I defined a number field "Unique"

Set Field ["Unique", "1"]

Loop

Set Field["g_Unique", "StudyID"]

Go to record [Exit after last, next]

if [studyID=g_Unique]

set field ["Unique", "0"]

else

set field ["unique", "1"]

end if

set field ["g_Unique", "StudyID"]

end Loop

The problem with this script is that it only looks at sequential StudyID, but if StudyID is duplicated 5 records below, the script still puts "1" in the Unique field. I can't imagine there's no tried-and-true way to do this, I just don't know how.

PS How can I copy the script to clipboard and paste it into the message?

Thanks again, I appreciate your help.

BikeBoy

[Chuck]

Believe it or not, this one is in the manual. Take a look at page 10-13 in the FMP 5 manual. It's the exact script I've used in similar circumstances.

As far as copying and pasting the text of a script, the only way I know of is with a third party tool such as AutoScript.

Chuck

[AndriesV]

Assuming your file has an other unique key field such as record number which is incremented by 1 for each new record you could use this to make your script work independent of sort order.

If you don't have this unique field you could also add a calculated field like Calc_RecID = Status(CurrentRecordID) which is the Filemaker unique identifier for each record.

Now add a selfjoin - relationship on field StudyID

In the looping script set the Unique field to 0 for each record where:

Count(selfjoin::StudyID) > 1 AND Calc_RecID > min(selfjoin::Calc_RecID).

This way only the StudyID with the lowest Calc_RecID should stays unique=1

Andries

[AndriesV]

Just thinking about it, you don't need to do this with a script anymore if you create a calculated field like:

UniqueCheck = IF(Count(selfjoin::StudyID) > 1 AND Calc_RecID > min(selfjoin::Calc_RecID), 0, 1).

By adding another calculated field(s) that shows or hides data depending on the UniqueCheck, you could add this(these) Calc_fields on a FindLayout so users would be able to search records for Main/Unique StudyID's.