Discussions

Expand all | Collapse all

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

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

    Posted 10-03-2018 11:32
    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?



  • 2.  RE: Java code for DoQuery and return value from another table to populate field

    Top
    Contributor
    Posted 10-03-2018 11:50
    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();
    })


  • 3.  RE: Java code for DoQuery and return value from another table to populate field

    Posted 10-03-2018 12:32
    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.


  • 4.  RE: Java code for DoQuery and return value from another table to populate field

    Top
    Contributor
    Posted 10-03-2018 12:36
    add debugs:

    console.dirxml(xml)

    console.log(keyword)


  • 5.  RE: Java code for DoQuery and return value from another table to populate field

    Posted 10-03-2018 15:19
    What Java script editors do people use? Good hint re the debugging.


  • 6.  RE: Java code for DoQuery and return value from another table to populate field

    Posted 10-04-2018 09:27
    ***** 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(); 
    })
       
    });
      
    });
    })();


  • 7.  RE: Java code for DoQuery and return value from another table to populate field

    Top
    Contributor
    Posted 10-04-2018 09:39
    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();