4 Replies Latest reply on Nov 4, 2011 4:23 PM by philmodjunk

    "Not In List" event [script]?



      "Not In List" event [script]?


       Have numerous drop-down lists and popup menus.

      In Access, when you enter something into a validated field that is not in the drop-down list, it becomes an "event" entitled "Not In List".

      I attached vba code to that event that ..

       - Opens a dialog box asking if you really want to add that entry to the list.

       - If you click OK, the code opens a form that would "feed" the list, where what you initially typed is added as a new record.


      Works perfectly when say, you don't have a city listed.

      ... Just opens the "Cities" form, inserts a new record [CityID or in FM terminology __kp_CityID] and copies the city name into the appropriate text field City.

      Is there an equivalent to this routine in FM Pro 11 ...?


       - Mike

        • 1. Re: "Not In List" event [script]?

          You can access the items that make up your value list with the valueListItems function.

          You can use FilterValues to compare the value in the list to the items in the value list.

          Put those together with the OnObjectSave or OnObjectValidate trigger and a script can use those two functions in combination to determine that the data in the field is not in the current value list. At that point, the script can take the value in the field and use it to create a new record in the table that serves as the source of values for the value list.

          • 2. Re: "Not In List" event [script]?

             Thanks Phil.

            As a newbie to FM, I *kind of* get what you're saying, but would need considerably more assistance to implement.

            Any chance you could specifically outline the above..?

            Thanks again.

             -  Mike

            • 3. Re: "Not In List" event [script]?

              Actually, there may be another way. I've Got Filemaker crunching a lot of data right now or I'd run a quick test to see if my other idea would work

              Here's the original concept:

              You'd need to define a table for the values in the value list. You can then use the specify field option in Manage | value lists to set up your value list to display data from this table. With this approach, you can add/remove values from your value list by adding/removing the record that provides that value to the list.

              Let's assume you name that value list: MikesValues.

              Then valueListItems ( Get ( FileName ) ; "Mikesvalues" )

              will return the list of values defined in that value list.

              In an If step,

              IsEmpty ( Filtervalues ( valueListItems ( Get ( FileName ) ; "Mikesvalues" ) ; YourTable::DropDownField ) )

              will evaluate as True if the value in DropDownField is not in the value list.


              My other idea, which would be simpler, but I can't test it at this moment, would be to set a validation rule on the drop down field requiring that the value in the field be a member of the value list. The script would then use:

              Set Error Capture [on]
              Commit Records
              If [Get ( LastError ) = //look up correct error code for a validation error and put it here ]

              To detect the fact that the value in the field is not in the value list.

              • 4. Re: "Not In List" event [script]?

                Finally got to test my second idea. The following script worked when performed from the OnObjectValidation Script Trigger:

                Set Error Capture [ On ]
                Commit Records/Requests
                If [ Get ( LastError ) = 506 // value is not in value list ]
                       Show Custom Dialog [ Message: ValueListValidation::NameDropDown & " is not in value list. Add it to the list?"; Buttons: “Yes”, “No” ]
                       If [ Get ( LastMessageChoice ) = 1 //yes was clicked ]
                               Commit Records/Requests [ Skip data entry validation ]
                               Freeze Window
                               Set Variable [ $Value; Value:ValueListValidation::NameDropDown ]
                               Go to Layout [ “valueTable” (valueTable) ]
                               New Record/Request
                               Set Field [ valueTable::Name; $Value ]
                               Go to Layout [ original layout ]
                       End If
                End If