1 2 Previous Next 15 Replies Latest reply on Feb 19, 2017 6:51 AM by Henry_2

    Parsing columns out of a matrix

    MSNThomas

      Does anyone know of a good way to parse out columns from data formatted in the following manner?

       

      旭市,Asahi City,ヨネゴメ

      旭市,Asahi City,ロ

      長生郡長南町,Chonan Town Chosei District,イカニケイサイガナイバアイ

      長生郡長南町,Chonan Town Chosei District,イチノノ

       

      This is part of a result from an ExecuteSQL call on an table of addresses. (some of it is in Japanese, so if it looks like garbly-gook that's why). I'd like to get the columns out as separate value lists, put them in some fields and then use them as actual value lists on an address entry layout.

       

      Right now I'm using auto-complete text fields that use executeSQL to get the lists of cities, streetnames, etc., but it can be very, very slow.  I tried "old-fashoned" value lists based on fields through filtered relations, but that was also very, very slow.

       

      My thought now is instead of calculating each list separately, I can use one ExecuteSQL to get a list like the one above (am I right that that would be a matrix?), and then just put the columns into fields.  Repeat values won't matter since when you create a value list based on a field (that must be indexed), repeat values are automatically discarded.

        • 1. Re: Parsing columns out of a matrix
          Mike_Mitchell

          Here's a custom function I use for that purpose:

           

          --------------------------------------------------------------------------------------------------------------------

           

          /*

          GetColumn function

           

          Author

          *COMMENT Visual Realisation

           

          Format

          cfGetColumn ( text ; columnNumber ; columnDelimiter ; result )

           

          Parameters

          text - any text expression or text field

          columnNumber - any numeric expression or field containing a number

          columnDelimiter - any text expression or text field

          result - initially empty; used for tail recursion

           

          Data type returned

          text

           

          Description

          Returns a carriage return-delimited list of values from the requested column in a tabular text array.

           

          July 7, 2008

           

          Modified February, 2014 to use tail recursion

          Mike Mitchell, Net Caster Solutions

          www.netcastersolutions.com

           

          */

           

          Let ( [

          row  = GetValue ( text ; 1 ) ;

          cell = MiddleValues ( Substitute ( row ; columnDelimiter ; ¶ ) ; columnNumber ; 1 ) ;

          cell = Substitute ( cell ; ¶ ; "" ) ;

          countRows = ValueCount ( text )

          ] ;

           

          Case (

          countRows > 0 ;

          cfGetColumn ( RightValues ( text ; countRows - 1 ) ; columnNumber ; columnDelimiter ; List ( result ; cell )) ;

           

          result

           

          )

          )

           

          --------------------------------------------------------------------------------------------------------------------

           

          HTH

           

          Mike

          • 2. Re: Parsing columns out of a matrix
            MSNThomas

            Hi Mike,

             

            I tried you custom function, and it worked great on smaller sets of data.  However, data that was 6 columns across with over a thousand rows slowed down quite a bit.

             

            I did some more experimenting, and found that making individual ExecuteSQL calls and then saving those into global variables instead of indexed fields make things go super speedy.  The real culprit was the indexes being built on all the data that was returned.

             

            To turn global variables into usable value lists I used a looping script to wrap them into some HTML, including fmp protocol links that call a script which sets the field with the data when clicked.

             

            Thank you for your response and for introducing a very useful custom function.  I'm pretty sure I'll get some use out of that in the future.

            • 3. Re: Parsing columns out of a matrix
              Mike_Mitchell

              Yes, variables are almost always what you want to use for processing of this nature. Glad the CF was useful.

              • 4. Re: Parsing columns out of a matrix
                beverly

                The FileMaker Training Series (and others) use the method of taking each "row" and substituting the "delimiter" with a return, then you can use the GetValue() function. However, as with any method to parse this kind of thing, IF your "delimiter" happens to occur in the "column" value, then custom functions and anything that substitutes may give you unpredictable results. Hopefully you have control over the delimiter and can use something rare. I like the "|" pipe for delimiter, but may often use a series of characters as my delimiter: "~|~" and can then be more assured of positive results from parsing.

                 

                Mike is right. Variables are so very wonderful for FileMaker text parsing!! Days gone by, the global fields could be used for temporary storage when parsing.

                beverly

                • 5. Re: Parsing columns out of a matrix
                  Henry_2

                  Mike, your cfGetColumn function helped me a lot,

                  The version I used earlier (https://www.briandunning.com/cf/877) caused Filemaker to use many GB's memory within 30 seconds and becoming unresponsive when getting columns from a (large) set of data. Your modified version solved this entirely

                   

                  Thanks!

                   

                  Henry

                  • 6. Re: Parsing columns out of a matrix
                    Henry_2

                    Ran into a cases where it's necessary that empty rows should also be taken into account, to ensure that each column has the same amount of rows. Any suggestions?

                    • 7. Re: Parsing columns out of a matrix
                      Mike_Mitchell

                      I'm assuming you mean empty "cells" (rather than rows). You can solve that by substituting a blank space for an empty cell.

                      1 of 1 people found this helpful
                      • 8. Re: Parsing columns out of a matrix
                        Henry_2

                        Thanks for your quick response, empty cells indeed. My data is generated by executesql, eg. output is like this:

                        column1,column2,column3,column4

                        1000008042,871551231,ads,121

                        1000008043,871551123,jgt,

                        1000008047,871851112,dsg,010

                        1000008048,871551111,abc,789

                         

                        (the second row has no value in column4)

                         

                        Where/how would you advise to do a substitution? Could I (also) adjust the cfGetColumn function to cope with this?

                        • 9. Re: Parsing columns out of a matrix
                          beverly

                          Keep in mind:

                          • the standard 'end-of-line' is return, Char(13)

                          • you can specify something that may make it easier for you to parse those "empties":

                          ; "|" & Char(13)

                          ; "~" & Char(13)

                          etc.

                          beverly

                          Sent from miPhone

                          1 of 1 people found this helpful
                          • 10. Re: Parsing columns out of a matrix
                            Henry_2

                            Thanks Beverly, not (yet) sure where to adjust to make use of your suggetion.

                             

                            Meanwhile I added a temporary prefix to most of my columns in the sql query:

                            SELECT  \"column1\", 'TEMPPP'+\"column2\", 'TEMPPP'+\"column3\", 'TEMPPP'+\"column4\"

                             

                            and then get rid of it when getting the column data:

                            $column4List = Substitute ( cfGetColumn ( $queryResult ; 4 ; "," ; "" ) ; "TEMPPP" ; "" )

                             

                            Ideally I could adjust the cfGetColumn function (or how I use it) to not need to add prefixes in the sql queries

                            • 11. Re: Parsing columns out of a matrix
                              beverly

                              ExecuteSQL(yourQuery; "" ; "")

                               

                              Can be

                              ExecuteSQL(yourQuery; "|" ; "~" & Char(13) )

                               

                              Try it and see if you have other characters to help you parse the results.

                               

                              Also see it these articles help:

                               

                              Sent from miPhone

                              1 of 1 people found this helpful
                              • 12. Re: Parsing columns out of a matrix
                                Henry_2

                                Thanks Beverly,

                                Did not check the Soliant links yet, got it anyway thanks to your suggestion. solved as follows:

                                ExecuteSQL ( $query ; "TEMP~|||" ; "TEMP~" & Char ( 13 ) )

                                 

                                combined with:

                                Substitute ( cfGetColumn ( $queryResult ; 1 ; "|||" ; "" ) ; "TEMP~" ; "" )

                                 

                                Added the TEMP~ as field and as row separator to ensure all columns would (temporarily) contain the string.

                                • 13. Re: Parsing columns out of a matrix
                                  Mike_Mitchell

                                  Not near a computer at the moment, but does ExecuteSQL support COALESCE?

                                  • 14. Re: Parsing columns out of a matrix
                                    beverly

                                    yes, MM. It is supported in ExecuteSQL() function. 'Supported' may be subjective if we're not sure how you propose to use it.

                                     

                                    Sent from miPhone

                                    1 2 Previous Next