Rich Text Formula Field for Approve / Reject button and redirection

  • 1
  • 1
  • Question
  • Updated 3 months ago
  • Answered
I'm trying to setup a rich text formula field that will allow the user to "approve" or "reject" a case. When the user chooses "approve", I'd like the button to add values to three fields on the current record, do a redirect to another record on a different table and update a value of a field on that table with a value from the original record, and then do another redirect back to the original record which has been updated with the initial edits. 
For "reject", only the values are added to three fields, and no redirect is needed.

Here is what I have so far:

var text approve =  URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=XXXXXX&rid=" & URLEncode ([Record ID#])

& "&_fid_xx=" & URLEncode("Approved")

& "&_fid_xx=" & URLEncode(Now())

& "&_fid_xx=" & URLEncode(User())

& "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a= API_EditRecord&apptoken=xxxxxxrid=" & URLEncode ([Related Store Item])

& "&_fid_xx=" & URLEncode([# of Features Requested])

& "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & [Record ID#]);

var text reject =   URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=XXXXXX&rid=" & URLEncode ([Record ID#])

& "&_fid_xx=" & URLEncode("Rejected")

& "&_fid_xx=" & URLEncode(Now())

& "&_fid_xx=" & URLEncode(User())

& "&z=" & Rurl();

Not sure where this is going wrong with the formatting or syntax errors.
Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb

Posted 3 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
I suggest that you walk before you try to run.

Make a URL formula field called Approve, not a rich test formula field.


var text approve =  URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=XXXXXX&rid=" & URLEncode ([Record ID#])

& "&_fid_xx=" & URLEncode("Approved")

& "&_fid_xx=" & URLEncode(Now())

& "&_fid_xx=" & URLEncode(User())


var text EditOtherRecord = 

URLRoot() & "db/xxxxx?a= API_EditRecord&apptoken=xxxxxxrid=" & URLEncode ([Related Store Item])

& "&_fid_xx=" & URLEncode([# of Features Requested]);


var text DisplaySomeRecord = 

URLRoot() & "db/xxxxx?a=dr&rid=" & [Record ID#]);

$approve URLONE 
& "&rdr=" & URLEncode($EditOtherRecord URLTWO)
& URLEncode("&rdr=" & URLEncode($DisplaySomeRecord URLTHREE))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
Small edit


$approve 
& "&rdr=" & URLEncode($EditOtherRecord URLTWO)
& URLEncode("&rdr=" & URLEncode($DisplaySomeRecord URLTHREE))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
me again

i meant to say

$approve 
& "&rdr=" & URLEncode($EditOtherRecord)
& URLEncode("&rdr=" & URLEncode($DisplaySomeRecord))
Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
Thanks for the info. The formula is accepting, however, it's appearing as the entire link on the form versus the button(s). 
Also, when I click the link I get taken to an error:

This XML file does not appear to have any style information associated with it. The document tree is shown below.

<qdbapi>
<action>API_EditRecord</action>
<errcode>2</errcode>
<errtext>Invalid input</errtext>
<errdetail>
You must provide a valid choice for the field named "Decision" with field id 12.
</errdetail>


<rid>1</rid>
<num_fields_changed>0</num_fields_changed>
<update_id/>

</qdbapi>



Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
Correction -  My mistake, the value for field id 12 was incorrect. I corrected it, the link works to update the three fields now. However, the value in the "other record" is not populating based on the value in the original.

This part:

var text EditOtherRecord = 

URLRoot() & "db/xxxxx?a= API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Related Store Item])

& "&_fid_xx=" & URLEncode([# of Features Requested]);

doesn't seem to be functioning. I've confirmed that there is a value in the [# of Features Requested] field.

(Edited)
Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
Really making progress here - much appreciated!

I've got all components functioning now for the approval. Is there a way to also include the rejection within the same url formula field? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
So still with the concept of walking before you run, can you get a reject button working?  Then post back.
Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
Agreed, thanks for the assistance.

Here is what I have for the reject button:

var text reject =  URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxx&rid=" & URLEncode ([Record ID#])

& "&_fid_xx=" & URLEncode("Reject")

& "&_fid_xx=" & URLEncode(Now())

& "&_fid_xx=" & URLEncode(User());

var text DisplaySomeRecord = 

URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#]);

$reject 
& URLEncode("&rdr=" & URLEncode($DisplaySomeRecord))

This works, but redirects to an xml page:

This XML file does not appear to have any style information associated with it. The document tree is shown below.

<qdbapi>
<action>API_EditRecord</action>
<errcode>0</errcode>
<errtext>No error</errtext>
<rid>1</rid>
<num_fields_changed>1</num_fields_changed>
<update_id>1545343689363</update_id>


</qdbapi>
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
The generic format of the nesting is 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)))))


and you slice as much off that salami as you like

So in your case it would be

$reject 
& "&rdr=" & URLEncode($DisplaySomeRecord)



Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
That is some great tutelage and knowledge, my good sir. Much obliged and appreciated
Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
Thanks to your help, I'm super close to where I want to be! Here is my code for the rich text field that I created, all is well, except for the last redirect for the approved button. I highlighted in bold, QB is throwing an "expected text" syntax error.

I tried to follow your convention above, but have been scratching my head over it for an hour now..



var text approve =  URLRoot() & "db/" &  [_DBID_] & "?a=API_EditRecord&apptoken=xxxxx&rid=" & URLEncode ([Record ID#])
& "&_fid_12=" & URLEncode("Approved")
& "&_fid_13=" & URLEncode(Now())
& "&_fid_14=" & URLEncode(User())
& "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=API_EditRecord&apptoken=xxxxxxx&rid=" & URLEncode ([Related Store Item])
& "&_fid_14=" & URLEncode([# of Features Requested]));
& URLEncode("&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#]));

var text reject =   URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxxx&rid=" & URLEncode ([Record ID#])
& "&_fid_12=" & URLEncode("Rejected")
& "&_fid_13=" & URLEncode(Now())
& "&_fid_14=" & URLEncode(User())
& "&rdr=" & URLEncode(URLRoot() & "db/xxxxxx?a=dr&rid=" & ([Record ID#]));

var text cancel =    URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxxx&rid=" & URLEncode ([Record ID#])
& "&_fid_12=" & URLEncode("Cancelled")
& "&_fid_13=" & URLEncode(Now())
& "&_fid_14=" & URLEncode(User())
& "&z=" & Rurl();


//Colors of the buttons. The second color is the mouseover color.
var text buttoncolor= "@#76c043@#367c2b@";
var text buttoncolortwo= "@#c04e43@#89362e@";


var text buttoncolorthree= "@#efab49 @#a96a0f@";

var text buttonone= $approve & $buttoncolor & "Approve";
var text buttontwo= $reject & $buttoncolortwo & "Reject";
var text buttonthree= $cancel & $buttoncolorthree & "Cancel";

var bool ShowButtonOne = If(Now()>[Date Created], true, false);

// ========================Begin Pattern========================

var text onclickone =  "\" onclick=\"LinkAway('";
var text onclicktwo = "', false); return false;\"";

//Button bar
If($ShowButtonOne=true,
"<div style=\"position:relative; width: 330px; height: 25px; \">" & 
If($buttonone<>"", "<a href=\""  & Left($buttonone, "@") & $onclickone & Left($buttonone, "@") & $onclicktwo & "\" style=\"color:white;text-decoration: none; \"> <span style=\"position: absolute; display: table-cell; text-align:center; width:160px; height:25px; border-radius: 5px; background-color:" & Part($buttonone,2, "@") & ";  font-weight: bold; \" onMouseOver=\"this.style.backgroundColor='" & Part($buttonone,3, "@") & "'\" onMouseOut=\"this.style.backgroundColor='" & Part($buttonone,2, "@") & "'\"><span style=\"position:relative; top:25%;\">" & Right($buttonone, "@") & "</span></span></a>","") & 
If($buttontwo<>"","<a href=\""  & Left($buttontwo, "@") & $onclickone & Left($buttontwo, "@") & $onclicktwo & "\" style=\"color:white;text-decoration: none;\"> <span style=\"position: absolute; display: table-cell; text-align:center; width:160px; height:25px; border-radius: 5px; background-color:" & Part($buttontwo,2, "@") & ";  left: 170px; font-weight: bold; \" onMouseOver=\"this.style.backgroundColor='" & Part($buttontwo,3, "@") & "'\" onMouseOut=\"this.style.backgroundColor='" & Part($buttontwo,2, "@") & "'\"><span style=\"position:relative; top:25%;\">" & Right($buttontwo, "@") & "</span></span></a>","") & 
//If($buttonthree<>"" and [Status]<>"On Hold","<a href=\""  & Left($buttonthree, "@") & $onclickone & Left($buttonthree, "@") & $onclicktwo & "\" style=\"color:white;text-decoration: none;\"> <span style=\"position: absolute; display: table-cell; text-align:center; width:130px; height:25px; border-radius: 5px; background-color:" & Part($buttonthree,2, "@") & ";left: 280px; font-weight: bold; \" onMouseOver=\"this.style.backgroundColor='" & Part($buttonthree,3, "@") & "'\" onMouseOut=\"this.style.backgroundColor='" & Part($buttonthree,2, "@") & "'\"><span style=\"position:relative; top:25%;\">" & Right($buttonthree, "@") & "</span></span></a>","") & 
"</div>", ""
)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
Sorry but that’s a little too complicated for me to spend the time on to try to debug the syntax remotely.
Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
No problem, I appreciate all the help. 

I'm having a hit or miss session figuring it out. It's the last redirect for the approval which is causing issues.
Looks like bracketology to me, Niraj. Without diving too deep into your code, it appears that you've nested some URLEncodes inside each other and they're not all closing where you probably want them to.

& "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=API_EditRecord&apptoken=xxxxxxx&rid=" & URLEncode ([Related Store Item])
& "&_fid_14=" & URLEncode([# of Features Requested]));
& URLEncode("&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#]));

The first URLEncode in that portion isn't closed until the end of the following line (could be intentional). The final line is short a close paren. You also have what looks like a stray ; at the line before the one you said is throwing the error.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
FWIW,  I do not believe i have ever been successful building href type links which contain nested URL with redirects. You may need to settle for a formula URL field called Toggle Approval, or else two separate buttons, one of which would calculate to null at any time, and hence be blank.  ie if you have an IF that calculates to null if the record is already approved, the  it would be blank and the other button would display.

Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
Thanks for all the feedback, I think I'm close to figuring this one out. Here's where I am now; 

Here is the meat of my formula that I'd love feedback on. The version below was accepted by QB but for some reason fid 14 did not populate when the approve option was selected but did when reject was selected. As I commented below the redirect I have in the approve is not populating the target table and goes to an xml page (see below):

This XML file does not appear to have any style information associated with it. The document tree is shown below.

<qdbapi>
<action>API_EditRecord</action>
<errcode>0</errcode>
<errtext>No error</errtext>
<rid>1</rid>
<num_fields_changed>0</num_fields_changed>
<update_id>1545345688859</update_id>

</qdbapi>

var text approve =  URLRoot() & "db/" &  [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxxxx&rid=" & URLEncode ([Record ID#])
& "&_fid_12=" & URLEncode("Approved")
& "&_fid_13=" & URLEncode(Now())
& "&_fid_14=" & URLEncode(User())
& "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Related Store Item]))
& "&_fid_14=" & URLEncode([# of Features Requested]);
//the redirect is not populating the fields and when i add another redirect it comes back with a syntax error

var text reject =   URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Record ID#])
& "&_fid_12=" & URLEncode("Rejected")
& "&_fid_13=" & URLEncode(Now())
& "&_fid_14=" & URLEncode(User())
& "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#]));


Funny thing is, when I remove the redirect from the approval, fid 14 is populating fine. 


I'm past the point of following exactly what you're trying to do here, but I notice that your approve code is defining fid_14 twice, is that intentional? You're trying to define 14 as the user field and then also do a redirect that subsequently defines it as something completely different?
Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
Good point - and this may be where I am messing up.

The first id 14 is being set as a user field in the current table. Then I am (attempting) to do a redirect to another table and record to define that tables fid 14 as a value from the first table.

I hope that makes sense in all my madness!
Oh, that makes sense. I see it now, it seems syntactically sound! But I have never tried to do anything like that. Certainly your testing seems to indicate that that's the point at which the process breaks down. At some point you may have to take a pause on this approach and try one of Mark's alternate suggestions using methods other than one super-long url string.
Photo of Niraj Shah

Niraj Shah

  • 240 Points 100 badge 2x thumb
Got it working, finally! Thanks for all the help!

 Here is the final code below. The first button edits the current record, redirects to another record in another table and edits that record, and then brings the user back to the current record they were on. 

How can I mark this as solved or answered?

var text approve =  URLRoot() & "db/" &  [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Record ID#])
& "&_fid_12=" & URLEncode("Approved")
& "&_fid_13=" & URLEncode(Now())
& "&_fid_14=" & URLEncode(User())
& "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Related Store Item])
& "&_fid_14=" & URLEncode([# of Features Requested]))
& URLEncode("&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#])));

var text reject =   URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxx&rid=" & URLEncode ([Record ID#])
& "&_fid_12=" & URLEncode("Rejected")
& "&_fid_13=" & URLEncode(Now())
& "&_fid_14=" & URLEncode(User())
& "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#]));
(Edited)
So it was another errant bracket. Damn. Missed it. Good job.

As far as marking answered, the community managers take care of that periodically.