11 Replies Latest reply on Mar 7, 2013 2:16 PM by philmodjunk

    Substitute out generic digits

    PeterMontague

      Title

      Substitute out generic digits

      Post

           I am trying to use this function to edit out ", 365 pages" and ", 123 pages" etc. Its not working. What am I doing wrong?

           Substitute ( Inventory::${item-note} ; ", \#\#\# pages" ; "" )

        • 1. Re: Substitute out generic digits
          JimMac

               If your Pattern only occurs a single time in your text field...

               Use a combo of Middle, Position and Substitute.

               If the Pattern occurs multiple times...

               Use a loop with a counter... and the above functions.

               Jim...

          • 2. Re: Substitute out generic digits
            PeterMontague

                 Substitute ( Inventory::${item-note} ;  "\#\#\# pages" ; "" )

                 Why won't the backslash in front of the hash tags work for isolating generic digits? Is there an alternative I can use?

            • 3. Re: Substitute out generic digits
              philmodjunk

                   You cannot use wild cards in substitute. It's looking for the actual text: ### pages.

                   That's going to make doing what you need here a lot more difficult. Does it work to look for " Pages" and then remove that word and the preceding word or might "Pages" appear elsewhere in this text?

                   Do you have FileMaker Advanced?

                   If so, you might want to search one of the sites that maintains a list of custom functions. You may find one that you can copy over to your solution to do what you need.

              • 4. Re: Substitute out generic digits
                PeterMontague

                     Substitute ( Inventory::${item-note} ; MiddleWords ( Inventory::${item-note} ; "pages" - 1 ; 2 ) ; "" )

                     I don't know why this doesn't work. I was hoping it would subsitute out the word before pages and pages. 

                • 5. Re: Substitute out generic digits
                  philmodjunk

                       "pages" - 1 does not evaluate to be the word position of the word that precedes "pages".

                       That will either return an "invalid" result or it returns -1, neither of which will do the job.

                       There is no build in function that can give you the word position of specified text the way we can use Position to get the character position of some specified text.

                       Do you have FileMaker Advanced? This is a case where a custom function could come in very handy.

                       Assuming that you don't:

                       Let ( [ t = TrimAll ( Inventory::${item-note} ; 1 ; 1 ) ; //strip out any extra spaces that may exist between words.
                                 p = Position ( t ; "pages" ; 1 ; 1 ) ; //and we can play games with the 2nd and 3rd parameters if needed to avoid other instances of "pages".
                                 st = Position ( t ; " " ; p ; -2 )  // find the second space character to the left of the P in pages.
                                ];
                                 Substitute ( t ; Middle ( t ; st + 1 ; p - st + 5 )  ; "" )
                               )

                       The removal of extra spaces using TrimAll may not be necessary. On the otherhand it may make an undesirable change to the text you want returned by this calculation.

                       Note: If you named the field Item_note, the ${   } would not be necessary and your calculations that refrernced that field would be easier to read.

                  • 6. Re: Substitute out generic digits
                    PeterMontague

                         I have advanced. I'll try that in the morning. The field contains just one instance of pages. I'll look up some custom functions too. 

                    • 7. Re: Substitute out generic digits
                      philmodjunk

                           My calculation works without advanced. The advantage to a custom function is that it may result in a simpler expression and not need the "TrimAll" function.

                           A custom function that let's you pass the text ahd "pages" to return the word position of "pages" in the text would be ideal.

                      • 8. Re: Substitute out generic digits
                        JimMac

                             @Phil  Good job using Middle, Position, and Substituteyes

                             If the "pages" are more than one in the string, you might have to loop.

                             Jim...

                        • 9. Re: Substitute out generic digits
                          philmodjunk

                               Yep, I am taking Peter's word for it that "pages" only occurs once.

                          • 10. Re: Substitute out generic digits
                            PeterMontague

                                 My renamed item_notes are nice and clean now. A badly needed spring clean. One strange thing happened to my backed up database. If the field did not contain "pages" other information was substituted. 

                                 Luckily I was able to do a find on my backup for only the records that contained "pages". 

                            • 11. Re: Substitute out generic digits
                              philmodjunk

                                   Use this version:

                                   Let ( [ t = TrimAll ( Inventory::${item-note} ; 1 ; 1 ) ; //strip out any extra spaces that may exist between words.
                                             p = Position ( t ; "pages" ; 1 ; 1 ) ; //and we can play games with the 2nd and 3rd parameters if needed to avoid other instances of "pages".
                                             st = Position ( t ; " " ; p ; -2 )  // find the second space character to the left of the P in pages.
                                            ];
                                             If ( p ; Substitute ( t ; Middle ( t ; st + 1 ; p - st + 5 )  ; "" ) )
                                           )

                                   It won't modify your text if "pages" is not present in the text field.