Trying to use one formula URL button to do two things.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I'm building a formula URL button which will do things: modify the current record and add a new record (to a separate table which is a child of the same parent). Here's a visual:

Services (parent)
Appointments (one child)         Notes (another child)
Specifically, on an Appointment record, there will be a CANCEL APPT button which will modify a couple of fields on the Appointment record and create a new Note record, which will be related to the same parent Service. I can get either API call to work separately, but not together. Current code looks like:

var text EditAppt = URLRoot() & "db/" & Dbid()
& "?act=API_EditRecord&rid="
& ToText([Record ID#])
& "&_fid_139=1"
& "&_fid_138=" & URLEncode("Weather")
& "&apptoken=xxxx";

var text AddNote = URLRoot() & "db/bjfnhvpqz"
& "?a=API_AddRecord"
& "&_fid_7=" & ToText([Related Service])
& "&_fid_16="  & URLEncode("Status Note for Client")
& "&_fid_6="  & URLEncode("Due to inclement weather, we will not be able to perform this scheduled service. We will reschedule this based on weather, and will let you know the updated time.")
& "&apptoken=xxxx";

"javascript:" &
"$.get('" &
$EditAppt &
$AddNote &
"',function(){" &
"location.reload(true);" &
"});"
& "void(0);"
Photo of Jonathan Heuer

Jonathan Heuer

  • 400 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
I also like to use that Javascript refresh code when doing a single API call as it so flexible in that it will refresh a record or any report.  But I don't believe that you can string two APIs together.  So you need to use the nested redirect method.

Note that your last API needs to either display a record or a report in order to suppress the API success message.  So in your case you will need a third formula variable to finish off with the redisplay the Appointment



var text DisplayAppt = URLRoot() & "db/" & Dbid()
& "?a=dr&rid="
& ToText([Record ID#]);



Once you have defined the formula variables you nest them like this.

$URLONE

& "&rdr=" & URLEncode($URLTWO)

& URLEncode("&rdr=" & URLEncode($URLTHREE))

& URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))

& URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFIVE))))

& URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSIX)))))

& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSEVEN))))))

 

Here is an example

var text AddRecord =

URLRoot() & "db/" & [_DBID_PO_DETAIL_LINES]

& "?act=API_AddRecord&_fid_6=" & URLEncode ([Purchase Order])

& "&_fid_17=" & [Qty]

& "&_fid_10=" & [Related Priced Material];



var text DeleteRecord =

URLRoot() & "db/" & [_DBID_PO_DETAIL_LINES]

& "?act=API_DeleteRecord&rid=" & [Record ID#];

 

var text DisplayRecord=

URLRoot() & "db/" & [_DBID_PURCHASE_ORDERS]

& "?a=dr&rid=" & [Purchase Order];

 

$AddRecord & "&rdr=" & URLEncode($DeleteRecord)

& URLEncode("&rdr=" & URLEncode($DisplayRecord))
Photo of Jonathan Heuer

Jonathan Heuer

  • 400 Points 250 badge 2x thumb
Dan Diebolt also supplied an answer which combined a user-defined page and the Diebolt Image Onload Technique. Here's a the code on the former, which is called via the button; I've x'ed out all the dbid's and the apptoken. Thanks Dan!!!


(function(){

  var dbid = "xxx";
  var dbidAppointments = "xxx";
  var dbidServices = "xxx";
  var dbidNotes = "xxx";
  var apptoken = "xxx";
  $.ajaxSetup({data: {apptoken: apptoken}});

  var querystring=document.location.search;

  if(/a=dr/i.test(querystring) || /a=q/i.test(querystring)) {

    $("img[qbu=module]").closest("td").css("background-color","#FFFFFF");
   
    $(".QBU_MyClass").on("click", function(event) {
      var rid = $(event.target)[0].dataset.rid;
     
      var promise1 = $.get(dbidAppointments, {
        act: "API_EditRecord",
        rid: rid,
        _fid_139: "1",
        _fid_138: "Weather"
      });

      var promise2 = $.get(dbidAppointments, {
        act: "API_DoQuery",
        query: "{3.EX." + rid + "}",
        clist: "10"
      });
      promise2.then(function(xml) {
        var relatedService = $("related_service", xml).text();

        var promise3 = $.get(dbidNotes, {
          act: "API_AddRecord",
          _fid_7: relatedService,
          _fid_16: "Status Note for Client",
          _fid_6: "Due to inclement weather, we will not be able to perform this scheduled service. We will reschedule this based on weather, and will let you know the updated time."
        });
        promise3.then(function(xml) {
          document.location.reload(true);
        });
      });
    });
  }

})();
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
JH> Thanks Dan!!!
Thanks you! I wasn't going to post the code because I thought it had some confidential info but I see you sanitized it. There is one missing piece of info and that is the formula for the button. But first here is a online version of the table relationship using a very neat tool called Nomnoml which can be used to generate UML diagrams and more specifrically simple ER diagrams just by typeing in some text. It is very extremely easy to use once you play with it and explore the various examples, options clickables (too bad it does not support a crow's foot):

Your Three Tables
http://goo.gl/npkucR

Nomnoml - Online UML Editor
http://www.nomnoml.com/

And here is my normal pastie for this question with the formula for the button:

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