field validation by calculation
I want to validate the format of data entered into a field, in this case patient name, using the validate by calculation option. I want to strip unnecessary spaces, numbers, and punctuation other than comma, apostrophe, hyphen, space, and parentheses. I then want to ensure that what is left has at least two letters of the last name and one for the first by creating local variables for the first and last names. If all is OK, I would like to recombine the last and first names with an intervening comma and space and place the result either in the entry field or in a different field.
Using the Let function, I can do all that, except that I can't seem to get the "good" name (ie, the concatenated last and first names) set in the same record as the entry name. It seems to get set in the next record. I set the concatenated name into a global variable, then set a field in the database equal to the global variable.
I suspect that the "good" name value not showing up until the next record has something to do with committing records, but I don't know how to handle that in a Let function. Perhaps I have to break down and use a completely separate script to do this.
Nan at Baylor College of Medicine