Array Generation From Table Data

[nmalawskey]

Maybe a dumb question, but is it possible generate a javascript array from tabular data contained within a datapage report?

 

Example, I have a tabular report of 35 entries. What I'd like to do is generate a javascript array that I could then use to build an svg graphic using d3.js within a html section of the datapage. 

 

What I guess I'm wondering is, aside from me building the array through scripting, does Caspio already use arrays when creating a table and could I just piggyback off that array?

 

Any thoughts?

 

Thanks.

[nmalawskey]

Hey never mind, I think I figured it out on my own.

 

In the footer:

 

 

 

var TableData = new Array();

    

$('#.cbResultSetTable tr').each(function(row, tr){

    TableData[row]={

        "Name" : $(tr).find('td:eq(0)').text()

        , "State" : $(tr).find('td:eq(2)').text()

        , "Amount" : $(tr).find('td:eq(4)').text()   

    }

}); 

TableData.shift();  // first row is the table header - so remove

TableData.splice(-1,1);

 

console.log(TableData);

[nmalawskey]

Hrmm but obviously now that I think about it that will only pull the displayed data, and not the full data associated with a table. 

 

So back to square one I suppose.

[DataCobalt]

Potentially you could use calculated fields with SQL to run the queries and then pull the data down with javascript?

 

What are your data types and what are you trying to pull in the end?

[nmalawskey]

I'm trying to create an array of data based off of the numbers contained in a filtered table.

 

I'm not familiar with SQL, but work extensively with javascript

 

Essentially, say I have 200 records in a table like this:

 

Name, Date, Amount

Bob, 9/16, 250

Tom, 8/16, 150

 

I'm trying to generate an array that would be:

 [

  {

    "name": "Bob",

    "amount": 250 

  },

  {

    "name": "Tom",

    "amount": 150

  },

  etc...

]

 

for the entire table based on applied filters -- not just what is being displayed (which I was able to do above). I don't know enough about the guts of caspio to do it, however.

 

I'm going to try and pull apart the mapmash up, maybe there's clues in there...

[DataCobalt]

Are you wanting to aggregate this data? Like show how much total or how many times "Bob" appeared in the table?

 

Also thanks for mentioning d3.js, that thing is amazing!

[nmalawskey]

Are you wanting to aggregate this data? Like show how much total or how many times "Bob" appeared in the table?

 

Also thanks for mentioning d3.js, that thing is amazing!

 

 

Correct. My goal would be to build a pie chart (or other viz) that shows the percentage of the total attributed to each individual name.

 

Yeah I mostly work in d3. It's getting the data to d3 that can be tricky :-) 

[jgorny]

I asked support about similar aggregation issues. Essentially each SQL call in a calculated field is a 1-1 - so you can grab extra an extra row of data but you can't really grab sets based on a single value -

 

UNLESS

 

- you use a FOR XML construct. I am spending some time digging into this aspect of MSSQL because SQL Server 2016 is providing FOR JSON which will allow for similar aggregations but formatted in a way that is easier to work with in javascript than XML. You get a XML recordset. Haven't played around yet with it.

 

The example code they gave me (since I want to build family trees/relationship diagrams with d3) for calc field aggregations is:

select convert(nvarchar, date_field, 103) + N' - ' , cast(COUNT(date_field) as nvarchar) + N' | ' from table_name where user_id = [field:idfield] group by date_field for xml path(N'')

[munkitkat]

Thanks for sharing! I also have the same problem