Automate Your Database: Using QuickBase Formula URLs

You can use QuickBase Formula URL fields to automate many manual database processes. Like pressing a button that toggles an approval check box or maybe pressing a button and automatically changing a task assignment. When you use Formula URL fields they create buttons and links that can invoke QuickBase’s very powerful API or Application Programming Interface.

I am a fan of right-clicking how other people use buttons and seeing how they tick. Somewhere in my head I know I am flattering the author by copying their work.

Say for instance we have a report in a Project Management application and we wish to trigger emails to select assignees. Let’s add a formula URL field, and title it “Make Toggle”. When the viewer presses this button the Toggle field will check. When pressed again it will uncheck. This is cool because we are actually using the QuickBase API to do something that would ordinarily require multiple mouse clicks and we have reduced it to one click. Another benefit of this is that the record itself is being changed. Sure you can grid edit this report and check the boxes but if you want to trigger automatic email notifications this might be a better way to do it. Grid Edit doesn’t trigger email notifications but API calls do.

You can find out more about Formula URL buttons and cool API functions by checking out the following links and signing up for any of our 10 free weekly webinars. Many of the webinars dive into customizing and making your QuickBase applications come alive.

Configure QuickBase URL Fields
Using Formulas
List of Formula Functions
Sign up for a Free Webinar

Kirk Trachy

QuickBase evangelist, sales engineer and lover of all things possible with QuickBase. Check out Kirk live at one of his daily webinars.

More Posts - Website

  • http://quickbase.intuit.com/ Kirk Trachy

    If anyone wanted that code, here it is:
    =============================
    PRESS A BUTTON AND TOGGLE A CHECK BOX ON AND OFF IN A LIST ALL REPORT

    If([Toggle]=false,
    URLRoot() & “db/” & Dbid() & “?act=API_EditRecord&apptoken;=b79yg53dmviaxucy3vzvf4actmt&rid;=”&[Task ID]&”&_fid_126=”&1& “&rdr;=” & URLEncode(URLRoot() & “db/” & Dbid() &”?a=q&qid;=1″),
    URLRoot() & “db/” & Dbid() & “?act=API_EditRecord&apptoken;=b79yg53dmviaxucy3vzvf4actmt&rid;=”&[Task ID]&”&_fid_126=”&0& “&rdr;=” & URLEncode(URLRoot() & “db/” & Dbid() &”?a=q&qid;=1″))

    Description:
    If the field Toggle is unchecked then use the API_EditRecord and use the apptoken, b79yg53dmviaxucy3vzvf4actmt (use your own) and edit the rid which is the key field for this table called [Task ID] and change fid_126 to a 1 (which is the checked state) and then rdr (redirect) to displaying a report (in this situation, URLEncode(URLRoot() & “db/” & Dbid() &”?a=q&qid;=1 is a “List All” tasks report). The remainder is the “Else” portion of the If statement. Else do the opposite which is to uncheck that same fid_126 field.

    =============================

    [Reply]

  • http://quickbase.intuit.com Kirk Trachy

    If anyone wanted that code, here it is:
    =============================
    PRESS A BUTTON AND TOGGLE A CHECK BOX ON AND OFF IN A LIST ALL REPORT

    If([Toggle]=false,
    URLRoot() & “db/” & Dbid() & “?act=API_EditRecord&apptoken=b79yg53dmviaxucy3vzvf4actmt&rid=”&[Task ID]&”&_fid_126=”&1& “&rdr=” & URLEncode(URLRoot() & “db/” & Dbid() &”?a=q&qid=1″),
    URLRoot() & “db/” & Dbid() & “?act=API_EditRecord&apptoken=b79yg53dmviaxucy3vzvf4actmt&rid=”&[Task ID]&”&_fid_126=”&0& “&rdr=” & URLEncode(URLRoot() & “db/” & Dbid() &”?a=q&qid=1″))

    Description:
    If the field Toggle is unchecked then use the API_EditRecord and use the apptoken, b79yg53dmviaxucy3vzvf4actmt (use your own) and edit the rid which is the key field for this table called [Task ID] and change fid_126 to a 1 (which is the checked state) and then rdr (redirect) to displaying a report (in this situation, URLEncode(URLRoot() & “db/” & Dbid() &”?a=q&qid=1 is a “List All” tasks report). The remainder is the “Else” portion of the If statement. Else do the opposite which is to uncheck that same fid_126 field.

    =============================

    [Reply]

  • Jared

    Kirk,

    GREAT post! Keep ‘em coming…I always learn so much when you do these.

    [Reply]

  • Jared

    Kirk,

    GREAT post! Keep ‘em coming…I always learn so much when you do these.

    [Reply]

  • Bill

    I tried this code, however when I click the button, it leaves the page and brings me to the “All Tasks” report.
    How do you make it stay on the page……I followed the code in your example in the video.

    [Reply]

  • Bill

    I tried this code, however when I click the button, it leaves the page and brings me to the “All Tasks” report.
    How do you make it stay on the page……I followed the code in your example in the video.

    [Reply]

  • http://www.heal10.com/ Igor Petrushenko

    Hi,

    Great post and tool! Thank you very much,

    between any comparision with MyTaskHelper.com?
    Just created and integrated into my site, looks not bad.

    Can I use both products together?

    Any help will be much appreciated.

    Sincerely,
    Igor

    [Reply]

    Kirk Trachy Reply:

    @Igor Petrushenko,
    Yes you can use this with QuickBase. QuickBase is an online web database with interactive forms, reports, dashboards, automation and role security. http://tinyurl.com/ygmtusq Kirk

    [Reply]

  • http://www.heal10.com Igor Petrushenko

    Hi,

    Great post and tool! Thank you very much,

    between any comparision with MyTaskHelper.com?
    Just created and integrated into my site, looks not bad.

    Can I use both products together?

    Any help will be much appreciated.

    Sincerely,
    Igor

    [Reply]

    Kirk Trachy Reply:

    @Igor Petrushenko,
    Yes you can use this with QuickBase. QuickBase is an online web database with interactive forms, reports, dashboards, automation and role security. http://tinyurl.com/ygmtusq Kirk

    [Reply]

  • http://www.mcftech.com/ Govind Davis, MCF Tech

    The issue with using the QuickBase API in a formula field is that as far as I have been able to determine it can only target a specific redirect. This means you can only end on a specific report or record which does limit the range of use of the button.

    It seems like the Rurl() should help with this but I have yet to accomplish it.

    We have a Javascript model for this that targets the source page where the script is launched and refreshes it after the filed change. This way you can use the button anywhere. If there is interest in this script please email at gdavis@mcftech.com.

    [Reply]

  • http://www.mcftech.com Govind Davis, MCF Tech

    The issue with using the QuickBase API in a formula field is that as far as I have been able to determine it can only target a specific redirect. This means you can only end on a specific report or record which does limit the range of use of the button.

    It seems like the Rurl() should help with this but I have yet to accomplish it.

    We have a Javascript model for this that targets the source page where the script is launched and refreshes it after the filed change. This way you can use the button anywhere. If there is interest in this script please email at gdavis@mcftech.com.

    [Reply]

  • (A Different) Jared

    If you copy and paste the above code, be sure to replace all the quotation marks. As they are, QB reads them as invalid characters.

    (There’s five minutes of my life I want back!)

    Thanks for the tip, Kirk! I didn’t even know I needed it.

    [Reply]

    Kirk Trachy Reply:

    Jared: You are so right. Many times when you cut and past code it changes the quotation marks from straight up and down to angled ones. It is very subtle and difficult to pick out. You definitely want the straight up and down ones. Sometimes I have to open note pad or some basic text editor, paste them there and then copy them just so formatting is stripped. Thanks!

    [Reply]

  • (A Different) Jared

    If you copy and paste the above code, be sure to replace all the quotation marks. As they are, QB reads them as invalid characters.

    (There’s five minutes of my life I want back!)

    Thanks for the tip, Kirk! I didn’t even know I needed it.

    [Reply]

    Kirk Trachy Reply:

    Jared: You are so right. Many times when you cut and past code it changes the quotation marks from straight up and down to angled ones. It is very subtle and difficult to pick out. You definitely want the straight up and down ones. Sometimes I have to open note pad or some basic text editor, paste them there and then copy them just so formatting is stripped. Thanks!

    [Reply]

  • http://www.tiffanyforsale.com tiffany sale

    In other words, if the [OpportunityID] is “57″ and the [Account - Company Name] field is the company who’s name is “5M” the formula text field puts them together so they look something like: “575M”.

    [Reply]

  • Phil

    How can I create a formula-URL field to open a specific report? I don’t want users to be taken to the default fields report, but instead a custom report. I can’t seem to find the simple answer anywhere…

    [Reply]

    Phil Reply:

    Here is the broken URL code that I’ve tried to create:

    URLRoot() & “db/” & [_DBID_ASSIGNMENTS] & “?a=API_DoQuery&apptoken;=b6z9cn6d99nre4dhyhjhndtrsuia;=”&”_qid_22=” & URLEncode ([Project ID#])& “&z=” & Rurl()

    [Reply]

    Phil Reply:

    Figured it out… URLRoot() & “db/” & [_DBID_ASSIGNMENTS]&
    “?a=API_GenResultsTable&apptoken=b6z9cn6d99nre4dhyhjhndtrsuia&qid=25&nv=1&v0=”& URLEncode ([Project ID#]) & “&z=” & Rurl()

    However, is there a way to open the report with the standard QuickBase frames? Currently the link opens in a separate, unformatted HTML page.

    [Reply]

    Phil Reply:

    Figured it out… much easier than I thought it would be…

    “https://www.quickbase.com/db/bgvhez58c?a=q&qid=21&nv=1&v0=”& URLEncode ([Project ID#])

  • Kate

    I’ve managed to get this working in my database but what if I want to use the API_SetDBVar to set a variable? Is this a possibility?

    [Reply]

    LizaLou Reply:

    Hey Kate-The call API_SetDBVar is a call that is pretty specific and would be used for one-time acts. I am not sure how having a button would be useful in for this call. Can you tell me more about what you are looking to do?

    [Reply]

    Kate Reply:

    I wish to use the variable created by the call API_SetDBVAR in another button which uses the “Copy Master and Detail Record” function.

    The Master record changes every day in my database, thus I want to be able to change the identifying record in the Copy master and Detail Record formula from a record number to the variable.

    “javascript:void(copyMasterDetailButtonHandler(‘&relfids=46&sourceRID=[variable a]&”&destrid=” & [Record ID#] & “‘, ‘bhq46s43f’))”

    [Reply]

    LizaLou Reply:

    I still do not think this is the command you would want to use in QuickBase however you could use it to set the variable in the application but you would have to have admin rights for this call to be successful. With that said it’s just as easy to go to the application settings and create the variable there. Once it exists through either method, you can use it with the [variable a] portion of your formula.

    Kate Reply:

    Hello, I have found another way to manage this step by using two seperate buttons:

    Create a new record:
    URLRoot()&
    “db/” & [_DBID_PGPPP_PUBLIC_SURVEY]
    &”?a=API_addRecord&apptoken=brkez3cd222keeehxgt94wdeyc&_fid_15=”&
    URLEncode ([Record ID#])& “&z=” & Rurl()

    Update record:
    URLRoot()& “db/” & Dbid() &”?act=API_EditRecord&apptoken=brkez3cd222keeehxgt94wdeyc&rid=”&[Record
    ID#]&”&_fid_100=”&(Today())
    & URLRoot()& “db/” & [_DBID_PGPPP_PUBLIC_SURVEY]
    &”?a=API_addRecord&apptoken=brkez3cd222keeehxgt94wdeyc&_fid_15=”
    & URLEncode ([Record ID#])”&rdr=”& URLEncode(URLRoot() & “db/” & Dbid()&”?a=q&qid=29″)

    However
    I would like these steps done by one button. When I attempt to ‘merge’
    the formula it will not work. Can you please advise what I’m doing wrong
    (example below)

    URLRoot()& “db/” & Dbid() &”?act=API_EditRecord&apptoken=brkez3cd222keeehxgt94wdeyc&rid=”&[Record
    ID#]&”&_fid_100=”&(Today())
    & “&rdr=”& URLEncode(URLRoot() & “db/” &
    Dbid()&”?a=q&qid=29″) &URLRoot()& “db/” &
    [_DBID_PGPPP_PUBLIC_SURVEY]
    &”?a=API_addRecord&apptoken=brkez3cd222keeehxgt94wdeyc&_fid_15=”&
    URLEncode ([Record ID#])&”&rdr=” & URLEncode(URLRoot()
    & “db/” &Dbid() &”?a=q&qid=29″)