Using JavaScript to interact with tables and fields

  • 1
  • 1
  • Question
  • Updated 6 months ago
  • In Progress
I have a lot of experience using Java, and although there are plenty of similarities, I have never used JavaScript prior to now. I have set up an IOL, the problem I am trying to solve involves looping through a table and accessing specific fields. My questions are:

1. How do I access a specific field
2. Is there a way to access a specific field AND row
3. Is there a call similar to EOF (End of File, for knowing when I reach the end of the table)
4. Is there any doc for JavaScript and table manipulation concepts

One thing to note, queries won't help me solve this problem, because I need to guarantee I don't loop through the same values I have already visited... Unless I am missing something.

Thanks for any help or any material that I can reference
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb

Posted 7 months ago

  • 1
  • 1
Photo of Scott

Scott

  • 314 Points 250 badge 2x thumb
Howdy John,

You should really message Dandiebolt.  He's pretty much the undisputed champion when it comes to all things Javascript, jQuery, Service Works/IOL in the Quickbase sector.

He's found here: https://getsatisfaction.com/people/dandiebolt

(Contact info in the left panel)
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb
That's not a real answer. Would still appreciate anybody's help.
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,758 Points 5k badge 2x thumb
Hi John,

You can access any field with the help of field ID.

Thanks,
Gaurav
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,954 Points 20k badge 2x thumb
You questions is a bit broad but I will try to answer.

The most common way to "interact with table and field" is to (1) perform an AJAX request using the QuickBaser HTTP API and (2) process the XML response. Historically this is done using jQuery because jQuery is already loaded in QuickBase's pages but these days everything can be done with raw JavaScript. Here is an example of typical code entered manually into the console to query the formula function table for all records with [Category]="Type Conversion":

(function(){
  var dbid ="9kaw8phg";
  var dbidTable = "6ewwzuuj";
  var apptoken = "not required";
  $.ajaxSetup({data: {apptoken: apptoken}});
  $.get(dbidTable, {
    act: "API_DoQuery",
    query: "{6.EX.Type Conversion}",
    clist: "3.7.8"
  }).then(function(xml) {
    console.dirxml(xml);
    $("record", xml).each(function() {
      var rid = $("record_id_", this).text();
      var name = $("function_name", this).text();
      var type = $("result_type", this).text();
      console.log(rid, name, type);
    });
  });
})();




I would encourage you to perform this test yourself and explore the console output of the xml response and individual fields.
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb
Dan:

Thanks a bunch, this helped me a ton with how to start and what I need to learn. A few comments/questions about the syntax/logic. Feel free to let me know anything about my thought process here:

1. I understand we need to create an anonymous function for the .each() call so that we can loop through every record and retrieve its attributes
2. I understand what get() conceptually should do. The dbidTable needs to be referenced on every iteration of the each() loop
3. The .then() is a promise to be fulfilled if the get() call is successful. However, what value is stored into 'xml' from get() if it's successful. And WHY do we need to pass 'xml' to our selection along with record? Why are double quotes allowed, when the literature I have read for this requires single quotes?
4. Can you briefly explain the necessity for the 'this' keyword in the selection. I have used it plenty, but maybe there are subtleties I don't quite understand in this statement.

Thanks again, I have learned quite a bit so far.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
I should have included the URL of the table in my reply:

https://login.quickbase.com/db/6ewwzuuj?a=td

You should visit this URL, press F12 and paste my code into the console.

As to your questions:

1. I understand we need to create an anonymous function for the .each() call so that we can loop through every record and retrieve its attributes

You don't normally think in terms of looping each record - rather you perform functional methods such as map(), filter(), reduce(), each() on the selection.

2. I understand what get() conceptually should do. The dbidTable needs to be referenced on every iteration of the each() loop

dbidTable is a relative URL which is just the dbid for the table you are accessing. The $.get() returns an XML document. The following statement selects each <record> in the XML response and processes it:

$("record", xml).each(function() {...}

3. The .then() is a promise to be fulfilled if the get() call is successful. However, what value is stored into 'xml' from get() if it's successful. And WHY do we need to pass 'xml' to our selection along with record? Why are double quotes allowed, when the literature I have read for this requires single quotes?

XML holds the response body of the GET request. If you expand the #document in the console you can see the XML response:



The double quotes are just placed around JavaScript strings. I think your reference to single quotes in the documentation is in forming query clauses which QuickBase documents like this:

{'3'.EX.'123'}

Why QuickBase does this I don't know because you don't need the single quotes.

4. Can you briefly explain the necessity for the 'this' keyword in the selection. I have used it plenty, but maybe there are subtleties I don't quite understand in this statement.

In my code this refers to the individual record being processed by the function inside the each method:

    $("record", xml).each(function() {
      var rid = $("record_id_", this).text();
      var name = $("function_name", this).text();
      var type = $("result_type", this).text();
      console.log(rid, name, type);
    });
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb
Thanks again Dan,
In your code:
 $("record", xml).each(function() {
      var rid = $("record_id_", this).text();
      var name = $("function_name", this).text();
      var type = $("result_type", this).text();
      console.log(rid, name, type);
    });
What is the optimal way to locate selection names when you don't know what they are called i.e. "record_id_", how did you know this was the name in the html? Am I able to reference the field ID from a selection statement?
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
The tag name is derived from the field label by lower-casing every letter and substituting an underscore (eg "_") for every non-alphanumeric character.

Perhaps another way to see the tag names QuickBase uses in the response is to just console log the XML response:
(function(){
  var dbid ="9kaw8phg";
  var dbidTable = "6ewwzuuj";
  var apptoken = "not required";
  $.ajaxSetup({data: {apptoken: apptoken}});
  $.get(dbidTable, {
    act: "API_DoQuery",
    query: "{6.EX.Type Conversion}",
    clist: "3.7.8"
  }).then(function(xml) {
    console.dirxml(xml);
    // more code here
  });
})();
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb
Thanks again for the help, Dan!

In this snippet:
 $("record", xml).each(function() {
      var rid = $("record_id_", this).text();
      var name = $("function_name", this).text();
      var type = $("result_type", this).text();
      console.log(rid, name, type);
    });

Why are we required to select BOTH 'record' and 'xml' in order to access those 3 elements, if those 3 elements are only a part of a record. If we are selecting two separate elements (in this case, record and xml) why doesn't this iterate through 'rid', 'name', and 'type' for both selections? It seems as though these selections are linked, but how? Aren't they distinct elements?

i.e. Why doesn't this work?:
$("record").each(function() { ... });

Another question. In each record of the XML response I have a field [Event Time] that stores a time. But this time is simply a large numeric number. How do I convert this numeric number to a duration?

i.e. 1. numDifference = numericNum1 - numericNum2
      2. convert numDifference to duration   

Would this work?
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
>Why are we required to select BOTH 'record' and 'xml'

The first argument is the selector which specifies what to look for - in this case <record> elements. The second argument is the context - in this case it is the XML document returned by the AJAX call. If you don't specify xml as the second argument it will default to the current document - namely the HTML page currently being displayed.

>i.e. Why doesn't this work?
>$("record").each(function() { ... });

Because without the xml as the second argument the the .each() method will be iterating of <record> elements in the current HTML page (there aren't any because <record> is not a recognized HTML tag). Again, specifying xml as the second argument tells jQuery to use the XML document returned by the AJAX call as the context.

>Another question. In each record of the XML response I have a field [Event Time] that stores a time. But this time is simply a large numeric number. How do I convert this numeric number to a duration?

In most programming languages time is represented as milliseconds since the start of the unix epoch (Midnight January 1, 1970). This code will get the milliseconds since the start of the unix epoch as an integer:

var ms = parseInt($("event_time", this).text(), 10);

But it isn't clear what the exact field type is for [Event Time].
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb
Thank you as usual, Dan!

[Event Time] is just a time of day. In the XML, since event_time is given to me as milliseconds, I converted these times to minutes and used Math.floor(time) since most were not whole numbers. Obviously not a very robust solution, do you have another idea?

Some context:

My overall goal is populate a field [Time Since Last Stop] for each record. Each [Event Time] represents when a vehicle has last stopped. So I wanted to iterate through each record and subtract  

(most recent stop - second most recent stop = time since last stop)

I have created a 2D array to store pairs of [driver name, event time]. My goal is to iterate through this array, store the driver from the current index in a variable, compare it to subsequent indices of the array with a nested loop until we find the next index with the same driver, then perform basic calculation from those two event times.

My question is:
If I know the index of the array that I am performing the calculation on, how can I guarantee I reach the actual record given this index? What is another way (if you have an explanation or link) to iterate through each record WITHOUT a .each() call?

Also, if you have a more ideal solution off the top of your head, please don't hesitate to let me know. I am still learning!
(Edited)