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.
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
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 :-)
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.
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.
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]
If [Get ( LastError ) //an error will occur if the selected value is a duplicate]
Show Custom Dialog ["You cannot select the same country twice for the same case."]
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.