We are updating our Terms of Service.
See our blog post highlighting the changes.
By continuing to use the Quick Base Service, the new Terms of Service will become effective on the renewal date for each account that you manage.
If you do not accept the changes, please notify us thirty days in advance of your renewal date.

Quick Base will send a report of the events currently displayed on your calendar.

Take the e-mail address baker@example.com and convert it to the user value connected with that e-mail account (you can use a user name instead of an e-mail address). If the value in the Salesperson field is that user, then display the word Western, otherwise, display the word Eastern.

Tip: Form rules can also automatically populate fields based on other values.

Want to set this up for multiple salespeople and territories? Use the Case() function instead. Read how in the next section.

Text

Color rows based on who a user is

You want to color code rows based on the user in the Assigned To field.

Take the e-mail address baker@example.com and convert it to the user value connected with that e-mail account (you can use a user name instead of an e-mail address). If the value in the Assigned To field is that user, then color the row pink, otherwise, don't color it.

-Don't forget to enclose the e-mail address or user name in quotation marks.

none - view builder formula

I need a formula that returns the reporting period month. Any date after the 18th of the month will return a reporting period of the following month

If the day of the month entered into the [Date] Field is less than or equal to 18, then subtract one from the month of the date field and based on the month number return the matching name of month.

Else if the day of the month entered into the [date] field is greater than 18 then return the month of the [date field], and based on the month number return the matching month name.

Date

Calculate the number of weeks into the year a date is

In Excel I can use the WeekNum function to calculate how many weeks into the year a particular date is. How do I do this in QuickBase?

Dates

Int(DayofYear([datefield])/7+1)

Find the day of the year that the date field is (in other words how many days into the year the date is). Then divide by seven to get the number of weeks into the year. Add one. Then show the integer only (in other words, leave off any fractional values that follow the decimal point).

There is no week 0, so the first week is 1. That's why you add 1.

This formulas returns the "Absolute Week Number" which counts weeks starting from January 1st, no matter what day of the week it is. In other words, Jan 1st through 7th is always week one. If Jan 1st is a Tuesday, than each week of the year will be Tuesday through Monday and tallied accordingly.

Numeric

If you'd like to calculate the week number, but specify that each week begin on a Sunday (in other words, week one always ends on the first Saturday of January and following weeks are tallied based on that premise) try this formula instead:

Set conditions across fields: If a user appears in any one of three fields show the record on a report

Report needs to show records where the current user appears in any of three fields: Owner, Author or Reviewer

Conditional (If - Then)

Create a custom formula column (type checkbox) with the following formula:

[Owner]=User() or
[Author]=User() or
[Reviewer]=User()

If the
Owner field contains the value that is the current user or
Author field contains the value that is the current user or
Reviewer field contains the value that is the current user
then turn on the checkbox

Because the custom column is a checkbox type field, you don't need to use an If() function to set the condition. If the conditions listed are met, the value in the custom column is automatically Yes (in other words, the checkbox is turned on).

Once you create this formula for the custom column of your report, you must set the filtering section to take advantage of it. Select Custom Column is and then type in the word YES.

The predecessor field (under properties) needs to be changed from formula builder to Type-In.

If the task is not completed, it calculates the expected end date (by adding the amount of time it will take to when it starts), and if the task is completed, then it uses the date that it was actually finished, as entered by the user completing the task.

Workdate

Setting a sales commission based on sale price

Create a formula for the Sales Commission field that calculates commission based on sale price in the Total field

If total is greater than or equal to 100 and less than 250, then display the value 25.
If total is greater than or equal to 250 and less than 1000, then display the value 50
If total is greater than or equal to 1000, then display 100

Otherwise (if none of the above conditions are met) display 0.

Numeric

Calculate a task's finish date, based on the start date and number entered in the Estimated # of Days field.

Note that the DayofWeek() takes a date, and not a workdate, so if the field is a workdate field, then you must convert it to date using ToDate().

Text

Find duration between two dates

Find the length of time between the date a record is created and the date the record's marked complete.

Dates

ToDays([Actual Finish Date] - ToDate([Date Created]))

Take the value in the Date Created field (Date / Time type field) and convert it to a date value. Then subtract that date from the Actual Finish Date. Convert the resulting duration to a number of days.

You must convert the this formula result to days to make it data type numeric.

If the Organization Name field's leftmost three characters are "the" then remove the four leftmost characters (Why specify 4 instead of 3? To remove the space after "the"). Otherwise (if there's no "the") just show the organization name.

Text

Highlight overdue tasks in pink

You want to color code rows based on the Due Date field.

Row Colorization

if ([Due Date] < Today(), "pink", "")

If the value in the Due Date field is less than (e.g. earlier than) today, then color the row pink, otherwise, don't color it.

If the value in the Status field is "Not Started," then color the row a shade of orange (#FFCC99).
If the value in the Status field is "Started," then color the row a shade of blue (#99FFFF).
If the value in the Status field is "In Progress," then color the row a shade of yellow (#FFFFCC).
If the value in the Status field is "On Hold," then color the row a shade of purple (#9999CC).
If the value in the Status field is "Completed," then color the row a shade of green (#99FF99)

If the value in the Status field is none of these, don't color it.

This formula doesn't use the if() function. Because you want to set multiple conditions on one field, it's more efficient to use the case() function.

Take this year (that today's date occurs in) and subtract it from the year listed in the Payoff Date field. Then multiply that number of years by 12 (to get months). Take that number of months and add it to the number of months that results from subtracting this month from the month listed in the Payoff date field.

You're really calculating the number of months in two steps. On the left side of the plus sign, you're figuring out how many years until the payoff date and converting that into months. On the right side of the plus sign, you're figuring out the difference in months. The plus sign then adds those figures together.

Numeric

Calculate an expiration date

Calculate an expiration date based on a contract start date and a period of months.

Dates

AdjustMonth([Start Contract Date], [Contract Length in Months])

Take the date in the Start Contract Date Field and add the number of months in the Contract Length field.

Contract Length is a numeric field which lists a number of months

Date

string together text snippets

Concatenate (string together) text in two different fields. For example, create a field that lists a contacts full name by combing values from the First Name and Last Name fields

Text

[First Name] & “ “ & [Last Name]

Display the value in the First Name field. Display a space. Display the value in the Last Name field.

Note: To create a space between the names, this formula inserts a text literal. QuickBase displays whatever characters appear between a set of double quotes--in this case, a space.

Text

Calculate the date one week from today

Dates

Today() + Days(7)

Display the date that is today plus 7 days.

Date

Add up the number of hours worked in a week.

Sum the values of hours entered for each day of the week.

Calculation

Nz([Mon]) + Nz([Tues] + Nz([Wed]) + Nz([Thurs]) + Nz([Fri])

Return the value in the Mon field. If the Mon field is empty (null) then return zero. Add that to the value in the Tues field. If the Tues field is empty (null) then return zero. Add that to the value in the Wed field, and so on.

You'd use Nz here instead of IsNull, because in order to add these values together, QuickBase needs the result to be a number. Nz generates a zero for a null, which the program can use in the calculation.

Numeric

Display a value that depends upon the value in a percent complete field

Show status "not started" "in process" or "completed" based on the value in the percent complete field.

If the value in the percent complete field is zero, the display the text "not started." If the value in the percent complete field is less than 100%, then display the text "in process." If the value in the percent complete field is 100%, display the text "complete"

A numeric percentage field shows values in percent format, like 50%, 75% and so on. But their real value is the exact mathematical representation of percent, which is always a portion of the whole number, one. For example, 20% is really.2 and 3% is really the number .03.

Text

Create a view that contains records for the "current month" only

Conditional (If - Then)

If(Month([Date])=Month(Today()), "Current Month","Not Current Month")

If the month in the Date field is the same month as today, then display the text "Current Month" otherwise display the text "Not Current Month.

Use this formula to create a view. You can feature the text field in the view to see which records say "Current Month." You can also use this custom formula field in the view's criteria. In other words, design the view so that you only see those records that occur in the Current Month. Read more about using custom columns in views here: https://www.quickbase.com/help/custom_column_in_view.html

If you subtract the date in the Sales Presentation field from today and it's greater than seven, turn the checkbox field on (true), otherwise turn it off (false).

Each line of the formula beginning with "If" repeats these same conditions for each field listed. Each If/Then condition is joined by an "or" operator. This means that if the condition is met in any field, QuickBase will turn on the checkbox field.

Use this formula to create a view. You can feature the checkbox field in the view to see which records have it checked and which don't. You can also use this custom formula field in the view's criteria. In other words, design the view so that you only see those records where this checkbox field is on. Read more about using custom columns in views here: https://www.quickbase.com/help/custom_column_in_view.html

Checkbox

Find records that occur before the current month

Dates

[Start Date]< FirstDayOfMonth(Today())

Start date is before the first day of the month in which today occurs

Checkbox

create a conditional salutation that can contain one or two names depending on content

If the First Name 2 field contains a name, then string it together with the value in the First Name 1 field to create a salutation. If not, then just show the name in First Name 1. For example "Mary & Joe" -Or if there's no text in First name 2, just "Mary"

If First Name 2 is empty, then display the value in the First Name 1 field. Otherwise, display the value in the first name 1 field then display a space, an ampersand and a space followed by the value in the First Name 2 field.

Text

Extract and display only the most recent entry from an append field

Append fields often take up too much room in views. Use a formula field to show only the most recent entry.

Text

Right([comments],"[")

Return the rightmost text from the comments field that follows the last occurence of the [ character.

This solution is for an append field that appends new entries. If the field were set to prepend new entries, the solution would be: Part([comments],2,"[")

Text

If a field is not blank (null), then display a value in another field

Calculate the Revenue field, only if your staff has entered a date in the Submitted for Billing field.

Conditional (If - Then)

Design the actual Revenue field using the following formula:

If(not IsNull([Submitted for Billing]),[Revenue Forecast])

If the Submitted for Billing field is not empty (or null), then display the value from the Revenue Forecast field.

Text

Set status based on whether or not another field is checked.

Automatically set the Status field to "Complete," when a staff member enters a date in the Completion Date field.

Conditional (If - Then)

Design the Status field using the following formula:

if(isnull([Completion Date]), "Pending", "Complete")

If no one's entered a value in the Completion Date field (in other words that field is null) then display the word "Pending." If not, display the word "Complete."

Text

Calculate the number of checkboxes that a user turned on

Total the boxes checked (turned on) within a given record

Calculation

ToNumber([Checkbox1]) + ToNumber([Checkbox2])

Convert the value in Checkbox1 to a number and add it to the value in Checkbox 2. Include as many checkbox fields as necessary

The ToNumber formula returns a 1 for true or yes and a 0 otherwise.

Numeric

Convert a value representing time in minutes into seconds

When I enter a time in minutes and seconds (ie. 3.42) I need to be able to convert these to seconds.

Time

ToNumber(Left([Minutes], ".")) * 60 + ToNumber(Right([Minutes], "."))

The Left function will return all the text up to but not including the period. In the example, that is 3. Since it is a text value, convert it into a number using the ToNumber function and then multiply the result by 60 to get seconds.

Do the same to extract the text after the period using the Right function. Finally, we add it up to get the total seconds -- in this case it should be 3*60 + 50 = 230 seconds.

Use a text field to store the number (like 3.42) since three minutes and 42 seconds is not the same thing as 3.42 seconds.

Numeric

Calculate how many fields are empty

I need to create a field that counts the number of field that are non blank. The field that I need to count are labeled Model number 1, Model number 2 etc...

IsNull returns a yes or no result, which when you convert it to a number using the ToNumber() function, becomes a one for yes or a zero for no. Insert a + operator to add all values together. Add as many additional fields as you need.

This solution assumes that the Model Number fields are numeric. If those fields are text type fields, the IsNull() function wont work. Try something like the following instead:

Return the value in the Actual Duration field. If the Actual Duration field is empty (null) then return zero. Add that to the value in the Actual Duration 1 field. If the Actual Duration 1 field is empty (null) then return zero. Add that to the value in the Actual Duration 2 field, and so on.

You'd use the Nz() function here instead of IsNull(), because in order to add these values together, QuickBase needs the result to be a number. Nz generates a zero for a null, which the program can use in the calculation.

Duration

View records from the previous week only

How do I create a view that on any day of the week I can view records dated from the previous week?

Subtract 7 days from today. Take the resulting date (let's call it "one week ago date") and calculate the first day of its week. If that is greater than the value in the Date field and If that date is less than the last day of the week in which "one week ago date" falls, the result is true (checkbox is on) otherwise the result is false (checkbox is off).

You can create a formula - checkbox type field to hold this formula or just use the formula within the report (view) builder page. In this case, also select checkbox as the type, then within the Matching Criteria section, specify that <custom column> is yes (type in the word yes).

Checkbox

Calculate the date of the last Friday before a Due Date

In each record, I want to calculate what date is the Friday that comes before each Due Date

Dates

PrevDayOfWeek([Date Due], 5)

Take the value in the date due field and figure out the date of the previous week's Friday.

5 represents Friday within the PrevDayOfWeek() function.

Date

Show only records where any checkbox is turned on

Formula to create a view that shows records where [checkbox 1] is turned on OR [checkbox 2] is on OR [checkbox 3] is on and so on

Conditional (If - Then)

If(([F1] or
[F2] or
[F3])=true, true,
false)

If F1 or F2 or F3 checkboxes are turned on (True), then this formula returns True (yes), otherwise it returns false (no).

You'd use this formula to create a "custom column" in the view builder. The custom column type is "checkbox." Then set the view's matching criteria to <custom column> = Yes. When you do so, your view shows only those records that meet the condition of the formula. For more on using formulas to create views and reports, please read: https://www.quickbase.com/help/custom_column_in_view.html

Convert the duration in the hours of operation field to hours. Then divide the value in the gallons consumed field by that number of hours

You can't divide a number by a duration. So you must convert the duration into a number using the ToHours() function.

Numeric

Find the duration between two date fields of different types

Actual Finish is a date field and Actual Start is a workdate field. How can I find the duration between them without getting a type mismatch error?

Type Conversion

[Actual Finish]-ToDate([Actual Start])

Convert the value in the Actual Start field to a date type value. Then subtract that date from the Actual Finish date

Duration

Don't show me this message again

Is there an easy way to create a calendar or timeline from a table report?

Yes. If the application administrator has specified default calendar and timeline settings for an application, you can easily create a calendar or timeline from a table report just by clicking a link. Quick Base uses the default settings as a basis for creating the calendar or timeline.

To create a calendar or timeline from a table report:

Display a table report.

In the menu at the top of the report, click Other, and then click Report Table as a Calendar or Report Table as a Timeline.

Save the calendar or timeline by clicking Save this report.

Note: If calendar or timeline defaults have not been set, then Report as Calendar and Report as Timeline won't appear as options. Read how to set defaults for calendars and for timelines.

foo

We're glad you're interested in doing more with Quick Base!

Now we need to make you official before you share apps or manage your account.

When you bring additional fields into a conversion, Quick Base often finds inconsistencies. For example, say you're converting your Companies column into its own table. One company, Acme Corporation, has offices in New York, Dallas and Portland. So, when you add the City column to the conversion, Quick Base finds three different locations for Acme. A single value in the column you're converting can only match one value in any additional field. Quick Base needs you to clean up the extra cities before it can create your new table. To do so, you have one of two choices:

If you want to create three separate Acme records (Acme-New York, Acme-Dallas and Acme-Portland) click the Conform link at the top of the column.

If the dissimilar entries are mistakes (say Acme only has one office in New York and the other locations are data-entry errors) go back into your table and correct the inconsistencies—in this case, changing all locations to New York. Then try the conversion again.