1 2 Previous Next 15 Replies Latest reply on Jan 6, 2017 1:29 PM by sam_oda

    Cross Tab Tables in the Web Viewer using html

    thehalpeen

      I am trying to create a dynamic staff roster that will compare favourably with other non-Filemaker solutions/apps I’ve found on the internet.

      It seems nowadays that any self-respecting app/solution should have a cross tab report.

       

      Below is what I'm trying to achieve in the Web Viewer: I have hard-coded all the html code.

      html roster.PNG

       

      I’m aware that cross-tab reports can be complex using Filemaker. With the help of these forums, I have successfully created a cross-tab ‘staff roster’ using the People/Staff table and some filtered portals. However this report is limited to the number of portal rows I assign.

       

      I would like the roster to be flexible so that it would expand and contract depending on the number of rows of data (shifts per person). This is not possible with the portal method.

       

      However, I’ve seen cross-tab reports done via the Web-Viewer, using variables, scripts, lists, and loops to build up a html table that includes table rows and table data.

       

      I’m trying to create a report that will show weekdays (Monday to Sunday) for a selected week – say Week 10 - across the top (X-axis) and the person/staff names down the side (Y-axis), with the relevant data in the middle.

       

      I have the following tables:

      • Staff/Employees
      • Shifts – there are 8 different shifts, ie 7am-11am, 11am-2pm, etc.
      • Staff/Shift (Join Table) this list all the data I’m trying to display in the Cross Tab table.

      With information from the web and from some Filemaker Discussions, I have managed to create the x-axis showing the dates. To do this I needed to create a list/array of the seven dates and parse this list into html table row and table data.

       

      I’ve search the web on this topic and I’ve come across some techniques, but they all seem to involve summaries of data – there would be one row per item. They  involved three scripts – one for the x-axis, one for the y-axis and one for the cross-tab data. My situation could have many rows for the one item (Staff member) on the y-axis.

       

      I have a created the report manually in the web Viewer (see image above) to show what I am trying to achieve, but I need help/advice/direction building the dynamic script to produce the Cross-Tab report. I’m aware I will need lists, counters, various loops, variables – so it’s not a normal request. I can create the first row, but I get lost, if I have to insert a 2nd row of data for the same staff member. I’m finding it difficult to determine the loops required to assimilate the cross-tab table.

       

      Any help or direction would be very much appreciated.

        • 1. Re: Cross Tab Tables in the Web Viewer using html
          sam_oda

          How about this one?

           

          1.Using FileMaker, make the text like this which represents the tabale data ( for example, use ExecuteSQL function )

          [{name:"John",age:"52",weight:"60kg"},{name:"Sam",age:"32",weight:"50kg"},{name:"Mary",age:"22",weight:"70kg"}]

           

          2:webviewer definition

           

          Let([

              data="[{name:\"John\",age:\"52\",weight:\"60kg\"},{name:\"Sam\",age:\"32\",weight:\"50kg\"},{name:\"Mary\",age:\"22\",weight:\"70kg\"}]";

          code="data:text/html,<!DOCTYPE html>

              <body>

              <script>

          const data=__DATA__;

           

          const firstRow=document.createElement('div');

          document.body.appendChild(firstRow);

          firstRow.style.display='flex';

          firstRow.style.width='100%';

          firstRow.style.border='1px solid';

          for(const key in data[0]){

              const cell=document.createElement('div');

              cell.style.flex=1;

              cell.style.display='flex';

              cell.style['justify-content']='center';

              cell.style['align-items']='center';

              cell.style.border='1px solid #ccc';

              cell.innerText=key;

              firstRow.appendChild(cell);

          }

           

          for(let i=0; i<data.length; i++){

              const row=document.createElement('div');

              document.body.appendChild(row);

              row.style.display='flex';

              row.style.width='100%';

              row.style.border='1px solid';

              for(const key in data[i]){

                  const cell=document.createElement('div');

                  cell.style.flex=1;

                  cell.style.display='flex';

                  cell.style['justify-content']='center';

                  cell.style['align-items']='center';

                  cell.style.border='1px solid #ccc';

                  cell.innerText=data[i][key];

                  row.appendChild(cell);

              }

          }

          </script>

          </body>"]

          ;

          Substitute(code; "__DATA__";data)

          )

           

          (Caution) This code works only on FileMaker15,Mac(Sierra). Please modify the code to fit your environment.

          1 of 1 people found this helpful
          • 2. Re: Cross Tab Tables in the Web Viewer using html
            beverly

            typically, it's a loop within a loop. can you post your script?

            beverly

            • 3. Re: Cross Tab Tables in the Web Viewer using html
              thehalpeen

              Sam,

               

              Thanks. You're using Javascript - ok, I'm not too familiar with that, but I recognise the tags.

               

              Can you post a screenshot of how the Web Viewer renders this?

               

              I am using window 10 and Filemaker Pro Advance 15 - so am I in trouble?

              • 4. Re: Cross Tab Tables in the Web Viewer using html
                beverly

                Yes! cool JSON by sam! the "data" still needed to be gathered and once you get the looping correct to do that, you can just HTML alone (as you have) or JSON or anything that helps.

                 

                beverly

                • 5. Re: Cross Tab Tables in the Web Viewer using html
                  sam_oda

                  Thanks, beverly!

                   

                  thehalpeen,

                  I rewrite the code for windows10,FileMaker15.

                  Copy and paste this to your webviewer, then you can see how this works.

                   

                  Let([
                  data="[{name:\"John\",age:\"52\",weight:\"60kg\"},{name:\"Sam\",age:\"32\",weight:\"50kg\"},{name:\"Mary\",age:\"22\",weight:\"70kg\"}]";
                  code="data:text/html,<!DOCTYPE html><meta http-equiv='X-UA-Compatible' content='IE=edge' >
                  <body>
                  <script>
                  var firsrRow,key,row,cell,i,data=__DATA__;
                  firstRow=document.createElement('div');
                  document.body.appendChild(firstRow);
                  firstRow.style.display='flex';
                  firstRow.style.width='100%';
                  firstRow.style.border='1px solid';
                  for(key in data[0]){
                      cell=document.createElement('div');
                      cell.style.flex=1;
                      cell.style.display='flex';
                      cell.style['justify-content']='center';
                      cell.style['align-items']='center';
                      cell.style.border='1px solid #ccc';
                      cell.innerText=key;
                      firstRow.appendChild(cell);
                  }

                  for(i=0; i<data.length; i++){
                      row=document.createElement('div');
                      document.body.appendChild(row);
                      row.style.display='flex';
                      row.style.width='100%';
                      row.style.border='1px solid';
                      for(key in data[i]){
                          const cell=document.createElement('div');
                          cell.style.flex=1;
                          cell.style.display='flex';
                          cell.style['justify-content']='center';
                          cell.style['align-items']='center';
                          cell.style.border='1px solid #ccc';
                          cell.innerText=data[i][key];
                          row.appendChild(cell);
                      }
                  }
                  </script>
                  </body>"]
                  ;
                  Substitute(code;"__DATA__";data)
                  )

                  1 of 1 people found this helpful
                  • 6. Re: Cross Tab Tables in the Web Viewer using html
                    beverly

                    Wonderful, sam! Your addition of:

                    <meta http-equiv='X-UA-Compatible' content='IE=edge' >

                    is probably what helps in Windows.

                     

                    Welcome all to the world of web developing! We have as many work arounds to make it work 'everywhere' as we do for FM (sometimes). LOL

                     

                    Do you have any tricks (Custom Function, perhaps) that would help "gather" the data for thehalpeen?

                    beverly

                    • 7. Re: Cross Tab Tables in the Web Viewer using html
                      thehalpeen

                      Sam,

                       

                      Thanks once again.

                       

                      Ok, we have one row per person. Lets say we had a heading/column called 'Allergies and we wanted to display these.

                       

                      Johns Allegeries:

                      Dairy Produce

                      Cats

                      Disco Music

                      Meat

                       

                      Sams Allergies

                      Donald Trump

                      Dogs

                      Dairy Produce

                      Fish

                      Wheat

                      New Year Resolutions

                      Microsoft Access

                       

                      Mary's Allergies

                      Her mother-in-law

                      Football

                      Dairy Produce

                       

                      Can a 'Allergies' column get added to display these allergies for each person?

                      • 8. Re: Cross Tab Tables in the Web Viewer using html
                        sam_oda

                        This technique is not only for the cross tab.When I pass the data from FileMaker to webviewer, I usually make the data text using ExecuteSQL function.

                        for example,

                         

                        "[{" & ExecuteSQL(sql; "\"," ; "\"},{" ) & "\"}]"

                         

                        I make the sql text like this.

                        "SELECT 'name:\"' || sample.name FROM sample"

                         

                        I prepend the label for data.

                        In this case, 'name:\"'

                        The label is the key of object, and data is the value of the property.

                        (|| operator concatenates the label with data)

                        Screen Shot 2017-01-05 at 23.54.05.png

                        The sample code above is only for simple case.

                        If you wonder how to use ExecuteSQL for cross tab, this site might be helpful for you, and is the answer for your question.

                         

                        https://filemakerhacks.com/2013/04/17/outer-joins-in-filemaker-12-part-3/

                        3 of 3 people found this helpful
                        • 9. Re: Cross Tab Tables in the Web Viewer using html
                          thehalpeen

                          Sam,

                           

                          Apologies for not getting this.

                           

                          I can't see from the link, how to construct the array that will populate the table.

                           

                          I don't know if it will help, but I have attached the file with the data. The layout called People_Shift_List contains records of the shifts. Some employees can have more than 1 shift on a particular day.

                           

                          Attached is how it looks using Filemaker portals. Each shift shown on the report, is a record in the Join table. Each line could equal 7 records in the join table called People_Shifts

                          roster for sam.PNG

                          • 10. Re: Cross Tab Tables in the Web Viewer using html
                            thehalpeen

                            I don't know if you or anyone can answer this, but when I hit the refresh button on the Filemaker hacks demo file, nothing appears. Would this have anything to do with me being in Europe and usind a European date system on my PC and the file having US dates?

                            • 11. Re: Cross Tab Tables in the Web Viewer using html
                              sam_oda

                              Hi, thehalpeen. I modified your file.

                              Screen Shot 2017-01-06 at 21.09.39.png

                               

                              Let(

                                 code="data:text/html,<!DOCTYPE html><meta http-equiv='X-UA-Compatible' content='IE=edge' >
                                 <body>

                                <script>

                              var firsrRow,key,row,cell,i,data=__DATA__;

                              firstRow=document.createElement('div');

                              document.body.appendChild(firstRow);

                              firstRow.style.display='flex';

                              firstRow.style.width='100%';

                              firstRow.style.border='1px solid';

                               

                              for(key in data[0]){

                                 cell=document.createElement('div');

                                 cell.style.flex=1;

                                 cell.style.display='flex';

                                 cell.style['justify-content']='center';

                                 cell.style['align-items']='center';

                                 cell.style.border='1px solid #ccc';

                               

                                 if(key=='Name'){

                                 cell.innerText=key;

                                 firstRow.insertBefore(cell,firstRow.querySelector('div'));

                                }else{

                                 cell.innerText=formatDate(key);

                                 firstRow.appendChild(cell);

                                }

                               

                              }

                              for(i=0; i<data.length; i++){

                                 row=document.createElement('div');

                                 document.body.appendChild(row);

                                 row.style.display='flex';

                                 row.style.width='100%';

                                 row.style.border='1px solid';

                                 row.style.height= '100px';

                                 for(key in data[i]){

                                 const cell=document.createElement('div');

                                 cell.style.flex=1;

                                 cell.style.display='flex';

                                 cell.style['justify-content']='center';

                                 cell.style['align-items']='center';

                                 cell.style.border='1px solid #ccc';

                                 if(key=='Name'){

                                 cell.innerText=data[i][key];

                                 row.insertBefore(cell,row.querySelector('div'));

                                }else{

                                 cell.innerHTML=displayShift(data[i][key]);

                                 row.appendChild(cell);

                                }

                               

                                }

                              }

                              function displayShift(shiftValues){

                                 console.log('displayShift');

                                 if(shiftValues){

                                 var shiftAll=['morning(7-3)','afternoon(3-9)','daily(9-5:30)','evening(5:30-11:30)'];

                                shiftValues=10000+shiftValues+'';

                                shiftValues=shiftValues.split('');

                                shiftValues.shift();

                                shiftValues.reverse();

                                 console.log(shiftValues);

                                shiftValues=shiftAll.filter(function(item, index){

                                 return shiftValues[index] == '1'
                                 });

                                 return shiftValues.join('<br>');

                                }else{

                                 return '';

                                }

                               

                              }

                               

                              function formatDate(dateText){

                                 var arr=dateText.match(/(\d{4})(\d{2})(\d{2})/);

                                 var theDate=new Date(Number(arr[1]),Number(arr[2])-1,Number(arr[3]) ) + '';

                                 var ymd = theDate.split(' ');

                                 return Number(ymd[2]) + ' ' + ymd[1] + ' ' + ymd[0]

                               

                              }

                               

                               

                              </script>

                              </body>"
                              ;

                              Substitute(code;"__DATA__";

                              customMakeObjectArray(

                                 "SELECT 'Name:\"'||People.EmployeeName,
                              '\"20170102\":\"'||(SELECT SUM(PeopleShifts.shiftValue) FROM PeopleShifts WHERE ShiftDate='20170102' AND PeopleShifts.EmployeeName = People.EmployeeName),

                              '\"20170103\":\"'||(SELECT SUM(PeopleShifts.shiftValue) FROM PeopleShifts WHERE ShiftDate='20170103' AND PeopleShifts.EmployeeName = People.EmployeeName),

                              '\"20170104\":\"'||(SELECT SUM(PeopleShifts.shiftValue) FROM PeopleShifts WHERE ShiftDate='20170104' AND PeopleShifts.EmployeeName = People.EmployeeName),

                              '\"20170105\":\"'||(SELECT SUM(PeopleShifts.shiftValue) FROM PeopleShifts WHERE ShiftDate='20170105' AND PeopleShifts.EmployeeName = People.EmployeeName),

                              '\"20170106\":\"'||(SELECT SUM(PeopleShifts.shiftValue) FROM PeopleShifts WHERE ShiftDate='20170106' AND PeopleShifts.EmployeeName = People.EmployeeName),

                              '\"20170107\":\"'||(SELECT SUM(PeopleShifts.shiftValue) FROM PeopleShifts WHERE ShiftDate='20170107' AND PeopleShifts.EmployeeName = People.EmployeeName)

                              FROM People"
                              )

                              )

                              )

                              2 of 2 people found this helpful
                              • 12. Re: Cross Tab Tables in the Web Viewer using html
                                thehalpeen

                                Wow, Wow Wow. I am impressed by the knowledge levels on this Filemaker discussion site, but this is up there with the most impressive stuff I have seen in relation the Javascript and the Webviewer, etc..

                                 

                                In another post, I had a contributor called 'erolst' and 'philmodjunk' who help me with producing cross tab report in Filemaker and that was fantastic, but this is equally fantastic.

                                 

                                Because, my knowledge of Javascript is limited, I am going to have to study this code to try and make out what is going on.  But let me say I am very grateful for your effort!! This is very exciting!

                                • 13. Re: Cross Tab Tables in the Web Viewer using html
                                  sam_oda

                                  Thanks, thehalpeen

                                  I will be glad if I was of any help.

                                  • 14. Re: Cross Tab Tables in the Web Viewer using html
                                    thehalpeen

                                    Sam,

                                     

                                    I think I will have to get a friend who is very familiar with Javascript to help me with the code. Not to worry. In the meantime I'm going to study Javascript over the weekend.

                                     

                                    Some Observations

                                    In the Join table, I added an entry for Paul McCartney on 6th January, but all the data from the web Viewer disappeared. When I deleted the entry, the data reappeared. However, if I add an entry on the 2 Jan, the Web Viewer updates.

                                     

                                    I noticed you added a column called 'Shift Value' to the Shifts Table and the numbers varies from 10 to 1000. If I wanted to add another shift - say a night shift  11.30pm to 7am  - what code do I enter in this column for this new shift?

                                     

                                    You have a function called DisplayShift. You have a line - var shiftAll=['morning(7-3)','afternoon(3-9)','daily(9-5:30)','evening(5:30-11:30)']; - If I add the night shift, do I have to hard-code it in here?

                                     

                                    Does this table in the Web Viewer limit the entries to 4 items per row?

                                    1 2 Previous Next