Want to reproduce a Summary Cross tab Report using JavaScript or JavaScript libraries such as underscore, to be imported as a new table just with the results.

  • 1
  • 2
  • Question
  • Updated 4 years ago
  • Answered

Want to reproduce a Summary Cross tab Report using JavaScript or JavaScript libraries such as underscore, to be imported as a new table just with the results.

Reason want to to import the results of this summary report to another table using import to CSV.

By the way I have accomplished lots of things with Dan Diebolt techniques via Import to CSV API

I have a query which I can reproduce using a promise.

The query returns VENDOR, PRODUCT, QUANTITY, VALUE

The promise returns an xml function (quickbase xml).

I am able to produce an array of arrays VENDOR =[vendor1, vendor2 etc.]

PRODUCT={product1, product2, etc.

I am able to create a CSV suitable to be reimported, with some manipulations in between.

I don’t know how to produce an ARRAY of objects that can be manipulates with underscore. Or an ARRAY of ARRAYS.

RECORDS = [{NAME: name1, PRODUCT: product 1 etc. }, {NAME:name2, PRODUCT: product2, etc.}, etc.]


or RECORDS = ["name1, product1, etc", "name2, product2, etc", "etc"]

I want to produce a summary report Grouping VENDOR, Crosstab Products, summing quantities and values.

The result table should look like this

NAME PRODUCT 1 QUANTIY 1 VALUE 1 PRODUCT 2 QUANTIY 2 VALUE 2

Name1 “XX” xx xx

There are 10 products.

Anyone can help?

Photo of eduardo

eduardo

  • 40 Points

Posted 4 years ago

  • 1
  • 2
Photo of Rama

Rama

  • 0 Points
first create a blank array

var stuff = [];

then create your object

var thing = {"attr1": "hey", "attr2": 10};
then push the object to your array
stuff.push(thing)
the creation of the object and the pushing of the object to the array should be done within a loop so you can assign and push all values from the xml QB gives you.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Let's say your crosstab summary report had qid=5. You can gain access to the raw data of the crosstab report using the API_GenResultsTable with jsa=1. The code in the following pastie will yield the response as an array in the global variable qdb_data when the promise resolves:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=363


This method returns the raw data of the crosstab report which you can import into a secondary table using API_ImportFromCSV after formatting and relevant processing. Note that the data in this secondary table will only be up to date immediately after importing. You may want to call API_PurgeRecords to empty the secondary table immediately before doing the import.
Photo of eduardo

eduardo

  • 40 Points
Rama thanks a lot. I was already trying an approach similar to dans suggestion.
Photo of eduardo

eduardo

  • 40 Points
Thanks Dan I am going to test it and return back.

I didn’t know that genresults table could be used with cross tabs, so I was trying to reproduce the cross tab report with underscore and then using your import csv technique to push values.

I created an array of unique salespeople; vendedoresUniqu

Did the count by with underscore for different products (in this case 3)

This created three OBJECTS (numFlat, numControle, numWeb), with this structure

Salesman: value

Value was the number of products sold for each different salesman.

I was trying to reproduce the csv using the push technique, that so much happiness brought to my small company.

Push the first salesman, then for that salesman I vas trying to push the correspondent value of the salesman name in the three objects. I got stuck in this process.
Salesman name1,valueproduct1,valueproduct2, valueproduct3
Salesman name2,valueproduct1,valueproduct2, valueproduct3
etc

The thing is that I need to grab some understanding of this cause I need to push values from other report to achieve what I want that is to have a visual representation of what they sold, comparing with the goal of the month, for each product in a table. Then I can do some formulas in the report, and put some colors, and push my salespeople towards the goal.

This is something we now do in excel, but I have all the information in quickbase

My code

$.getScript(url, function() {
  // response is available in global variables:
  // qdb_numcols
  // qdb_numrows
  // qdb_heading
  // qdb_data
  console.log(qdb_data);
 
 var vendedores = _.pluck(qdb_data, "1");
 console.log(vendedores);
 var vendedoresUniq = _.uniq(vendedores);
 console.log(vendedoresUniq);
 var flat = _.where(qdb_data, { 3: "FLAT"});
 var controle = _.where(qdb_data, { 3: "CONTROLE"});
 var web = _.where(qdb_data, { 3: "WEB"});
 var numFlat = _.countBy(flat, "1");
 var numControle = _.countBy(controle, "1");
 var numWeb = _.countBy(web, "1");
 console.log(numFlat);
 console.log(numControle);
 console.log(numWeb);

 $.each(vendedoresUniq, function( index, value ) {
  


csv_line = [];
var newValue = value.replace(/"/g, "");
//alert(newValue);
csv_line.push(newValue);
csv_line.push(_.pluck(numFlat, 'newValue'));
csv_line.push(_.pluck(numControle, 'newValue'));
csv_line.push(_.pluck(numWeb, 'newValue'));

tabelaVendas.push('"' + csv_line.join('","') + '"');


 });
 var csv_data = tabelaVendas.join("\n");
 console.log(tabelaVendas);

etc
Photo of eduardo

eduardo

  • 40 Points
Dan just to understand is do query or gen results.
Photo of eduardo

eduardo

  • 40 Points
Dan console.log(qdb_data), retrieves an array of arrays, but no data of the cross tab report just

0: "1"
length: 1

in each array. Used gen results
Any ideas of what I may be doing wrong
Photo of eduardo

eduardo

  • 40 Points
Dan one of the impressive things I am trying to achieve without success is to read a summary report in qdb_data global variable (pastie 363)

Have tried a couple of things such as:

//TABLE DASHBOARD PAGE ========================================
    //alert("You are on the Table Dashboard Page");
    $(".QBU_MyClass").on("click", function(event) {
      var rid = $(event.target)[0].dataset.rid;
      alert("Do Something Impressive\n[Record ID#] = " +rid );
      var promise = $.getScript(dbid + "?a=API_DoQuery&qid=" + qid + "&jsa=1");

$.when(promise).then(function() {
console.log(qdb_data);
 // your raw data will be available in the variable qdb_data
});

My dbid apptoken qid etc are correct

Any insight for this?
Photo of eduardo

eduardo

  • 40 Points
My quid is a summary report no cross tabs
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
>My quid is a summary report no cross tabs

Paste the exact URL of your summary report that your sript is using into the address bar and inspect the result. I think the result will reveal why it it not working.

Perhaps you should just query for the unsummarized data and then use Underscore's _.groyupBy() method to do the grouping. See

http://underscorejs.org/#groupBy

Underscore's groupBy() method will allow you to group on anything - not just the few options listed in QuickBase's summary report builder.

BTW we have secured the fake venue for the Mas Que Nada Brazilian User Group Conference - it will be glorious!