2 Replies Latest reply on May 11, 2012 8:52 AM by philmodjunk

    Search column records



      Search column records


      I have a field that users enter a number that was given to them and would like a script to search the column in the record for an existing number. If the number does not exist the record is commited and the field is locked or can not be edited. I dont want this number to be changed at all. If the record does exist then it sends the user to the record in the layout. Forcing users to use the same records. This number is not the index. I have a separate index for the table.

      I am not looking to use the unique option inside field options. I was having errors when using it. Plus I would like to redirct the user to the record if it exist.

      For locking the field after it is entered and verified, will I need to create another layout that have the field with out browse mode enabled.

        • 1. Re: Search column records

          In FileMaker a  "column" is a field in a record so I assume you are searching a specific field to see if a record with the same value in that field already exists.

          First problem I see is that you don't want to create a new record and enter this number, then discover that it has already been entered in a different record in the table.

          So I would start by defining a field with global storage for the user to initially enter the number. You can define a relationship like this:

          SomeTable::gInputNumber = YourExistingTable::NumberField

          Literally any table in your database can serve for the first table. Using two occurrences of the same table, you can even use YourExistingTable on both sides of this relationship. However you decide on that, base your layout on the "SomeTable" occurrence in order to use the above relationship to check for prior use of the entered number.

          Then this script can be performed with an OnObjectSave trigger on the global gInputNumber field:

          If [YourExistingTable::NumberField //only true if a related record already exists]
             Go To Related Record [Show only related records; From table: YourExistingTable; Using layout: "YourExistingTable" (YourExistingTable)]
             Go to Layout [YourExistingTable]
             New Record/Request
             Set field [YourExistingTable::NumberField ; SomeTable::gInputNumber]
          End If

          • 2. Re: Search column records

            and the field is locked or can not be edited.

            Since you are now using a global field to enter the number originally, you can use behavior settings to deny access to the NumberField on your layout when in browse mode.