7 Replies Latest reply on Jan 21, 2016 7:03 AM by HelderCardoso

    Script for alerting to existing value

    HelderCardoso

      Hello,

       

      I would like to have an alert when I input one value in a field and if that value already exists in another field, I would like to see one alert with "This value already exists! Please review the record". But I would like to keep a repeating value if I choose to, so I should not use the Unique Value.

       

      Any ideas to solve this?

       

      Many thanks for your help and time.

       

      With my best regards,

        • 1. Re: Script for alerting to existing value
          mikebeargie

          Script it with a trigger for OnObjectSave on the field.

          Commit Record [ no dialog ]

          Set Variable [ $count ; ExecuteSQL("SELECT COUNT(*) FROM yourTable WHERE yourField =?";"";""; yourTable::yourField) ]

          If [ $count > 1 ]

            Show Custom Dialog [ "VALUE ALREADY IN ANOTHER RECORD" ; "Proceed" ; "Cancel" ]

            If [ Get(LastMessageChoice) = 1 ]

               Do stuff here, user accepted.

            Else

               Set Field [ yourTable::yourField ; "" ]

            End If

          End If

          • 2. Re: Script for alerting to existing value
            HelderCardoso

            Thank you Mike!

             

            But I´m still starting on FM, could you please explain me a little better this part?:

             

            "Set Variable [ $count ; ExecuteSQL("SELECT COUNT(*) FROM yourTable WHERE yourField =?";"";""; yourTable::yourField) ]"


            Thank you for your patience.


            With my best wishes,

            • 3. Re: Script for alerting to existing value
              mikebeargie

              That creates a variable ( $count ), which is a count of all of the records ( in yourTable, use your table name here ), where the field ( yourField, use your field name here ) matches the value you just entered ( replace yourTable::yourField with the field you are triggering).

              • 4. Re: Script for alerting to existing value
                Extensitech

                On the validation tab of the field options:

                • check "unique value"
                • leaved checked the option "allow user to override...",
                • use "display custom message..." to enter a yes/no question, like "This value already exists! Are you SURE it is correct?"

                Because you are allowing override, the custom message will display with yes and no buttons. If they click Yes, the non-unique value will be saved. If they click No, it won't.

                 

                Nothing wrong with the other solutions, and they may be appropriate, but have you considered this most basic approach?

                 

                Chris Cain

                Extensitech

                • 5. Re: Script for alerting to existing value
                  mikebeargie

                  I've found from a user perspective (with at least three clients now), the "revert" button on the system validation messages causes more confusion than it's worth.

                  • 6. Re: Script for alerting to existing value
                    Extensitech

                    A valid concern, but I'd still consider the simple approach.

                     

                    I've had a couple clients who couldn't wrap their minds around the fact that entering "12" in a field displayed as percent gives you 1200%, so I've used auto-enters and other methods to allow them to enter 12 when they mean .12. So, yes, sometimes you have to go further if your users need it. I wouldn't say that percentages as-is don't work, though, or skip over considering just using them as-is.

                     

                    Like I said, all good options. There's a good chance that one of those more advanced options is more appropriate, too.

                     

                    Since the OP is "just starting on FM" and has made the blanket assumption that "I should not use the Unique Value", it occurred to me that we might be going straight to more advanced (and arguably, more elegant) solutions, but skipping the obvious.

                     

                    I use some alternate methods for field validation, too. For a small solution, though, for a few users who either know what "revert" means or can just be taught, "revert field" might not be a problem worth additional work.

                     

                    One might also include in the custom menu "(click 'Revert Field' to remove the duplicate value)".

                     

                    Chris Cain

                    Extensitech

                    • 7. Re: Script for alerting to existing value
                      HelderCardoso

                      Thank you for the good options and explanations you gave me! I will try them .

                       

                      With my best wishes,

                       

                      Helder