- Working Extended Hours? You're Not Alone: http://t.co/Fl1OFWJq #remoteworkers #workingfromhome #worklifebalance

Automate Your Database: Using QuickBase Formula URLs

By Kirk Trachy | Posted on July 9th, 2009

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

About Kirk Trachy

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

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:
October 21st, 2009 at 8:01 pm
@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:
October 21st, 2009 at 3:01 pm
@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:
July 31st, 2009 at 11:42 pm
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:
July 31st, 2009 at 6:42 pm
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]

blog comments powered by Disqus

Meet Our Writers

Alexandra Levit

Alexandra Levit’s goal is to help people find meaningful jobs - quickly and simply - and to succeed beyond measure once they get there. Follow her @alevit.

Alison Green

Alison Green writes the popular Ask a Manager blog where she dispenses advice on career, job search, and management issues. She's also the co-author of Managing to Change the World: The Nonprofit Leader's Guide to Getting Results and former chief of staff of a successful nonprofit organization, where she oversaw day-to-day staff management, hiring, firing, and employee development.

Eva Rykr

Eva Rykr is an organizational psychology practitioner. Her passion lies in bringing a psychology perspective to the business world, with the mission of creating a high-performance environment. Follow her @EvaRykr.

Anita Bruzzese

Anita Bruzzese is a syndicated columnist for Gannett/USA Today on workplace issues and the author of “45 Things You Do That Drive Your Boss Crazy.” She has been on the Today show, and quoted in publications such as O, The Oprah Magazine, Glamour, Self.com and BusinessWeek.com. Her website, 45things.com, is listed on the Forbes top 100 websites for women.