Auto populate a field in quickbase by querying a table

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
I am very new to QB and helping out my team to modify an existing QB application. One of the requirement is to auto-populate the contract amendment field on contract table so that it displays the latest amendment number(fetch the last amendment number from the contract table for the current contract id and add 1 to it).For example if the current contract number is Contract#1 and the last contract amendment number is 7 then 8 should be auto-populated when I select the above contract number while creating a new record in the contract table. Is there a way to query the tables in QB? Please let me know how this can be done.
Photo of Siddharth Asapu

Siddharth Asapu

  • 80 Points 75 badge 2x thumb

Posted 4 months ago

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

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 Points 20k badge 2x thumb
Use the IOL technique with code similar to this:

var dbid = "your app dbid here";
var dbidTable = "your table dbid here";
var apptoken = "your apptoken here";
$.ajaxSetup({data: {apptoken: apptoken}});
$.get(dbidTable, {
  act: "API_DoQuery",
  clist: "6",
  slist: "6",
  options: "num-1.sortorder-D"
}).then(function(xml) {
  var nextContractId = parseInt($("contract_id", xml).text(), 10) + 1;
  $("#_fid_6").val(nextContractId);
});

Photo of Siddharth Asapu

Siddharth Asapu

  • 80 Points 75 badge 2x thumb

Hi Dan, Thanks for  your response. However I am very new to QB and do not know how to implement IOL technique. I it possible to create a button which uses  Api_Doquery to find the last sequence number and populate a field on the screen with that number?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
The code I provided does precisely what you are asking for. To use it you should become familiar with the IOL Technique discusses here:

I am sure I have done something wrong
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294

How Do I Setup the Image Onload Technique (IOL)? <= BEST RESOURCE
https://community.quickbase.com/quickbase/topics/how-do-i-setup-the-image-onload-technique-iol
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Siddha Ruth

I did d not see your post until now.

T native way to do this is actually quite simple.

Relationship between contracts and amendments you make a summary field of the maximum of the amendment number.

Then you look that up down to the child record.

Can you create a snapshot field. A snapshot feel takes the value of the look up at the moment it is created and Fries is it like a snapshot photograph. To set up a snapshot field he would set up a simple numeric field but at the very bottom of the field properties you tell it to be a snapshot field

So then you just have a formula that the amendment number for that record is the snapshot field plus one.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Here is some help text on setting up a snapshot field.

https://help.quickbase.com/user-assis...