Learning to write even simple formulas can dramatically upgrade your use of Quickbase.
Quickbase formulas include powerful functions to calculate, handle conditions and adjust duration.
Here are some less obvious yet powerful ways to enhance the value from your formulas.
Concatenating with “&”
Let’s say you want to simplify a personnel report by having a Full Name field for a person as opposed to a First Name and Last Name field which are often used when storing information.
Sounds tricky, right? Well, Quickbase formulas make this challenge a cinch. Just use the “&” sign, which strings text together.
Your formula for the Full Name field will look something like this:
[First Name] & “ “ & [Last Name]
Please note that in order to get a space between the names, a piece of empty text needs to be added (as space). Text in Quickbase formulas must always be enclosed in “marks
Today () and Now ()
Need a formula to flag a Task when it’s late. Hmm, what a puzzler, how do we know in a formula if a Due Date comes before or after the current moment. Well, fortunately Quickbase calculates formulas every time a page are rendered, meaning it can evaluate a Task Due Date against the moment the data for the page got calculated.
They also provide two handy functions for use in writing formulas Today() and Now(). Both work similarly in using the value at the moment the formula calculates with Today() being used for Date comparison and Now() for Date/Time comparison.
Here’s an example for an Overdue Task Flag
if ([Due Date]<Today(),”PAST DUE”, “OK”)
Don’t forget that conditionals are always structured as if(STATEMENT,TRUE OUTPUT,FALSE OUTPUT).
We’ll challenge you to find intriguing uses for this last cool function, just as we have while building 3376 Quickbase applications.
Imagine you want to show a certain value or even control security when the person accessing a record is a certain user. The function User() in Quickbase will return the User Id of the person accessing a record or for each record in a Report. You can also use some other User related functions to get the Role, or evaluate against a User List field.
A simple example would be setting a checkbox value in a field based on whether the person accessing the Record is the Record Owner.
Note that while this is a conditional statement, because it’s on a checkbox field the formula will evaluate the statement and if it’s True will check the box, otherwise the box value will stay FALSE.
Hungry for more nuggets of Quickbase knowledge? Stay tuned for future blogs …or contacts us at MCFTech and sees how our experts can accelerate your Quickbase game.
Curious to learn more about Quickbase? Contact us now to schedule a free call