Filemaker value validation against value list

Question

Need to validate values within a field against Value List, retaining the value if on list but substituting a specific Value if not?

Answer 1

I am afraid you are mixing two separate things:

Validation checks if some conditions have been satisfied; if not, it throws an error. It will not correct the entry.

If you want user entry to be corrected, you need to either:

• define the field to auto-enter a calculated value; or
• attach a script trigger to it, and have the script modify the value entered by the user.

In this case, you could auto-enter a calculated value (replacing existing value) =

If ( IsEmpty ( FilterValues ( Self ; ValueListItems ( Get (FileName) ; "YourValueList" ) ) ) ; "Specific Value" ; Self )

---

--- Added in response to your clarification ---

Technically, you could run a script to find the records you want to verify and do Replace Field Contents (using the same calculation) on that field. You could run the script after changing the value list, as part of the weekly routine.

However, there are two major problems with this approach:

• some records could be locked by another user;
• you have no history of what happened, and no way to go back in case of making a mistake.

I also don't think it's good practice to have users modify a value list routinely. If you need to have a weekly list of values, you should store them in records, not in a value list. That way at least the part of the value list would have a history.

Another option you may consider is using an unstored calculation field with a similar formula. This would change dynamically with the value list, and leave the original field unmodified. This would be a good arrangement if, for example, you need to export the corrected values every week.