5 Replies Latest reply on Jan 17, 2013 10:36 AM by philmodjunk

    Entry check in portal using ONOBJECTMODIFY



      Entry check in portal using ONOBJECTMODIFY



           I have a main database and a related one as a portal. I have the portal open in a layout together with the data of the main database. I wish to validate entries in the portal on onobjectmodify, so NOT when storing the entries. It's a country code which  needs to be checked (in the 'portal' database) if it's hasn't been used before in the relationship before going to the next field.

           The main database is a 'case' database which stores several cases people work on, the related database is one with several country entries per case (EG one case (or 'trademark) can be filed in several countries.). I need to be sure a country will not be entered twice in the related database.

           How do I perform a search in the related database (portal) before going to the next field (if NOT found>move on to the next field, if found, message and the field emptied).

           The relationship from the main to the related is esthablished by a case number, but the related database has several similar casenumbers which all different country codes.

           The setup works good, but I can't seem to get a right script for searching for double country codes with (the same) casenumber.

           Wow, this description must be vague...



        • 1. Re: Entry check in portal using ONOBJECTMODIFY

               Set up a new text field in your portal's table with this auto-enter calculation. (Can't use a calculation field for this one):

               Country & "|" & CaseNumber //any expression that combines the two along with a "separator" character can be used here

               Then select the Unique Values validation option for this same field.

               Your database will now throw up an error message every time a user tries to select a previously specified country in your portal.

               Event better,

               Use a special kind of conditional value list called a Diminishing Value Lists and ExecuteSQL on your Country field. This value list will automatically update to omit any Countries previously selected for that Case.

               For an example database that uses a Diminishing Value List, see: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

          • 2. Re: Entry check in portal using ONOBJECTMODIFY

                 Thanks PhilModJunk!

                 I got that far, but got stuck there. Is it possible to trigger the error message when entering the country-code? I'm using a popup to enter country-codes (only values from the 'country' table). So how can I get the validation working when the user select a country from the list, and if invalid, popup a message and clear the field or something

                 Thanks for your code, wow, need to focus on that one to figure out what you did :-)

            • 3. Re: Entry check in portal using ONOBJECTMODIFY

                   You won't need to if you implement the diminishing value list technique. (Link describes how this is done with or without using ExecuteSQL and the demo file illustrates the method that does not user ExecuteSQL as it was created before the release of version 12.)

                   But to add a scripted error check, you can use the OnObjectValidate trigger to check this and revert the record if an error is detected.

              • 4. Re: Entry check in portal using ONOBJECTMODIFY

                     Thanks again,

                     I recently switched from FoxPro to FM (yeah, yeah, I know...) so I'm a bit of a newby here.

                     I can't seem to have the entry checked whenever a country code is selected. It only checkes whenever I leave the layout or record...sorry for the, obviously, stupid questions. It now just selects the entry and moves on, until I leave the record.


                • 5. Re: Entry check in portal using ONOBJECTMODIFY

                       This is a forum primarly intended to help out "newbies" so that's not a problem.

                       The trick is in how you check for the validation error in your script.

                       If I get every detail right, this should work:

                       Set Variable [$Row ; value: Get ( ActivePortalRowNumber ) ]
                       Set Error Capture [on]
                       Commit Records[]
                       If [Get ( LastError ) //an error will occur if the selected value is a duplicate]
                         Revert Record
                         Show Custom Dialog ["You cannot select the same country twice for the same case."]
                       End IF
                       Go to Object ["Portal Object Name Here"]
                       Go to Portal Row [no dialog ; $Row] ---Use the "by calculation" option here
                       Go to Field [Specify the next field in the portal row]
                       Exit Script [false]--this keeps the "triggering event" from being processed in "before the event" script triggers

                       To give a portal an object name, select it while in layout mode and type in a name in the Name box found at the top of the Inspector's position tab.