How do I total results from multiple lines in a DoQuery?

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

How do I total results from multiple lines in a DoQuery?

The Human Readable QuickBase Summary report looks like this:

Number of
Payments
25
25
25
7
TOT 82


The raw XML results for the same report using DoQuery as a URL on the browser's address bar looks like this:

<qdbapi>
<action>API_DoQuery</action>
<errcode>0</errcode>
<errtext>No error</errtext>
<dbinfo><name>REP Batch</name><desc/></dbinfo>
<variables></variables>
<chdbids></chdbids>

<record>
<number_of_payments>25</number_of_payments>
<update_id>1401297755209</update_id>
</record>

<record>
<number_of_payments>25</number_of_payments>
<update_id>1401297678550</update_id>
</record>

<record>
<number_of_payments>25</number_of_payments>
<update_id>1401297337064</update_id>
</record>

<record>
<number_of_payments>7</number_of_payments>
<update_id>1401226887676</update_id></record>

</qdbapi>


I'm attempting to get the total result, 82 as shown on the human readable report above, on an HTML page using the QuickBase API.

It seems that totaling the results one-by-one (line-by-line on the human readable report) from an array would be a way to go, but I'm stuck.

Using a method shared by Dan Diebolt (https://quickbase-community.intuit.com/questions/782176-how-does-one-code-an-api-do_query_count-from...), I can see that there are 4 rows returned, therefore 4 values to total.

I'm not sure how to total them.


When I applied the method mentioned above:


var dbid_EH_12_D_G = "https://-my-domain-.quickbase.com/db/-my-table-";
var qid_EH_12_D_G = "1000009";
var numMatches_EH_12_D_G = "" // set numMatches to blank
var number_of_payments_EH_12_D_G = "" // Set number_of_payments to blank



var promise_EH_12_D_G_Q = jQuery.get(dbid_EH_12_D_G,{
act: "API_DoQuery",
qid: qid_EH_12_D_G,
apptoken: "-My Token-"
});


var promise_EH_12_D_G_QC = jQuery.get(dbid_EH_12_D_G,{
act: "API_DoQueryCount",
qid: qid_EH_12_D_G,
apptoken: "-My Token-"
});


jQuery.when(promise_EH_12_D_G_QC).then(function(xml){
var numMatches_EH_12_D_G = $("numMatches",xml).text();

document.getElementById("AM_EH_12_D_Y").innerHTML=numMatches_EH_12_D_G;
});


*** This DoQueryCount returns the value 4, the count of lines on the human readable summary report.



jQuery.when(promise_EH_12_D_G_Q).then(function(xml){
var number_of_payments_EH_12_D_G = $("number_of_payments",xml).text();


document.getElementById("AM_EH_12_D_G").innerHTML=number_of_payments_EH_12_D_G;

*** This DoQuery returns all of the values visible in the human readable report, in reverse order: 7252525


// I did an experiment to try to parse out the individual numbers
document.getElementById("AM_EH_16_G").innerHTML=number_of_payments_EH_12_D_G[0];
document.getElementById("AM_EH_16_Y").innerHTML=number_of_payments_EH_12_D_G[1];
document.getElementById("AM_EH_16_R").innerHTML=number_of_payments_EH_12_D_G[2];
document.getElementById("AM_EH_16_P").innerHTML=number_of_payments_EH_12_D_G[3];

*** Treating this as an array returned individual digits: 7 2 5 2

*** I expected the different lines as shown in the raw XML return shown above.

});


How, specifically, could one total the number_of_payments as they are returned from QuickBase?


Thank you for your assistance!


- David



Photo of David_In_Tucson

David_In_Tucson

  • 206 Points 100 badge 2x thumb

Posted 5 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,904 Points 20k badge 2x thumb
Your code: tl;dr

Pastie Database

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=278


Notes:

jQuery does not have a reduce method so the "number of payments" data is returned as an array using $().map() and subsequently reduced using Underscore's _.reduce() method. There are a lot of different ways to do this but learning Underscore is well worth it.

There was a small typo in the original code which is now fixed.
Photo of David_In_Tucson

David_In_Tucson

  • 206 Points 100 badge 2x thumb
Thank you Dan.

I got an error message "Uncaught ReferenceError: _ is not defined ".   

Is there a statement to include the Underscore library that I'm missing?  

I tried including:
 <script src="http://underscorejs.org/underscore-min.js"&gt; </script>  
from Underscorejs.org and am still getting the error.

Thank you,

David
Photo of David_In_Tucson

David_In_Tucson

  • 206 Points 100 badge 2x thumb
Thank you Dan.

I see an added .Get();

I'm still getting
"Uncaught ReferenceError: _ is not defined bggi54tyn?a=dbpage&pageID=27:4370
(anonymous function) bggi54tyn?a=dbpage&pageID=27:4370
f.Callbacks.o jquery.min.js:2
f.Callbacks.p.fireWith jquery.min.js:2
w jquery.min.js:4
f.support.ajax.f.ajaxTransport.send.d"

Line 4370 is where the "_" is used.

Here is my paragraph of code (with the variables matching the rest of my program):


$.ajaxSetup({data: {apptoken: apptoken_EH_12_D}});

  jQuery.when(promise_EH_12_D_G_Q).then(function(xml){
   var number_of_payments_EH_12_D_G = $("number_of_payments", xml).map(function() {
    return parseFloat($(this).text());
  }) .get();
  var total_number_of_payments_EH_12_D_G = _.reduce(number_of_payments_EH_12_D_G, function(memo, nop) {return memo + nop; }, 0);
  alert(total_number_of_payments_EH_12_D_G);
});
 
if (am === true) {document.getElementById("AM_EH_12_D_G").innerHTML=total_number_of_payments_EH_12_D_G;}  // If morning, populate the AM field
   else {document.getElementById("PM_EH_12_D_G").innerHTML=total_number_of_payments_EH_12_D_G;}           // If afternoon, populate the PM field   
 


 
The Alert never pops up.  
The field PM_EH_12_D_G remains blank.

I'm sure that I'm missing something simple while translating your example to this program.   Your input is greatly appreciated.

Thank you,

David
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,904 Points 20k badge 2x thumb
If you are getting an error that indicates _ is undefined my code will terminate before the alert is ever executed. You need to include the underscore library from a cdn such as

CDN page for Underscore
http://cdnjs.com/libraries/underscore.js

https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.6.0/underscore-min.js

FWIW, most of the code I write is initially executed from the JavaScript console and when working added to a QuickBase user defined page with whatever glue is needed to integrate it to a form or page. When you code is injected into a QuickBase authored page you can use the libraries QuickBase has loaded (including jQuery and Underscore ...) and selectively read some of QuickBase's global variables. You only have to load those special libraries that QuickBase has not already loaded into their pages. However, if you are running your code our of a loaded user defined page or from a third party web site you have to load all your own libraries.
Photo of David_In_Tucson

David_In_Tucson

  • 206 Points 100 badge 2x thumb
Thank you Dan,

I was trying to include the library when I go the error.  Not sure what I got wrong.  I went to the UnderscoreJS.org site, viewed the library and then used the URL from the address bar as an include file.  
<script src="http://underscorejs.org/underscore-min.js"&gt; </script>  

In the console I just noticed a message that that include was not from a secure server and I see that the one you reference is.   Putting in the secure include made it work!   

I appreciate your quick contributions and am learning from what you share.  Thank you.   I'm looking forward to learning even more.

- David
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,904 Points 20k badge 2x thumb
Project home pages and Githup archives are not considered CDN's so you should only link to libraries hosted therein for light testing or exploration - otherwise you leach their bandwidth. You can of course host any library out of a user defined page or file attachment within QuickBase. Also, QuickBase has versions of these libraries but the path to them has a version number within it and over time these URLs age out of existence. For example, over time this URL will go away once QuickBase devops decides to no longer host version 60573-12:

https://quickbase.intuitcdn.net/res/60573-12/js/jquery-ui-1.8.18.min.js
Photo of David_In_Tucson

David_In_Tucson

  • 206 Points 100 badge 2x thumb
Thank you Dan.  Good point.   I could paste the library into one of the QuickBase's pages and link to that and know that it would continue to do what it started doing and nobody would change the links on us.
- David