Populating form fields with DoQuery results

  • 1
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
Hello. Apologies if I've missed a similar conversation. Within a form, I'd like a user to be able to fill in some fundamental data, then click a button and have a field within that form populated with a value returned from a DoQuery call that would reference those fundamental inputs. Note: the value to be populated would not be attainable via table to table relationships.
I've only recently started working with formula URL buttons and the API, and I'm struggling to get the two to work in tandem.

Any guidance would be much appreciated.
Thanks
Photo of Patrick Dunn

Patrick Dunn

  • 130 Points 100 badge 2x thumb

Posted 6 months ago

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

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,234 Points 20k badge 2x thumb
What I understand from your description is that:
  1. you are creating a new record or editing an existing record
  2. next, you want a button on the form the will take various values sitting in the open form and make an API_DoQuery request
  3. finally, you want to take the response of the API_DoQuery call and parse the results and stuff them into some other field on the open form
This is easy to do with script but there are two issues. (1) To the extent that the values sitting the open form are used in forming the API_DoQuery it is possible that these values may be changed by the user after the API_DoQuery returns a result. This may create an inconsistent state as the results of the API_DoQuery do not reflect the values of the fields that were changed. (2) Likewise, I suppose it is possible for the user to modify the field that received the parsed results of the API_DoQuery if they make their change after the button is pressed. Again when the form is saved it is possible that it may reflect inconsistent or state information.

Other than that this is easy to do with script once the details of the fields and query are supplied.
Photo of Patrick Dunn

Patrick Dunn

  • 130 Points 100 badge 2x thumb
Thanks, Dan for that thoughtful reply. The issues you cited are actually reasons why I want to approach my case this way, i.e. I want the user to be able to edit the doquery-derived values once they are populated - they are intended to be sort of a starting point.
That being said, I'm having a hard time scripting this. Do you know of any sample scripts? Thanks!
More specifically, I guess I'm after examples of how to use a DoQuery call within a formula url such that I'm using the result to populate a field within the form. The parameters of the do query will be specific enough that only one value is returned, i.e. it will not return a list of values.
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,234 Points 20k badge 2x thumb
This is very simple to do. I will put it on my todo list for the weekend as have an event to go to now.
Photo of Patrick Dunn

Patrick Dunn

  • 130 Points 100 badge 2x thumb
Thanks!!!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,234 Points 20k badge 2x thumb
Here is an demo. There are two tables Records and Ipsums. The table Ipsums contains 59 records of hilarious Lorem Ipsum themed text. For the demo you can think of these records in Ipssums as fragments of a document you may want to include into the [Document] field you are editing in the table Records. To test enter a [keyword] such as "QuickBase", "Ipsum" or a word of your choice and press the button. The [Document] field will be populated with the [Sample] field in each Ipsums record that matching your keyword.




Clone Ipsum ~ Add New Record

https://haversineconsulting.quickbase.com/db/bnsfbfx9a?a=nwr

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=663

Notes:

(1) The logic for matching Ipsums records actually are those records containing the keyword along with always including three records for "QuickBase Ipsum", "Product Manager Ipsum" and "Script Ipsum". Consider the inclusion of these three records as boilerplate.

(2) Obviously I just dreamed up this example out of thin air. Whatever your requirements are I am sure the script can be easily modified to suit your needs.
(Edited)
Photo of Carlos

Carlos

  • 654 Points 500 badge 2x thumb
Dan, For some reason I cannot get the script to run.. might there be something missing?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,234 Points 20k badge 2x thumb
My demo works. You are not providing any information to debug what you are doing wrong.
Photo of Carlos

Carlos

  • 654 Points 500 badge 2x thumb
Indeed. Here's what I have so far. I've edited it multiple times trying to get it to work, but no luck yet.
Thanks!

Button:
[iol] & "consultareserva.js" & [/iol]
&
"<a class='QBU_Button Vibrant Success' " &
"  data-rid='" & [Record ID#] & "'" &
">Search and Fill [Document]</a>"




js file:
(//https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=663
function(){
  $("img[qbu=module]").remove();
  $("a.QBU_Button").css({"margin-top": 10});
  $("a.QBU_Button").parent("td").css({"border-top": 0});


  var dbid = "ddwugpby3";
  var dbidRecords = "ddt2xatw4";
  var dbidIpsums = "ddwugpp65";
  var apptoken = "apptokengoeshere";
  $.ajaxSetup({data: {apptoken: apptoken}});
  $("a.QBU_Button").on("click", function(event) {
    var rid = this.dataset.rid;
    var keyword = _fid_34.value;
    $.get(dbidIpsums, {
      act: "API_DoQuery",
      query: `{586.CT.${keyword}`,
      clist: "586.10",
      fmt: "structured"
    }).then(function(xml) {
      $("record", xml).each(function() {
       _fid_25.value += $("f[id=586]", this).text();
      })
    });
  });
})();



Photo of Jack Fretwell

Jack Fretwell

  • 484 Points 250 badge 2x thumb
Did this ever get answered?  I'm getting some issues with this as well.  It would be helpful to have screen shots of the button properties.  I'm getting "This site can’t be reached" error for the clear.gif file.
Photo of Jack Fretwell

Jack Fretwell

  • 484 Points 250 badge 2x thumb
Even better it would be great if there was a sample application that used this that we could download and look at.