Script to validate if a calculated field entry is unique (FM14)
I'm trying to develop a script triggered by clicking a button to Validate an ID, which is a concatenated calculation from several other fields entered into the form (HouseholdCode-SubjectCode-RecruitmentDate). I want the validation to check that (a) the ID calculated in the form for Table2 already exists among the IDs in Table 1, and (b) that the ID is unique in Table 2.
Here's what the script I have which seems to cover part a)
Set Field [ Table2::ID ; Table2:: HouseholdCode & "-" & Table2::SubjectCode & "-" & Table2::RecruitmentDate ]
If [ IsEmpty ( FilterValues ( Table2::ID; ValueListItems ( Get(FileName) ; "Registered_IDs" ) )) ] #(where "Registered_IDs" is a value list defined by field Table1::ID)
Show Custom Dialog [ Title:"Warning"; Message: "ID does not exist. Please enter valid ID"; Button:“OK”, Commit:“No”]
However I'd like to expand the script to validate if the calculated value for Table2::ID is Unique. Setting the field options to Validate for Unique Value is not ideal as you cannot directly modify this field in the form.
If the calculated value for Table2::ID is not unique, then is it possible to just retrieve the form details for this record? (i.e. rather than creating a new record?)
I also want the rest of the fields in the form for Table2 only to appear when a valid ID has been entered, so any pointers for how to incorporate that into the script also much appreciated.
Sorry I'm a newbie so please let me know if this isn't clear.