Forum Discussion

JackFretwell's avatar
JackFretwell
Qrew Assistant Captain
7 years ago

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

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?

APIs and custom code
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    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();
      })
  • JackFretwell's avatar
    JackFretwell
    Qrew Assistant Captain
    7 years ago
    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.
  • JackFretwell's avatar
    JackFretwell
    Qrew Assistant Captain
    7 years ago
    What Java script editors do people use? Good hint re the debugging.
  • JackFretwell's avatar
    JackFretwell
    Qrew Assistant Captain
    7 years ago
    ***** 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(); 
    })
       
    });
      
    });
    })();
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      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();