This would not be a validation. It would either be an auto-enter calculation or a script that would be able to start with the last letter entered and remove each non alphabetic character until the first alphabetic character is reached.
If you have FileMaker Advanced, this can be done with a recursive custom function.
Without Advanced, you might want to use the OnObjectSave script trigger (if this is a box where you type in this text) to perform a looping script to do the same.
If you would like an example of either the script or custom function, let me know which one you would like to see and I'll post it.
Thanks for the response. I think I like the auto-enter calculation route. I understand them and have been using them a lot to clean up inbound data. Thanks.
But this option only works if you have FileMaker Advanced so that you can create and use a custom function in that auto-enter calculation:
Your auto enter calculation would look like:
but in Manage | custom functions you would create this recursive calculation:
//StripTrailingPunc( String )
//Removes any trailing punctuation characters
Let ( [ L = Length ( String ) ;
StringLeft = Left ( String ; L - 1 ) ;
RightChar = Right ( String ; 1 )
Case ( IsEmpty ( String ) ; String ;
Filter ( Upper ( RightChar ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) = RightChar ; String ;
StripTrailingPunc (StringLeft )
) // Case
) // Let
I misunderstood. I have FM Pro not advanced. Is there a place I can put this code? Or other way to solve this problem?
This custom function requires FileMaker Advanced to define in the file. Once defined, the function will work when the file is opened with either advanced or filemaker pro.
As I stated in the first post, if you do not have advanced, your best bet is probably to set up a looping script that loops through these characters from right to left,deleting characters until it reaches the first alphabetic character and stops.
Bits and pieces of the above function can be used in different script steps, but the script, over all, will be significantly different.
Set Variable [$L ; value: Length ( YourTable::YourTextFIeld ) ]
Exit Loop If [ $L < 1 ]
Set Variable [$C ; RIght ( YourTable::YourTextField ; 1 ) ]
Exit Loop If [ $C = Filter ( Upper ( $C ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ]
Set Variable [$L ; Value: $L - 1 ]
Set Field [YourTable::YourField ; Right ( YourTable::YourField ; $L ) ]
Note: When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.