Once a user has mastered the simple task of creating basic fields in QuickBase, they will need to learn how to use formula fields. QuickBase formula fields are created in the same way any field is created in QuickBase.
Formulas can be as simple as
Often when this QuickBase formula is suggested, ‘Why?’ is the common response. Imagine the confusion and sometimes distrust a client would have if a business were to contact a client using their name incorrectly. But this is a common issue for companies when collecting contact information. For example the name Xiu Wang can confuse some users as to which is the last name and which is the first. Another issue frequently arising is when multiple users enter data, inconsistencies can occur with capitalisation for example St John vs St john.
To avoid inconsistencies and provide end users with an easy method to identify First and Last names, the Upper or Lower formula function is used. An example of the formula where using a field titled Last Name: Upper([Last Name]). This will result in any values entered into the last name field being converted to uppercase in the QuickBase formula field.
Prior to the introduction of the Euro Date option in QuickBase, countries outside of America who utilised QuickBase, used the Left/Right function to convert the American Date Format in their forms to a more friendly display option, an example of this is Year([YourDate]) & “-” & Right(“0” & Month([YourDate]), 2) & “-” & Right(“0” & Day([YourDate]), 2).
Now that intuit has modified QuickBase to allow the end user to choose how a date display, a more common use for the Left Function is to convert contact details to Initials. For example using a field titled First Name, the user can extract the first initial with the formula Left([First Name],1).
Currently no Proper formula function is available in QuickBase, unlike in the Microsoft software suite. Proper will allow the user to convert text to a title case such as ann-marie to Ann-Marie. This function has been suggested to intuit in the Feedback Forum for future development.
The Today function will return the current date, the value in this field will update automatically to the current date, thus it is useful if the user wishes to calculate the difference between the current day and another date field. For example Today()-[Due Date] will provide the user with the number of days the between the two.
IsNull is a function mostly used in conjunction with other QuickBase formula functions, and is used to identify fields which have no value entered or undefined. It’s attribute must be a CheckBox as it returns a true or false value. For example if the value in the field Start Date IsNull([Start Date]) is null, QuickBase will return a value of TRUE. This QuickBase formula is mostly used in conjunction with the IF formula function which is the next function example.
IF Functions can be very intimidating initially, but by starting with very basic formulas a user can quickly master the function and build their knowledge to create what are sometimes very complex IF formulas. The basics of an IF formula are that it looks at a particular value or field, if it equals a particular value to return a result of X, otherwise to return a value of Y. For example If([Date Due]<Today(),”Overdue”,”Not Overdue”). This formula states that if the due date is after today, then the field is equal to Overdue, otherwise return the value of Not Over Due.
Users are then able to build on this using other functions such as the IsNull function, for example what if the Due Date
In the example provided where the QuickBase formula is to calculate where an item is overdue, the previous function IsNull could be also used as another reference in the formula
Created a Basic QuickBase Formula? So What Now?
Like anything, practicing is always the best way to improve skills. Practicing formulas frequently in QuickBase will not only improve your skill level in the function, but also provide the confidence to explore larger and more complex QuickBase formulas to reduce the amount of data entry and increase data quality.