5 Replies Latest reply on Jun 12, 2015 1:05 AM by DilGreen

    Determine if a field entry matches value list records

    NicholasFernandez

      Title

      Determine if a field entry matches value list records

      Post

           Hi,

           I'd like to be able to check if a user has entered one of the acceptable values from a value list in a field while still allowing them to override the value list. 

           The setup: a field (Source), a value list from a field in a related table in another file (Source ID)

           I want a script that is triggered by the user leaving the Source field. It will check the data entered in Source against all of the values in Source ID. If there is no match, the script will show a Custom Dialog to ask the user to reenter the information or to add the source to our database. I don't want to automatically update Source ID because there will be times that the user will want to have a Source that should not be included in the Source ID database.

           Any suggestions?

           Thanks!

        • 1. Re: Determine if a field entry matches value list records
          philmodjunk

               Not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "YourValueListNameHere" ); SourceFieldHere ) )

               will be true if the value of SourceField is a member of your value list.

               OnObjectValidate is a trigger that can give you the option to cancel the event that tripped the trigger and you can use the custom dialog or just return them to the field to correct their data entry.

          • 2. Re: Determine if a field entry matches value list records
            NicholasFernandez

                 Okay. That makes sense.

                 Thanks!

            • 3. Re: Determine if a field entry matches value list records
              DilGreen

              This looked promising. Just tried it;

              If [ IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "ExerciseJoints" ); Left(Exercises::ExerciseJoint_AA;2) ) ) ]

              but got a failure. Turns out that because this value list (ExerciseJoints) is one populated from a field in another table, rather than a list of fixed values, it seems FPro won't evaluate the ValueListItems function (a null string is returned) - while it works perfectly for a static list of items.

              Any clever thoughts? In the meantime I am going to try to get the values out by means of calculation field external to the script.

              • 4. Re: Determine if a field entry matches value list records
                SteveMartino

                What are you trying to accomplish with  this? Left(Exercises::ExerciseJoint_AA;2)

                Also you have "ExerciseJoints" (Plural) and Left(Exercises::ExerciseJoint_AA;2) (Singular).  Just making sure there is not a typ.

                • 5. Re: Determine if a field entry matches value list records
                  DilGreen

                  Thank you for your response!

                  This is part of a validation for an exercise code built up from a number of elements (Joint, Kind, Method, etc). As the exercise is entered onto the system, I use a value list for validation for each section of the code.

                  "ExerciseJoints" is the value list of current joint codes, populated from a field in a table (thus plural).

                  "ExerciseJoint_AA" is the code segment that is chosen from this value list (thus singular). This works perfectly.

                  I use Left(Exercises::ExerciseJoint_AA;2) for the test for certainty because the value list drop down also shows values from another field, and in some circumstances seems to append the value from the second field (I know it's not supposed to).

                  What I want to do is to ensure that the code for each section of an already established code is still valid (in case the coding scheme has been changed - the users keep realising they need to refine the code system as they add new exercises). A validation script checks each code field ("ExerciseJoint_AA", "ExerciseKind_BB", etc) in turn against the current value list by means of versions of the line originally quoted.

                  I have debug lines in the script that show that everything is working, except that the result of this expression; ValueListItems ( Get ( FileName ) ; "ExerciseJoints" ) is null. A test with a static value list works fine.

                  I hope this is clear, and thanks again.