Thank you for your post.
The Filter() function allows you to filter out unwanted characters. Since you want to "strip unnecessary spaces, numbers, and punctuation other than comma, apostrophe, hyphen, space (huh?), and parentheses", the calculation would be:
Filter (<field name>; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz,'-()" )
Any other characters not in this text string are ignored. For example, if in your field you had:
Dr. Jones %56
The result would be:
.... because the period is ignored, the spaces ignored, the percent symbol ignored, as well as any number.
Since you are removing spaces, I'm not sure how you would retrieve the first and last names. If the first letter of the first and last names are capitalized, you could use the Filter function to filter only uppercase characters so you could retrieve the first letter of the last name. Somehow, you would need to determine what works for you.
If you have a calculation that returns the first name and last name, the calculation to combine the two fields would be:
Last Name & ", " & First Name
If you are doing this all within a script, then you can use "Set Field" script step to assign a value to a field.
If you have a specific example, perhaps that would help more.
Thanks for responding, TSGal. I tried to reply to this before, but I don't know that it posted, so here goes, again.
I am not having a problem with the filtering. I want to keep interior spaces, but trim them at the ends. The Trim() function does fine for that. The filter function is also working as I expected it to. The problem is that once I have the name trimmed, filtered, and checked, and separated into first and last names, I want to create a name that is the concatenation of last name & ", " & first name. I can do that too. But I cannot seem to store it in the same record that the name was entered into in the first place. It doesn't show up there, but if I do ctl N for a new record, then it shows up.
The following is the code I have in the calculation part of the validate by calculation option for field PNtest. I would like the validated name to be in field PN. I use the global variable $$PN to pass that value back, since the result of the calculation must be boolean to satisfy the validation. OK is that value.
Let ( [X=Trim (PNtest ); $FN=""; $LN=""; Y=Filter ( X; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz '-()," ); PC=Position (Y; ","; 1; 1); $LN=Case ( PC>2; Left ( Y; PC-1 ); "" ); $FN = Case ( Length ( Y ) > (PC+1); Right ( Y; Length ( Y ) - PC ); ""); OK=( Length ( $LN ) > 0 and Length ($FN ) > 0); $$PN = Case ( OK; $LN & ", " & $FN; "" )]; OK)
Thanks again for your help.
Well, I have written a script which is virtually the same as the calculation field. The difference is that in the script I set the field PN equal to the Let function which returns the "good" name. In the validation calculation, the value returned had to be boolean, so I couldn't set the PN field directly.
I think this solves the problem.
Thanks for your time and effort.
Looking at this problem, I don't think you need to validate the data so much as to replace it with a calculated result. (This also means that you don't need a script) This can be done in the auto-enter options for the PNTest field. Open the Options dialog for the field and on the Auto-Enter tab choose Calculated value. Enter the fololowing:
Let ( [
Y= Filter ( X; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz '-()," );
PC= Position (Y; ","; 1; 1);
LN = Trim ( Left ( Y; PC - 1 ) ) ;
FN = If ( PC > 0 ; Trim ( Right ( Y; Length ( Y ) - PC ) ) ; "" );
OK = ( Length ( LN ) > 1 and Length ( FN ) > 0 );
PN = If ( OK; LN & ", " & FN; "Invalid name" )
Click OK. Then in the options, UNCHECK the 'Do not replace existing value of field (if any)" checkbox.
When you enter a name into PNTest, it will be corrected as per the calculation when the user tabs out of the field. If the name does not match the criteria (OK = 0), then it will be replaced with the string "Invalid name".
Thanks for your help, David. This is what I needed. The only thing is that autoenter doesn't set the PN field (local variable, yes, field, no), but it does reset the PNtest field. Since PN is the field I'm interested in anyway, I just set the autoenter for that field to the Let function as you described.
Thanks so much for the info. I think this solves the issue, but I don't know how to set the solved flag here in the Forum. Maybe you do...
Can somebody please answer my question related to the topic:
Is it possible to conditionally format a field refering to whether it validates as a member of a value list?
e.g. If I deliberately choose a non-existent value from a value list,I need to format the field in red colour in order to spot it easily.
Great! Thanks a lot...