1 Reply Latest reply on Jun 3, 2015 10:45 AM by philmodjunk

    Script to validate if a calculated field entry is unique  (FM14)

    JamesRudge

      Title

      Script to validate if a calculated field entry is unique  (FM14)

      Post

      Hi,

      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”]
      Halt Script
      End If

      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.

      Many thanks,

      James

        • 1. Re: Script to validate if a calculated field entry is unique  (FM14)
          philmodjunk

          Setting the field options to Validate for Unique Value is not ideal as you cannot directly modify this field in the form.

          But your script can detect the validation error and handle the error instead of just letting field options display the validation error message.

          You can also use either a self join relationship that links two occurrences of Table 2 by this ID field to count the number of matching records as a way to detect a duplicate.

          A SQL query inside ExecuteSQL could also be used.

          But please note that your ID is not the ideal ID to use in order to link records in a relationship. It would be better to use an auto-entered serial number or the text returned by Get (UUID ) for that purpose. If you have to use this ID to satisfy other requirements, set it up as a field in your table so that searches, sorts and labels can use it, but don't use it as your primary key in relationships.