How to aggregate different tables in one

[neilw]

neilw,

OT: Your post made me think of something: how useful would be to have an extension/script/template that builds a table from a dataset that contains columns and row information? I would imagine that it could come in quite handy, no?

Oui.

I've actually been working on a bunch of related stuff (too bad it's only a side task for me or I'd get some of it done) because we do this kind of data extraction and table construction all the time. I've been struggling to think of the smartest and most flexible way to generalize this into an extension. I figured that at some point, when I finish all these various hand-built implementations, I could look at them in aggregate and try to extract a general model.

It is a problem worth thinking about. Making this sort of thing very easy to code would enable more users to implement it without needing to become Dekiscript experts.

For now, though, I'm focused on some other cool stuff (in-line editable sortable tables), which I hope to share if and when I can ever get it all working.

[sborny09]

Hi NeilW,
I have looked at the code and unfortunately am stumped at the first hurdle. I know you are busy but would appreciate it if it would be possible to get a basic working model for just one section and then I should be able to add the additional sections etc.

I hope you are in a position to help.

Thanks in advance.

[sborny09]

Hi there,

I was just wondering if there is anyone out there that would be willin gto help me on this. I have tried to see if I can get this working but as I am so new to the scripting side of things I can't seem to get my head round it. I know I am being cheeky but would really appreciate any help.

Thanks in advance

Symon.

[rberinger]

neilw,

OT: Your post made me think of something: how useful would be to have an extension/script/template that builds a table from a dataset that contains columns and row information? I would imagine that it could come in quite handy, no?

SteveB,

I think that would be a very worth while extention/functionality. After all what is a table other than a recordset. I think this could expose an awful lot of data to the end user without being over complicated for the end user to take advantage of.

Here is my thinking on this as an example:

foreach(var p in Pages) {
  foreach(tbl in p.tables) {
    foreach(row in tbl.rows) {
       /* Do some cool stuff with table data */
       /* At this point we could start doing calculations, formatting, data comparison etc. */
       /* Could even be a great start to some simple spread sheet functionality */
      
       var field1 = row.td(0);
       var field2 = row.td(1);
       var field3 = row.td(2);
       var totals = row.td(2) + row.td(2);

    }
  }
}

Huge undertaking I know but you guys at Mindtouch always seem to like a challenge.

[sborny09]

Hi Again,

I have been given a change of plan. Is it possible to swap the columns and rows around. The reason is because I have a set number of pages (11) and would like them to run across the top and the sections to run down the left. We could have a lot of sections and would rather the page got longer and not wider.

Please can someone help.

Thanks.

[neilw]

Hi Again,

I have been given a change of plan. Is it possible to swap the columns and rows around. The reason is because I have a set number of pages (11) and would like them to run across the top and the sections to run down the left. We could have a lot of sections and would rather the page got longer and not wider.

Please can someone help.

Thanks.

Can you provide a new picture of your desired finished table format?

[sborny09]

Can you provide a new picture of your desired finished table format?

Hi NeilW,

Thanks for such a prompt response. What I am now looking to do it to have a table like Fig.1 on all 11 pages and to aggregate that information into a table that lokks like fig. on a different page.

Hope that makes sense.

[neilw]

Hi NeilW,

Thanks for such a prompt response. What I am now looking to do it to have a table like Fig.1 on all 11 pages and to aggregate that information into a table that lokks like fig. on a different page.

Hope that makes sense.

That looks like no problem. Give me a day or two and I'll throw some code over.

[sborny09]

That looks like no problem. Give me a day or two and I'll throw some code over.

Thanks you very much. You are a life saver.

[neilw]

OK, here you go:

  <div init="var tablepagename = 'summary'; // replace this with the real page name that has the table
var sitedata = [];
var fields = [ 'Diary', 'Chart', 'PM', 'IM' ];
foreach (var p in list.sort(map.values(page.subpages),'title')) {
  var x = p.subpages[tablepagename].xml;
  var data = { name:p.title };
  foreach (var f in fields) {
    let data ..= { (f):xml.text(x,'//tr[normalize-space(td)=\''.. f ..'\']/td[2]') };
  }
  let sitedata ..= [ data ];
}"> 
    <table cellspacing="0" cellpadding="3" border="1"> 
      <tbody> 
        <tr> 
          <td>&nbsp;</td> 
          <td foreach="var d in sitedata" style="background-color: #c3c3c3; ">{{ d.name }}</td> 
        </tr> 
        <tr foreach="var f in fields"> 
          <td style="background-color: #c3c3c3; ">{{ f }}</td> 
          <td foreach="var d in sitedata">{{ d[f] }}</td> 
        </tr> 
      </tbody> 
    </table> 
  </div>

You can see it in action here.

Notes:
1) In the first line, set the "tablepagename" variable to the name of the page in each site that contains the table. I called it 'summary'. Don't forget to use single quotes!
2) The code assumes correct capitalization of the row names in each table (Diary, Chart, PM, IM).
3) I assume this code will live on the parent page of all the sites. If not, some tweaks will be necessary.

Other than that, it's pretty straightforward. If you need any further help with it, let me know.