12 Replies Latest reply on Apr 25, 2013 12:44 AM by FlorianWiegand

    Text Function Help (Search and pass position info on)

    FlorianWiegand

      Title

      Text Function Help (Search and pass position info on)

      Post

           Dear Forum,

           i need some help regarding text functions. The following is an extract of a MySQL Database Collumn which is generated by the Magento webshop and shows custom options for a product:

           "

      a:2:{s:15:"info_buyRequest";a:6:{s:4:"uenc";s:124:"aHR0cDovL3NjYy5zaG9wLm52cy5pbnRyYS9zY2NzaG9wL2luZGV4LnBocC9hdmFpbGFibGUtcHJvZHVjdHMvZXh0ZW5zaW9uLXBhY2suaHRtbD9fX19TSUQ9VQ,,";s:7:"product";s:2:"58";s:15:"related_product";s:0:"";s:7:"options";a:18:{i:176;s:19:"Application Florian";i:174;s:25:"Application Owner Florian";i:196;s:18:"Clarity ID Florian";i:175;s:11:"SID Florian";i:181;s:3:"347";i:190;s:11:"GCM Florian";i:186;s:16:"NewScale Florian";i:189;s:3:"357";i:177;s:13:"rPerf Florian";i:178;s:21:"Additional GB Florian";i:180;s:16:"SAN High Florian";i:179;s:20:"SAN Standard Florian";i:187;s:17:"VG and FS Florian";i:182;s:21:"SAP Flashcopy Florian";i:185;s:3:"354";i:184;s:3:"353";i:191;a:2:{

      "

      Every data entry changes the layout of the text saved. What i need, however is the following in a calculation:

      1. Search Text for Value "s:19"  If value is not found set field to "No entry was made"

      2. Move 2 spaced to the right

      3. Select Text to next " sign

      4. Store in Field

      The result should be the following text: Application Florian

      Thank you for all help

      Florian

        • 1. Re: Text Function Help (Search and pass position info on)
          philmodjunk

               1) Position ( TextField ; "s:19" ; 1 ; 1 )

               will return the position of the s in s:19. If the text does not texist in TextField, a zero is returned so you can check for a zero to now when to set field to "no Entry..."

               2) Position ( TextField ; "s:19" ; 1 ; 1 ) + 6

               will return the posttion of A in "Application.

               4) This expression can be the second parameter of a set field step:

               Let ( [ p = Position ( YourTable::TextField ; "s:19" ; 1 ; 1 ) ;
                         Tend = Position ( YourTable::TextField ; "\"" ; p + 6  ; 1 )
                        ] ;
                        If ( p ; Middle ( YourTable::TextField ; p + 6 ; Tend - p - 6 ) ; "No entry was made" )
                       )

          • 2. Re: Text Function Help (Search and pass position info on)
            FlorianWiegand

                 Hello Phil (if that is your name),

                 i could kiss you! It works very well indeed. However, i made a mistake and mistakelnly used the wrong unique identifier. Instead of "s:19" i would like to use "i:176". I adapted your calculation:

                 Let ( [ p = Position ( product_options; "i:176" ; 1 ; 1 ) ;
                 Tend = Position ( sales_flat_order_item::product_options ; "\"" ; p + 12 ; 1 )
                 ] ;
                 If ( p ; Middle ( sales_flat_order_item::product_options ; p + 6 ; Tend - p - 6 ) ; "No entry was made" )
                 )

                 But now i get "s:19:"Application Florian" as a result, i asume this is because of the " " " stop. How can i make it work now?

            Thank you

            • 3. Re: Text Function Help (Search and pass position info on)
              FlorianWiegand

                   Hello Phil,

                   i found my mistake. By looking at the manual, however, i cant find any info on the "tend" and "\"" commands. Could you explain them further please`?

                   Thank you

              • 4. Re: Text Function Help (Search and pass position info on)
                philmodjunk

                     Look up the Let function.

                     tend is just a variable I typed in to use with the Let function.

                     the backslash \ is called the "escape character" when you put it in front of a double quote character, it causes FileMaker to interpret the quote as a character and not the operator that marks the end/start of a literal text string. When testing this function in the DataViewer, I copied a large portion of your sample text and then inserted that character in front of every quote character so that I had text I could use to test my suggested calculation to make sure that it worked.

                • 5. Re: Text Function Help (Search and pass position info on)
                  FlorianWiegand

                       Hello Phil,

                       thank you for the explenation. I spend some more hours on it and have even more questions. I learned how to use the Let function, how to set variables and positions. The following is the structure of the document: "i:$$$" is the unique identifier, "s:$$" stands for string and defines the amount of characters which are to follow after the ":" If i go from a position to another one using the "+$" function i run into problems as soon as an "i:$$$" has a string of "0", or is empty.

                       I would like to scheck if the "s:$" is zero. If it is zero, then it should also print "No entry was made". What function can i use to select text? And how can i move the position to the next " " " symbol?

                       Thank you

                  • 6. Re: Text Function Help (Search and pass position info on)
                    philmodjunk

                         I can't quite understand your last post. a "+$" function? What's that?

                         Doesn't look like you should be using a check for "s:19" as the numeric part could change and might not always uniquely identify a specific value as other strings could hold completely different text but have the same length..

                         What's the big picture here? Are you parsing this text into a series of fields and/or records? if so, you may be able to use a loop where the script searches out the position of each instance of "s:" in order to extract the data in quotation marks immediately to the right of each.

                    • 7. Re: Text Function Help (Search and pass position info on)
                      FlorianWiegand

                           Hello Phil,

                           we use a webshop for internal customers, where they can order specific SAP systems with custom options. These orders are saved with their custom options in the file i posted above. I am trying to parse info from the text block into specific fields in our configuration management database via field calculations. The i:176 is a unique id which contains the application name. The identifier is followed by the definition of the string which contains the value of the identifier. My first try was with your help tto parse a specific position and move with the + sign to the string contents. It worked, but if a uniqe id contains no string information it returns garbage. The "s:xx" contains the number of characters which make up the string.

                           Thank you for your help

                      • 8. Re: Text Function Help (Search and pass position info on)
                        philmodjunk

                             This isn't xml by some chance? I haven't worked with that format so I'm not sure that I'd recognize it. If it is xml, FileMaker can import the xml data without the need to parse the text with a script like this.

                        • 9. Re: Text Function Help (Search and pass position info on)
                          FlorianWiegand

                               Hi Phil,

                               i tried that, but Filemaker does not recognize it as xml. It does not appear to be xml.

                          Update

                               It appears to be a serialized array.

                          • 10. Re: Text Function Help (Search and pass position info on)
                            FlorianWiegand

                                 Hello Phil,

                                 "if so, you may be able to use a loop where the script searches out the position of each instance of "s:" in order to extract the data in quotation marks immediately to the right of each."

                                 How would i go about doing that? I havent really figured out how to mark text next to a certain symbol such as "

                            • 11. Re: Text Function Help (Search and pass position info on)
                              FlorianWiegand

                                   Hi Phil,

                                   my latest brain work made this:

                                   Let (
                                     [
                                       source = sales_flat_order_item::product_options;
                                       sourceLength = Length(sales_flat_order_item::product_options);
                                       startPosition = Position ( source ; “i:177” ; 1 ; 1 ) - 1;
                                       endPosition = Position (source ; "\"" ;  startPosition;  1 )
                                      //result = Right ( source ; sourceLength - startPosition )
                                     ];
                                   //startPosition
                                   endPosition
                                    )

                                   Now i have to find a way to extract the the contents between the startPosition and the endPosition.
                                    

                              • 12. Re: Text Function Help (Search and pass position info on)
                                FlorianWiegand

                                I got it :-)

                                     //
                                     //Written by Florian

                                     //This parser goes through the sales_flat_order_items::product_options field and parses specific data entries
                                     //example.: "i:177" stands xxxxxxx

                                     Let (
                                       [
                                         source = sales_flat_order_item::product_options;
                                         sourceLength = Length(sales_flat_order_item::product_options);
                                         startPosition = Position ( source ; "i:178" ; 1 ; 1 ) ;
                                         middlePosition = Position ( source ; "\"" ; startPosition; 1 ) +1 ;
                                         endPosition = Position (source ; "\"" ;  middlePosition;  1 ) -1;
                                         result = getchar ( source ; middlePosition; endPosition)
                                      ];

                                     //startPosition
                                     //middlePosition
                                     //endPosition
                                     If ( result >"" ; result ; "No entry was made" )
                                     )

                                     //getchar ( text ; start ; stop )

                                      

                                     Thank you for your tips and help!