You'll need a script that checks the field against the conditional value list and clears the field if it is not a member. An OnObjectSave script trigger on the catetory field can perform a script such as:
Set Field [YourTable::Tools ; IF ( valuecount ( Filtervalues ( valueListItems ( Get ( FileName ) ; "ConditionalvalueListNameinQuotes" ) ; YourTable::Tools ) ) ; YourTable::Tools ; "" ) ]
I am fiddling trying to make sense of this. Took your IF statement and included it as a Valadated by calculation string in the Items field. It pops up error message correctly, but does not clear field.
I tried same in the category field prior with similar results. Any thoughts on how to clear the Item field please? Or where/how to embed full script in category field? For info my Order Table has fieldname:TypeO for category and ItemO for field. Value list is "Name of Items"
This is not a validation calculation. Validation rules cannot not modify data in other fields--only throw up an error message and ask if you want to revert.
You have to use a script trigger to perform this as a script.
PhilModJunk. With a manual process (click on Scripts), I can get the full script to clear the correct record cells, but don't know how to automate wtih OnObjectSave. Can you please assist?
This requires FileMaker 10 or 11.
Use Manage | Scripts to create the one line script I first posted.
Enter layout mode on the layout where you have these fields.
Right click the field and choose script triggers.
Click the OnObjectSave trigger's check box and use the specify button to find and select this script we just created.
Thansk very much, problem addressed.