11 Replies Latest reply on Mar 11, 2012 11:02 AM by g@briellelevenson

    Function Help - Maybe Pattern?


      Hello! I am a long time FM PRO user, but limited in function building using anything outside of the easier ones. I need to extract info from the following sample data:


      Save $1 on 1 steak sauce 10oz+

      Save $1 on 1 bar 5pk Save $1 on 1 shake 4pk

      Save $1 on 1 Ears treats Foot, Fruit Gushers, Fruit Roll-Ups

      Save $.50 on 2 Fruit Shapes, Fruit by the


      These are coupons typed into one field and I need to separate the amount saved ($1) into one field and the qty (1) in another field. The pattern is the same - Save $xx "ON" xx.


      I know most of you can do this in your sleep. Please give me a starting point. I'm thinking PatternCount or maybe Middle.


      Thanks for your help!



        • 1. Re: Function Help - Maybe Pattern?

          Hi Matt,


          Can you guarantee that the pattern is always $(number)(space)on(space)quantity?

          • 2. Re: Function Help - Maybe Pattern?

            Hi Matt,


            Here is a possible solution, but there are some problems.


            Formula for AmtSaved =


            Let ( [ START = Position(test::text;"$";1;1)+1 ;

                      END = Position(test::text;"on";1;1)-1]


            Middle ( test::text ; START ; END-START )



            Formula for Qty=


            Let ( [ START = Position(test::text;"on";1;1)+3 ;

                      END = Position(test::text;" ";1;4)]


            Middle ( test::text ; START ; END-START )



            The problems that will arise are that you can never be sure your entry people won't make typing errors and if'/when they do, the formulas will fail.


            I am sure others will have better solutions, but this is a start for you.





            • 3. Re: Function Help - Maybe Pattern?

              I have normalized this data in excel and imported, as I would expect to do...but if calculated assumptions could be made outside of this pattern, it would be nice. Normalizing doesn't scare me to keep it simple. There is only <100 records per week to deal with. The future hope is to import and have all data automated.

              • 4. Re: Function Help - Maybe Pattern?

                Hi matttandy,


                I may have overcomplicated this, but I built on MicheleOlson's solution a bit, to remove some of the user error possibilities.  Your main key here is using the "Filter" function to get rid of all the extra text and boil it down so you can extract your data.  Here is at least how I would go about getting this info:


                Assuming your field says this:

                Save $10 on 4 steak sauce 10oz+


                I would use this code:


                Let ( [

                REMOVE_EVERYTHING_BEFORE_MONEY = Right (Coupon ; Length (Coupon) - Position(Coupon;"$";1;1)) ;

                FILTER_NUMBERS_ONLY = Substitute (Filter (REMOVE_EVERYTHING_BEFORE_MONEY ; "0123456789 ") ; "  " ; " " );

                START_FIRST_NUMBER = 1 ;

                LENGTH_FIRST_NUMBER = Position ( FILTER_NUMBERS_ONLY ; " "  ; 1 ; 1 ) - 1 ;

                START_SECOND_NUMBER = Position ( FILTER_NUMBERS_ONLY ; " "  ; 1 ; 1 ) + 1 ;

                LENGTH_SECOND_NUMBER = Position ( FILTER_NUMBERS_ONLY ; " "  ; 1 ; 2 ) - START_SECOND_NUMBER

                ] ;


                "Money: " & Left (FILTER_NUMBERS_ONLY ; LENGTH_FIRST_NUMBER) &¶&

                "Quantity: " &




                And it will give you this result:

                Money: 10

                Quantity: 4

                • 5. Re: Function Help - Maybe Pattern?

                  Hi Mattandy



                  In my view it would be better to use the 'MiddleWords' and 'GetAsNumber' functions.



                  Money Amount = GetAsNumber(MiddleWords(Input Text;2;1))


                  Quantity = GetAsNumber(MiddleWords(Input Text;4;1))



                  The 'GetAsNumber' will remove the '$' sign and any other punctuation around the 'Quantity'.


                  I must admit to not testing these two formulas, but they look like they will work ...



                  Best wishes - Alan Stirling, London UK.




                  PS:  I have just tested my formulas and there is one situation where the 'Money Amount' formula returns the wrong value;


                  'Save $.50 on 4 straws' must be written as 'Save $0.50 on 4 Straws', since if the leading zero is missing, the decimal point is not picked up, giving the result of '50'!


                  The 'MiddleWords' function collects punctuation between alpha-numeric characters, but not before the start, or at the end of the 'word'.


                  Since I assume that you have control over how this list of offers is set up, you can ensure that leading zeros are entered where necessary.

                  • 6. Re: Function Help - Maybe Pattern?

                    There are already custom functions created for these occasions on briandunning.com, such as the before and after functions.


                    You might want to take a look at them.


                    Sent from my iPad

                    • 7. Re: Function Help - Maybe Pattern?

                      matttandy wrote:


                      I need to separate the amount saved ($1) into one field and the qty (1) in another field.


                      To handle the multiple data-sets within the single field, you will need script or custom function and each data-set should be a record with two fields (Dollars and Qty).  I cannot assist you with the actual transfer until we are clear on whether the records should be created in the existing table or as related records.   You might wish to take a look at the attached file (you must sign into Tech Net to download it).  It uses a custom function to pull the values and in example file attached, data result is (first number is dollars, second is quantity):








                      This takes into account negative numbers, decimals - it should work in all situations (and not necessary to add a leading 0 for decimals) and it generates an error if the field contains an uneven number of "$" in comparison to " on " so you know if you need to correct the field first.  I have included two additional calculations which show how to then pull the values from this calc.  So you can use the calc to view your data and make sure it is fine before 'moving it on' or you can just use same logic directly in a script for extracting the pieces as you loop and create records.


                      If you tell us how you need to move this information, I would be happy to help you with a final script to 'make it so'  otherwise I hope this gives you enough to accomplish your task.




                      Message was edited by: LaRetta - Also, I dropped the dollar sign.  That isn't necessary.

                      • 8. Re: Function Help - Maybe Pattern?

                        I thought each coupon was in its own record?

                        • 9. Re: Function Help - Maybe Pattern?

                          Hi Michael!


                          LOL, well I read this "These are coupons typed into one field" as meaning it was a multi-line text string with all the examples in it. 


                          It would sure make a difference in the solution suggested.

                          • 10. Re: Function Help - Maybe Pattern?

                            Sorry guys I should've been more clear.  Each coupon IS a separate record. I am also entering weekly deals from stores in another dbs and matching with available coupons. I've automated most, like category, sub-cat and store section when manf name is entered. As well as calls to manage expirations, etc. Thanks again for all the help.

                            • 11. Re: Function Help - Maybe Pattern?

                              Good to know! I've been doing this workaround forever, will be happy to try it out!