Java code for DoQuery and return value from another table to populate field

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
Help with code:

So imagine I have a customer quote table and a lookup table for reference values, things the customer selects, there are a lot so relationships would fail here as I need to connect to several columns, please don't ask why I'm not using relationships, I need something similar to an excel vlookup or a SQL update query.

At the moment I press the button and nothing happens.

This is the basic request : https://xxxxxxxx.quickbase.com/db/bn2igarv2?act=API_DoQuery&query={6.EX.%27BBLRBWA4SS4001%27}&am... and XML reply.

<qdbapi>
<action>API_DoQuery</action>
<errcode>0</errcode>
<errtext>No error</errtext>
<dbinfo>
<name>Process Costs</name>
<desc>Holds information for Daily Mailing Process Costs</desc>
</dbinfo>
<variables>
<__iol>&rand='+new Date().getTime())};"></__iol>
<iol>
<img qbu='module' src='/i/clear2x2.gif' onload="javascript:if(typeof QBU=='undefined'){QBU={};$.getScript(gReqAppDBID+'?a=dbpage&pagename=
</iol>
</variables>
<chdbids> </chdbids>
<record>
<peritem>0.35</peritem>
<update_id>1537874531635</update_id>
</record>
</qdbapi>

I'm trying to recover the value in the <peritem> tag and put this in the customer quote field _fid_59.

I'm getting closer, I have a button set up in an "-" field and have gone through checks to see if the iol is loading correctly, I followed the instructions here :


The Button at the moment has this formula:

[iol] & "update.js" & [/iol]
&
"<a class='QBU_Button Vibrant Success' " &
"data-rid='" & [Record ID#] & "'" &"
>Update Quote [Name Field]</a>"
---------------------------------------
Page Name: update.js

function(){
  $("img[qbu=module]").remove();
  $("a.QBU_Button").css({"margin-top": 10});
  $("a.QBU_Button").parent("td").css({"border-top": 0});

  var dbid = "xxxxxxxx";
  var dbidRecords = "bn2h9ha3c";   // the table I'm on 
  var dbidReference = "bn2igarv2"; // the table i'm trying to get data from

  var apptoken = "apptokengoeshere";
  $.ajaxSetup({data: {apptoken: apptoken}});
  
$("a.QBU_Button").on("click", function(event) {

    var rid = this.dataset.rid;
    var keyword = _fid_24.value; //for the moment I'm using the hardcoded query below which returns '0.35'

    $.get(dbidReference, {
      act: "API_DoQuery",
      query: `{6.EX.'BBLRBWA4SS4001'}`,
      clist: "9",
    }).then(function(xml) {
      $("peritem", xml).each(function() {
       _fid_59.value += '$("peritem", this).text();' // not sure what this line is doing
      })
    });
  });

Any thoughts on where I'm going wrong?

Photo of Jack Fretwell

Jack Fretwell

  • 464 Points 250 badge 2x thumb

Posted 1 month ago

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

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
I think this is the offending line:
      _fid_59.value += '$("peritem", this).text();' // not sure what this line is doing
To confirm you can stuff  a value in fid=59 replace with this:
      _fid_59.value = "foo";
If your query is guaranteed to return a single record:
  _fid_59.value = $("peritem", xml).text();

If your query returns multiple records and you want to concatenate them into fid=59 try this:
  $("peritem", xml).each(function() {
_fid_59.value += $("peritem", this).text();
})
(Edited)
Photo of Jack Fretwell

Jack Fretwell

  • 464 Points 250 badge 2x thumb
Thanks, just tried the = "foo"; so it may be something more fundamental.  I'll take the thing apart and write it again.  Thank you for replying.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
add debugs:

console.dirxml(xml)

console.log(keyword)
Photo of Jack Fretwell

Jack Fretwell

  • 464 Points 250 badge 2x thumb
What Java script editors do people use? Good hint re the debugging.
Photo of Jack Fretwell

Jack Fretwell

  • 464 Points 250 badge 2x thumb
***** it works! ***** 

Thank you for all the pointers!

(function(){

$("img[qbu=module]").remove();
$("a.QBU_Button").css({"margin-top": 0});
$("a.QBU_Button").parent("td").css({"border-top": 0});

let dbid = "bnwnjkext";
let dbidRecords = "bn2h9ha3c";
let dbidReference = "bn2igarv2";

$("a.QBU_Button").on("click", function(event) {

let rid = this.dataset.rid;
let refcode = this.dataset.code;

$.get(dbidReference , {
act: "API_DoQuery",
query: `{6.EX.${refcode}}`,
clist: "9"
}).then(function(xml){
        $("record", xml).each(function() {
_fid_59.value = $("peritem", xml).text(); 
})
   
});
  
});
})();
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
I would think it does not work as you think it does:

(function() {
  $("img[qbu=module]").remove();
  $("a.QBU_Button").css({
    "margin-top": 0
  });
  $("a.QBU_Button").parent("td").css({
    "border-top": 0
  });
  let dbid = "bnwnjkext";
  let dbidRecords = "bn2h9ha3c";
  let dbidReference = "bn2igarv2";
  $("a.QBU_Button").on("click", function(event) {
    let rid = this.dataset.rid;
    let refcode = this.dataset.code;
    $.get(dbidReference, {
      act: "API_DoQuery",
      query: `{6.EX.${refcode}}`,
      clist: "9"
    }).then(function(xml) {
      $("record", xml).each(function() {
        _fid_59.value = $("peritem", xml).text();
      })
    });
  });
})();

The body of the each loop is performing the exact same assignment for every. record. You might be getting confused because this statement alone concatenates all the <peritem> values into on string and assigns it to fid=59
_fid_59.value = $("peritem", xml).text();
(Edited)