Quick Base Discussions

Expand all | Collapse all

Rich Text Formula Field for Approve / Reject button and redirection

  • 1.  Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 20:28
    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.


  • 2.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 20:45
    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))


  • 3.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 20:49
    Small edit


    $approve 
    & "&rdr=" & URLEncode($EditOtherRecord URLTWO)
    & URLEncode("&rdr=" & URLEncode($DisplaySomeRecord URLTHREE))


  • 4.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 21:01
    me again

    i meant to say

    $approve 
    & "&rdr=" & URLEncode($EditOtherRecord)
    & URLEncode("&rdr=" & URLEncode($DisplaySomeRecord))


  • 5.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 21:14
    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>





  • 6.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 21:23
    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.



  • 7.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 21:43
    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? 


  • 8.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 22:00
    So still with the concept of walking before you run, can you get a reject button working?  Then post back.


  • 9.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 22:09
    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>


  • 10.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 22:17
    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)





  • 11.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 22:22
    That is some great tutelage and knowledge, my good sir. Much obliged and appreciated


  • 12.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-20-2018 23:34
    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>", ""
    )


  • 13.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-21-2018 00:20
    Sorry but that�s a little too complicated for me to spend the time on to try to debug the syntax remotely.


  • 14.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-21-2018 00:27
    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.


  • 15.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Bronze
    Contributor
    Posted 12-21-2018 01:47
    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.


  • 16.  RE: Rich Text Formula Field for Approve / Reject button and redirection

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



  • 17.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-21-2018 03:52
    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. 




  • 18.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Bronze
    Contributor
    Posted 12-21-2018 16:58
    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?


  • 19.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-21-2018 17:14
    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!


  • 20.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Bronze
    Contributor
    Posted 12-21-2018 17:23
    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.


  • 21.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Posted 12-21-2018 18:01
    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#]));


  • 22.  RE: Rich Text Formula Field for Approve / Reject button and redirection

    Bronze
    Contributor
    Posted 12-21-2018 18:11
    So it was another errant bracket. Damn. Missed it. Good job.

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