5 Replies Latest reply on May 6, 2014 2:24 PM by philmodjunk

    Auto Field Validation to Strip out trailing punctuation

    bokenrosie

      Title

      Auto Field Validation to Strip out trailing punctuation

      Post

           I need to remove trailing punctuation on a field.

           If I have Company, LLC that is okay but Company, LLC., is not okay, I would need to remove the ., at the end of the field.

           Since there are some places where ., are valid I cannot use Filter.  What else can I do?

           Thanks

        • 1. Re: Auto Field Validation to Strip out trailing punctuation
          philmodjunk

               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.

          • 2. Re: Auto Field Validation to Strip out trailing punctuation
            bokenrosie

                 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.

            • 3. Re: Auto Field Validation to Strip out trailing punctuation
              philmodjunk

                   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:

                   StripTrailingPunc(Self)

                   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

              • 4. Re: Auto Field Validation to Strip out trailing punctuation
                bokenrosie

                     I misunderstood.  I have FM Pro not advanced.  Is there a place I can put this code?  Or other way to solve this problem?

                      

                • 5. Re: Auto Field Validation to Strip out trailing punctuation
                  philmodjunk

                       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 ) ]
                       Loop
                           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 ) ]
                       End Loop

                       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.