Having trouble getting an API call to fire.

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • In Progress
Hello I am a new Quickbase user having a bit of trouble writing a script to return a small sample of data. Below is my code,

var url = "domain";
url += "/db/";
url += "bm9rx6w83"; 
url += "?a=API_DoQuery";

var request = "<qdbapi>";
request += "<apptoken>tvy5zcdaqafyfxp43vhd78gyys</apptoken>";
request += "<query>{'20'.GT.'1000'}</query>";
request += "<clist>a</clist>";
request += "<options>sortorder-D</options>";
request += "</qdbapi>";

$.ajax({
type: "POST",
contentType: "text/xml",
async: false,
url: url,
data: request,
dataType: "xml",
processData: false,
success: function (response) {

var xml = $(response);
if(xml.find("qdbapi").find('errcode').text() == "0"){

processRecords(xml);

}
else
{
console.log("Quickbase returned an error.");
console.log(response);
}
     },
error.function (response) {
console.log("Quickbase Returned an error");
console.log(response);
}
});

function processRecords(xml)
{


xml.find('qdbapi').find('record').each(function(index){
console.log("Name: " + $(this).find("name").text());
console.log("Tuition: " + $(this).find("tuition").text());
console.log("Major: " +  $(this).find("major").text());
});
}

I replaced the domain name obviously so that is not the problem but I cannot get a post message to show up with the data. It is only three records in a student table. The only thing I get posted back when I run off MY Iframe button is 
 XHR finished loading: GET "https://domain/db/bm9rv78gs?a=dbpage&pagename=GetStudents.js&_=1512409558232"....

Anyone have any ideas? I am still pretty new with it so I could just be a gaf on my side.
Photo of William Marrone

William Marrone

  • 310 Points 250 badge 2x thumb

Posted 11 months ago

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

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Try this by pasting into the console (don't waste your time pasting into a code page):

(function() {
  var dbid = "<your application dbid>";
  var dbidTable = "bm9rx6w83";
  var apptoken = "tvy5zcdaqafyfxp43vhd78gyys";
  $.ajaxSetup({data: {apptoken: apptoken}});
  $.post(dbidTable, {
    act: "API_DoQuery",
    query: "{20.GT.1000}",
    clist: "a",
    options: "sortorder-D"
  }).then(function(xml) {
    console.dirxml(xml);
    var name, tuition, major;
    $("record", xml).each(function() {
      name = $("name", this).text();
      tuition = $("tuition", this).text();
      major = $("major", this).text();
      console.log(name, tuition, major);
    });
  });
})();
Photo of William Marrone

William Marrone

  • 310 Points 250 badge 2x thumb
That correctly returned the three records, is there any way to attach this to a Button to run from a homepage?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,332 Points 20k badge 2x thumb
> ... return the tuition as a number

tuition = parseFloat($("tuition", this).text());

> ... or convert it to summarize

Based on the names of your other fields (name and major) I would have assumed the record represented a person in the aggregate and that tuition would represent the current semester's tuition. If you need to summarize tuition you need to further describe your records and any child records along with how you want tuition to summarize (total, average, harmonic mean?)
Photo of William Marrone

William Marrone

  • 310 Points 250 badge 2x thumb
Thank you and yes that all makes sense. This student work is just a test app I am using to avoid using our live data in case something goes ary. The end goal and you could tell me if this is viable or not, we want to create a script to write data from a few different apps to get year to date totals of sales numbers. I don't know how much functionality Quickbase has when it comes to injecting code and if this is possible,
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,332 Points 20k badge 2x thumb
> ...  how much functionality Quickbase has when it comes to injecting code ...

Once you get to script you exceed the functionality of QuickBase and anything is possible.

If you write your code like this you will return JSON instead of XML and you will not have to process each record with $().each(...):

(function() {
  var dbid = "<your application dbid>";
  var dbidTable = "bm9rx6w83";
  var apptoken = "tvy5zcdaqafyfxp43vhd78gyys";
  $.ajaxSetup({data: {apptoken: apptoken}});
  
  var XMLFlatToObj = function(xml, type) {
  var data = [];
  var record = {};
  $("record", xml).each(function() {
    record = {};
    $(this).children().each(function () {
      record[$(this).prop("tagName")] = $(this).text();
    });
    data.push(record);
  });
  return {records: data};
  }
  var promise = $.ajax({
    url: dbidTable, 
    data: {
      act: "API_DoQuery",
      query: "{20.GT.1000}",
      clist: "a",
      options: "sortorder-D"
    },
    dataFilter: XMLFlatToObj
  });
  var promise = $.post(dbidTable, {
    act: "API_DoQuery",
    query: "{20.GT.1000}",
    clist: "a",
    options: "sortorder-D"
  })
  
  promise.then(function(response) {
    console.log(JSON.stringify(response, null, "  "));
  });
  
})();

To gather JSON from multiple AJAX calls you simply create additional promises and with a $.when(promise1, promise2, promise3 ...) call you can merge the individual JSON responses together into one overall JSON structure using $.extend(). The combined JSON  can be then summarized using underscore's  _.groupBy() method and templated to HTML using underscore's _.template() method.
Photo of William Marrone

William Marrone

  • 310 Points 250 badge 2x thumb
Does the Quickbase API have support for JSON objects and creating reports from them at all? Or would that require a bit of work around?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,332 Points 20k badge 2x thumb
The API does not return JSON so one solution is to use the code I provided which converts the XML to JSON using the data filter option and the helper utility XMLFlatToObj. BTW, I just noticed a typo in the code - depth the second "var promise =" statement - I am on a tablet tonight and forgot to remove it.