1 2 Previous Next 17 Replies Latest reply on Apr 11, 2017 8:38 PM by JackRodges

    stripping out prices


      I'm wanting to strip out prices from a load of text in fields. The prices range from £0.00 to £23456.00. They are always followed by a return.


      I can substitute "" for the "£" sign, but how do I select the variable length word associated with it?




        • 1. Re: stripping out prices

          are there other NUMBERS in the text? If not you may be able to Filter():

          http://fmhelp.filemaker.com//help/15/fmp/en/#page/FMP_Help/filter.html <http://fmhelp.filemaker.com//help/15/fmp/en/#page/FMP_Help/filter.html>


          Filter ( myText ; "ABCDEF-Zabcdef-z ." ) // add in all _allowed characters

          There may be other custom functions that simplify this. You may not want to remove the "." from the text, except in numbers.



          • 2. Re: stripping out prices

            Thanks for the speedy reply Beverley. Unfortunately there are numbers in the rest of the text too.



            • 3. Re: stripping out prices

              A view of the complete text that you want to manipulate is needed in order for any suggested fix to be made with confidence that it will work for you.


              Position can give you the position of the currency character. You can then use that value with position again to get the position of the first return that follows that currency character. You then know the position of the first and last characters of the text that you want to strip out.


              Let ( [ Currency = Position ( YourText ; "£" ; 1 ; 1 ) ;

                         Return = Position ( YourText ; ¶ ; Currency ; 1 ) ] ;

              • 4. Re: stripping out prices

                Would you be willing to give us an example of a full block of text you'd like to extract from?


                the approach will vary based upon that.

                • 5. Re: stripping out prices

                  Here's an example of the text...


                  New 9m Cantilever palisade gate, 2.4m high with a weld mesh infill.

                  Galvanised hot dip finish

                  HD carriage and guild roller set.

                  Guild and receiver post to be 150mm rhs capped

                  All support posts to be base plated and fixed directly to a concrete slab.




                  Construct fencing to conceal the rear of the gate travel to meet compliance levels.

                  weld mesh fence at least 1.8m in height

                  Accessible removable panel to enter to carry out maintenance and commissioning on the motor.

                  Mesh the internal elevation of the existing palisade fence to eliminate all drag and shear zones as gate is in motion.

                  Galvanised finish to match gates.


                  AUTOMATION KIT

                  1x electromechanically driven sliding motor set

                  Full compliance safety features

                  Automatic closing

                  Emergency manual release fitted to both the motors.


                  ACCESS EQUIPMENT

                  1x Wireless key fob (upto 200 available @ £35 each)

                  Controlled access system as detailed in separate quotation.


                  • 6. Re: stripping out prices

                    And is the result that you want, the same exact text and formatting minus the currency entries?


                    And how would you modify this text?

                    1x Wireless key fob (upto 200 available @ £35 each)


                    Notice that it does not follow your specified format rules as it starts with a currency symbol but is NOT followed by a return.


                    I would guess that you want this text to be left unmodified, but please confirm.

                    • 7. Re: stripping out prices

                      Well spotted. The £35 each can disappear too. The client want's this quote to go to the fitters to be used as the specification for the job but without prices.



                      • 8. Re: stripping out prices

                        Yes, but how would that look? Take out the entire phrase enclosed in parenthesis? Just the part after the @? The currency and the @ character?


                        This is the one exception to a pattern that I see, which is not only is the currency followed by a return, but it's on a row of text that is just the currency. And you can set up a custom function or script to loop through such text using GetValue to check for currency entries.


                        But not for this one value.


                        And you didn't answer my other question: What is the final result that you want to see for the entire block of text? I've made a guess, but I'd like confirmation.


                        the GIGO corollary:


                        Messy Data makes for messy parsing algorithms.

                        • 9. Re: stripping out prices

                          There is a basic data design principle: one fact per field.

                          There are ways that you can use concatenation calculations to take properly designed data; and build the text blocks that you want.

                          Therefore; there are ways to build a calc that INCLUDES the price; and a separate text block calc that does not include the price.


                          Having got this far, note that if you choose to do things the hard way - and then ask OTHER people to clean up after you, it would probably be better if they all declined. Though the spirit of helpfulness and problem solving is very strong here, and you may get your unnecessary problem solved anyway.

                          • 10. Re: stripping out prices

                            Yes the text is messy, but that's not the issue. Really any price with a £ before it needs deleting. So if that leaves...


                            "ACCESS EQUIPMENT

                            1x Wireless key fob (upto 200 available @ each)

                            Controlled access system as detailed in separate quotation.




                            that's fine.


                            Going forward is no problem as I'm compiling the text without the prices. This is just to loop through the few hundred existing records.

                            • 11. Re: stripping out prices

                              Thanks Bruce for your input.

                              I agree that having 2 separate text boxes, one with prices and one without is the best solution. Which is what I've done now that the client has asked for it. It was something he specifically didn't want before. Clients eh!?


                              What I'm trying to do it extract the info for the existing 305 records.


                              I wasn't asking for someone to fix my bad work, rather it seemed a good example of pondering a more fundamental question of substituting a variable, varied length word. Fortunately we know what unique character starts the word and what common one ends it.

                              • 12. Re: stripping out prices

                                I have not tested this and have not put in all the details needed but:


                                Set Variable [$FullText ; Value: YourTable::YourTextFieldHere ]


                                    Set Variable [ $K ; value: $K + 1 ]

                                    Exit Loop If [$K > ValueCount ( $FullText ) ]

                                    Set Variable [ $TextRow ; GetValue ( $FullText ; $K ) ]

                                    Set Variable [$Curr ; value: Position ( $TextRow ; "£" ; 1 ; 1 ) ]

                                    If [ $Curr > 0 // currency amount detected ]

                                       If [$Curr > 1 // currency embedded in text ]

                                          #Loop through $TextRow from $Curr to first non numeric character using MIddle to look at one char at a time

                                          #Set $CurrEnd to last character of currency.

                                          Set Variable [$KeepText ; value: Left ( $TextRow ; $Curr - 1 ) & Middle ( $TextRow ; $CurrEnd + 1 ; 99999 ) ]


                                          Set Variable [$KeepText ; value: "" ]

                                       End If

                                       Set Variable [$NewText ; Value: List ( $NewText ; $KeepText ) ]


                                        Set Variable [$NewText ; Value: List ( $NewText ; $TextRow ) ]

                                    End If

                                End Loop

                                Set Field [YourTable::yourTextFieldHere ; $NewText ]

                                • 13. Re: stripping out prices

                                  Wow thanks Phil. Perfection!


                                  I've done a much simple one based on


                                  Let ( [

                                  §Price = Position ( QUOTE::Quote_Compile_Engineers_View ; "£" ; 1 ; 1 ) ;

                                  §End = Position ( QUOTE::Quote_Compile_Engineers_View ; "¶" ; §Price ; 1 )



                                  Replace ( QUOTE::Quote_Compile_Engineers_View ; §Price ; §End - §Price ; "" ) )


                                  I've then looped through the text to get all the prices and exited the loop if there's no more prices found, using


                                  PatternCount ( QUOTE::Quote_Compile_Engineers_View ; "£" ) = 0


                                  and then looped through the records.


                                  Thanks for your excellent effort. I'll get to grips with your catch all and save it for future applications.



                                  • 14. Re: stripping out prices

                                    I think you misunderstood.

                                    The messy text should not even exist in the first place.

                                    I don't know where all the original data for the text blocks comes from.

                                    But it SHOULD come from some kind of order description that includes line items; with separate fields for quantity, description, and price. And this is what you should be telling the customer that you need to build for him if it doesn't exist.

                                    Then you can build a text summary of the order from that.

                                    1 2 Previous Next