1 2 3 Previous Next 30 Replies Latest reply on Oct 1, 2012 10:12 AM by steve_ssh

    Converting text array to html format for table

    carlo.m

      I have an array of text (comma dilmited data return delimted rows) that I want to display on an html table with webviewer.

       

       

      As of now, I have an array with the following rows as headers:

       

      Category, Part, Week 30, Week 30, Week 31, Week 31, Week 32, Week 32

      Category, Part, Sales, Units, Sales, Units, Sales, Units

       

      With a Custom Function, I would like to convert it to:

       

       

      <tr>

      <th rowspan="2"> Category </th>

      <th rowspan="2"> Part </th>

      <th colspan="2"> Week 30 </th>

      <th colspan="2"> Week 31 </th>

      <th colspan="2"> Week 32</th>

      </tr>

      <tr>

      <th> sales </th>

      <th> units </th>

      <th> sales </th>

      <th> units </th>

      <th> sales </th>

      <th> units </th>

      </tr>

       

       

      ANY IDEAS????

        • 1. Re: Converting text array to html format for table
          Mike_Mitchell

          Hello, Carlo.

           

          I don't have one that does exactly that, but I have one that's similar. Maybe you can adapt it.

           

          /*

           

          cfArrayToTableWithClasses ( sourceArray ; classArray ; startSourceRow ; startClassRow )

           

          Converts a return-delimited array to a single-column table with classes assigned to the cells where any element of each row is present in the classArray array.

           

          Parameters:

          sourceArray = starting array to be modified

          classArray = array containing the class names to add

          startRow = starting row (usually should be 1)

          startCol = starting column (should always be 1)

           

          Example

          sourceArray:

          apple|red

          banana|yellow

          orange|orange

          golden delicious|yellow

           

          classArray:

          yellow

          orange

          golden delicious

           

          columnDelimiter = "|" ; startRow = 1 ; startCol = 1

           

          Result:

          <table><tr><td>apple|red</td></tr>

          <tr><td class="yellow">banana|yellow</td></tr>

          <tr><td class="orange">orange|orange</td></tr>

          <tr><td class="yellow">yellow|golden delicious</td></tr></table>

           

          Error trapping: If more than one of the class values is found in a row, the first one "in" will be tagged as the class for that row.

           

          Another error trap is to strip out forbidden characters from class names ( parentheses, spaces, etc.). Spaces are replaced with underscores; parentheses are removed. The developer will need to encode the CSS styles appropriately.

           

          Author: Mike Mitchell

           

          */

           

          Let( [ textRow = GetValue( sourceArray ; startSourceRow ) ;

           

          compareVal = Case( startClassRow > ValueCount( classArray ) ; "" ;

           

          GetValue( classArray ; startClassRow )

           

          ) ;

           

          compareValClean = Substitute( Lower( compareVal ) ; [ "(" ; "" ] ; [ ")" ; "" ] ; [ " " ; "_" ] ) ;

           

          newTextRow = Case( startSourceRow = 1 and startClassRow = ValueCount( classArray ) ; "<table>") &

           

          Case( startClassRow = ValueCount( classArray ) ; "<tr>" ) &

           

          Case( IsEmpty( compareVal ) ; textRow ;

           

          PatternCount( textRow ; "class=" ) ; textRow ;

           

          PatternCount( textRow ; compareVal ) ;

           

          Case( not PatternCount( textRow ; "td>" ) ;

           

          "<td class=\"" & compareValClean & "\">" & textRow ;

           

          Substitute( textRow ; "<td>" ; "<td class=\"" & compareValClean & "\">" )) ;

           

          Case( startClassRow = ValueCount( classArray ) and not PatternCount( textRow ; "<td" ) ; "<td>" ) & textRow )  &

           

          Case( startClassRow = ValueCount( classArray ) ; "</td></tr>" )

           

          ] ;

           

          Case( startClassRow > ValueCount( classArray ) ;

           

          cfArrayToTableWithClasses( LeftValues( sourceArray ; startSourceRow - 1 ) & newTextRow & ¶ & RightValues( sourceArray ; ValueCount( sourceArray ) - startSourceRow ) ;

           

          classArray ; startSourceRow + 1 ; 1 ) ;

           

          startSourceRow > ValueCount( sourceArray ) ; sourceArray & "</table>" ;

           

          cfArrayToTableWithClasses( LeftValues( sourceArray ; startSourceRow - 1 ) & newTextRow & ¶ & RightValues( sourceArray ; ValueCount( sourceArray ) - startSourceRow ) ;

           

          classArray ; startSourceRow ; startClassRow + 1 )

           

          ))

          • 2. Re: Converting text array to html format for table
            carlo.m

            Thanks, helpful.

             

            However the first issue I have to tackle, and the one I'm stuck with is determining how many repetitions of a given string are in a row before the value changes or the end of the row is reached.

             

            Because as per the example, I can't do a straight patterncount() because it would count without regarding if other values where in between.

             

            I have an idea of how to do it on a row using position to see if the next value is identical to the test value and recurse until its different, and then by counting the recursions get my colspan="x". But then I get stuck on combining that one row with the next ones to see if a rowspan applies. Also I always like to try and do things without recursion to improve performance when possible, and with this case I just can't think of a way to do any part of it without recursion.  

             

            Sent from my iPhone

            • 3. Re: Converting text array to html format for table
              beverly

              Not perfect or optimized but this script worked for me. See if it helps you figure out a custom function (notes as # lines):

               

              code resent as test (below)

               

              the "Let()" inside the Set Variable $test does the work of figuring if we have a rowspan or colspan. Obviously this only works for your set with possible rowspan=2 or colspan=2

               

              See if it helps.

              Beverly

               

              Message was edited by: Beverly Voth Can I *emphasize* how much I really HATE HATE HATE the fact that this forum won't let me put a script as text and have it come out as valid? The above is obviously serious garbage. The square braces apparently are some sort of secret code that this forum uses (to munge my code, of course). Where is the HELP on this forum software and it's truly insane editor? Try the attachment PDF of the above code.

              • 4. Re: Converting text array to html format for table
                BruceRobertson

                cfArrayToTableWithClasses looks interesting but doesn't work for me. Or maybe - just don't understand the classes bit. It does produce a result. Unclear about columnDelimiter; it is declared in the comments but does not seem to be used in the function or its parameters.

                • 5. Re: Converting text array to html format for table
                  Mike_Mitchell

                  Sorry, Bruce. The purpose of the function is to convert a two-column array into an HTML table. One column on each row contains data; the other contains names of CSS classes for those rows. The "columnDelimiter" is meant to indicate the separator between the text of the table cell and the name of the class you want to insert into the table cell. In the example used in the comments, for example, you have:

                   

                  apple|red

                  banana|yellow

                  orange|orange

                  golden delicious|yellow

                   

                  Then, you pass an additional array that contains the names of the classes you want to apply, for example:

                   

                  yellow

                  orange

                  golden delicious

                   

                  The function then compares the items in the array of classes to the elements in the first array. If it finds an element in the first array that corresponds to a class name, it'll insert the name of the class in the <td> tag. If not, it just inserts a plain old <td> tag:

                   

                  <table><tr><td>apple|red</td></tr>

                  <tr><td class="yellow">banana|yellow</td></tr>

                  <tr><td class="orange">orange|orange</td></tr>

                  <tr><td class="yellow">yellow|golden delicious</td></tr></table>

                   

                  Note the first row. The class name "red" doesn't exist in the class name array, so it gets normal <td> tags. The other rows have a class name embedded.

                   

                  This was a special-purpose function I wrote some time back for a solution I was working on. I needed a way to insert classes into a table easily, and so I wrote this function to accomplish this. It can probably be improved and modified for more broad application.

                   

                  Thanks for the interest. I'd be curious to know why it's not working for you.

                   

                  Mike

                  • 6. Re: Converting text array to html format for table
                    BruceRobertson

                    Well, I guess it is working but I expected the data column to be split off in the result; and as mentioned the column delimter isn't being called but looks like it's there for some reason, to do the splitting.

                    • 7. Re: Converting text array to html format for table
                      BruceRobertson

                      So I would have expected the result to be:

                       

                      <table>

                      <tr><td>apple|red</td></tr>

                      <tr><td class="yellow">banana</td></tr>

                      <tr><td class="orange">orange</td></tr>

                      <tr><td class="yellow">golden delicious</td></tr>

                      </table>

                       

                      Or something like that

                      • 8. Re: Converting text array to html format for table
                        Mike_Mitchell

                        There's a partner function I have that pulls the class column out. Probably should include that. I will if you're interested.

                         

                        Mike

                        • 9. Re: Converting text array to html format for table
                          BruceRobertson

                          Sure, I'd like to see that. Thanks.

                          • 10. Re: Converting text array to html format for table
                            beverly

                            Not perfect or optimized but this script worked for me. See if it helps you figure out a custom function (notes as # lines):

                             

                            # using "$input" for the start, but you could have a field feeding the two header lines
                            Set Variable [ $input; Value:"Category, Part, Week 30, Week 30, Week 31, Week 31, Week 32, Week 32 ¶
                            Category, Part, Sales, Units, Sales, Units, Sales, Units" // tokenize the header into "rows" (VALUES) ]
                            #
                            # now convert the commas to returns for each row
                            Set Variable [ $row1; Value:Substitute ( GetValue ( $input ; 1 ) ; "," ; "¶" ) // tokenize the row into VALUES ] 
                            Set Variable [ $row2; Value:Substitute ( GetValue ( $input ; 2 ) ; "," ; "¶" ) // tokenize the row into VALUES ] 
                            #
                            # just a test to see if we have the same number of values in each row
                            If [ ValueCount ( $row1 ) ≠ ValueCount ( $row2 ) // test for EQUAL rows ]
                             Show Custom Dialog [ Message: "Your two rows don't match."; Default Button: “OK”, Commit: “Yes” ]
                             Halt Script 
                            End If
                            #
                            # set up the "counter", the start of html for rows 1 & 2
                            Set Variable [ $i; Value:1 ] 
                            Set Variable [ $tr1; Value:"<tr>" ] 
                            Set Variable [ $tr2; Value:"<tr>" ] 
                            #
                            # loop through the value count, incrementing as needed. $test is just a place holder to set the other variables
                            Loop
                            Set Variable [ $test; Value:
                            Let ( 
                                 [ $r1ci = Trim ( GetValue ( $row1 ; $i ) ) 
                                 ; $r1ci2 = Trim ( GetValue ( $row1; $i + 1 ) )
                                 ; $r2ci = Trim ( GetValue ( $row2 ; $i ) )
                                 ; $r2c2 = Trim ( GetValue ( $row2 ; $i + 1 ) )
                                 ; $rs = If 
                                      ( $r1ci = $r2ci 
                                      ;  2 // cell in each row matches, so we have a rowspan
                                      )
                                 ; $cs = If 
                                      ( $r1ci = $r1ci2 
                                      ; 2 // cell in row 1 matches the next cell, so we have colspan
                                      )
                                 ; $tr1 = $tr1 & Case 
                                      ( $rs ; "<th rowspan=2>" & $r1ci
                                      ; $cs ; "<th colspan=2>" & $r1ci
                                      ; "<th>" & $r1ci
                                      ) & "</th>"
                                 ; $tr2 = $tr2 & Case 
                                      ( $rs ; "" 
                                      ; $cs ; "<th>" & $r2ci & "</th><th>" & $r2c2 & "</th>"
                                      ; "<th>" & $r2ci & "</th>"
                                      ) 
                                 ; $i = If ( $cs ; $i + 1 ; $i // increment again if we've used a colspan )
                                 ; $result = $i
                                 ]; $result
                            )
                            ] 
                            Set Variable [ $i; Value:$i + 1 // increment the counter ] 
                            Exit Loop If [ $i > ValueCount ( $row1 ) // stop if we run out of values ]
                            End Loop
                            #
                            # finish off the rows and push into a single variable
                            Set Variable [ $header; Value:
                            Let ( 
                                 [ $tr1 = $tr1 & "</tr>"
                                 ; $tr2 = $tr2 & "</tr>" 
                                 ; $result = $tr1 & ¶ & $tr2 
                                 ]; $result
                            )
                            ] 
                            Exit Script [ ]
                            
                            

                             

                            the "Let()" inside the Set Variable $test does the work of figuring if we have a rowspan or colspan. Obviously this only works for your set with possible rowspan=2 or colspan=2

                             

                            See if it helps.

                            Beverly

                            • 11. Re: Converting text array to html format for table
                              Mike_Mitchell

                              Drat! Now I can't find it. I'll have to recreate it and get back with you.

                              • 12. Re: Converting text array to html format for table
                                TomHays

                                Since a WebViewer display is your objective, what about doing all of the parsing and formatting work in JavaScript?

                                Make your WebViewer calc generate a custom JavaScript calc that assigns your block of CSV text to a JavaScript variable. (Or better yet, if your CSV is generated by FileMaker, generate a more JavaScript-friendly format.)

                                 

                                This would perform much faster than a recursive FileMaker calculation.

                                 

                                -Tom

                                • 13. Re: Converting text array to html format for table
                                  carlo.m

                                  Beverly,

                                   

                                       Good find! Unfortunately it doesn't work for me because of the limitation on the max span of 2.

                                   

                                   

                                       Fortunately, I was at it a good while yesterday and after a lot of back and forth modifying the calc, I got it. At one point it was working but would return an incorrect result when the array had a format like this:

                                   

                                  A,A,B,B

                                  A,B,B,B

                                   

                                  It would return <th colspan="2" rowspan="2"> A </th> <th colspan="2" rowspan="2"> B </th>

                                   

                                       Curious thing was the solution to the problem was actually getting the same custom function to add the html tags from the begining... which was awesome... because I fixed the bug while getting the end result in the process of finding the span, making the function more efficient.

                                   

                                  If you are interested, after putting some formatting touches on the function, I'd be happy to share it.

                                  • 14. Re: Converting text array to html format for table
                                    carlo.m

                                    Hi Tom,

                                     

                                    This is my first endeavor of any kind with HTML. I went through some tutorials on w3schools.com which were super super helpful...

                                     

                                    I'm not sure exactly what you mean by formatting with JavaScript.

                                     

                                    I am generating the csv with filemaker. I have a function that converts the array of text (data) to <tr><td>data</td></tr> format.

                                     

                                    I am using CSS I found and on the tutorials to format the table:

                                    http://www.w3schools.com/css/css_table.asp

                                     

                                    An issue I have now is precisely giving format to the data. It has to many decimals and sometimes the info will be % or currency $. So I'm thinking of what way will be best/fastest to give format to it.

                                     

                                    I'm getting the info via executesql, I can't round it there because you can't round a sum, and sum is what I'm doing...

                                     

                                    If you can enlighten me on what I can do with JavaScript I would truly appreciate it.

                                    For example, can data be interacted with once it is on the HTML Table, can I modify it and then refresh totals? Or can I, by clicking on a given cell in the HTML table, go and perform a find in Filemaker to show the detailed records that are summed up in that number?

                                     

                                    Thanks in advance for your guidance...

                                    1 2 3 Previous Next