Formula Cheat Sheet#

Chestwood Art Studio ships art across the country and has the option of monthly payments over the course of 12 months.

We have the subtotal, the tax (based on the state it is shipping to) and Amount Due Monthly. This formula column uses addition, multiplication and division.

The formula used here is:

($Subtotal + ($Subtotal*$Tax)) / 12

We add the subtotal to the calculated tax then divide this by 12 months to get our Amount Due Monthly.

#TypeError: Confirm all columns used in the formula are of Numeric type.

Max: Classes table of the Class Enrollment template.

The formula used in the ‘Spots Left’ column of the Classes table is:

max($Max_Students - $Count, 0) or "Full"

This formula shows the number of spots remaining in a class, or the text ‘Full’ when the class is full or oversubscribed.

We build a list between the parenthesis consisting of two items: $Max_Students - $Count and 0. The formula returns whichever is greater.

When $Count is less than $Max_Students, the difference $Max_Students - $Count is positive and represents the spots left in the class. When $Count exceeds $Max_Students, then the class is full or oversubscribed, and $Max_Students - $Count is negative. The maximum of a negative number and 0 will be 0, so max($Max_Students - $Count, 0) is 0. This represents a full class. The addition of or "Full" is applied when the value is falsy, which means that a 0 is replaced with the text "Full".

Min: Contacts table of the Lightweight CRM template.

The formula used in the ‘Due’ column of the Contacts table is:

items = Interactions.lookupRecords(Contact=$id, Type="To-Do")
return min(items.Date) if items else None

Let’s break this down.

Interactions.lookupRecords(Contact=$id, Type="To-Do") finds all records in the Interactions table where the Contacts match and the Type is To-Do. This returns a list of records that we assign to the variable items.

Next, we use dot notation to find all Dates assigned to the records in our items list. These dates are evaluated to find the minimum date. This is the value that is returned. So, we see the date of the task that is due the soonest.

If there are no items in the list, nothing is returned and the field is left blank.

In the MAX() example, the list has two items: $Max_Students - $Count and 0, and the formula returns whichever is greater. In the min() example, the variable items is pulling a list of records based on the lookupRecords arguments, listing the dates, and returning the smallest date. Note that this is a Python function. If we had written the formula as MIN(), a spreadsheet function, the formula would not work because the spreadsheet formula requires a very specific format.

Custom Product Builder template

The formula used in the Total Cost column of the Select or Add New Products table is:

SUM($Requirements.Cost)

The Requirements column is a hidden column in this table. It is a reference list column that pulls data from the Build Requirements table.

Our formula uses the Requirements column to access the Build Requirements table then pulls the cost for each record in the table.

We use SUM() to sum the costs from each record.

Inventory Manager template

The formula used in the Received column of the All Products table is:

SUM(Incoming_Order_Line_Items.lookupRecords(SKU=$id).Received_Qty)

We use the lookupRecords function to find all records in the Incoming Order Line Items table where the SKU matches the SKU in this row then pull the value in the Received Qty column for each of those records. We use SUM() to find the sum of those values.

The Qty on Order and Sold columns of the All Products table are also great examples of the SUM() function.

Check out another example in our Community Forum: Creating a Sum of Net and Gross profit from multiple tables

Inventory Manager template

The formula used in the Received Qty column of the Incoming Order Line Items table is:

if $Order.Status =='Received':
  return $Qty
else:
  return None

The Order column of the Incoming Order Line Items Table is a reference column that points to the Order Number column of the Incoming Orders table. $Order.Status uses dot notation to pull the value from the Status column of the Incoming Orders table. If the value in this column is equal to Received, the value from the Qty column will be returned. If the value is not equal to Received, nothing is returned.

The formula used in the Date Received column of the Create New Order table is:

if $Status == "Received":
  return NOW()

This is a trigger formula that is triggered when a change is made to the Status column. If the value in the Status column is equal to Received, the current date is returned. If the values are not equal, nothing is returned.

Project Management template

The formula used in the Missed Deadline column of the Missed Deadline table is:

TODAY()> $Due_Date and $Status != "Completed"

If the current date is greater than the date given in the Due Date column and the value in the Status column is not equal to Completed, the formula is True. If either of these statements is false, the formula is False.

Inventory Manager template

The formula used in the Stock Alert column of the All Products table is:

if $In_Stock + $QTY_on_Order > 5:
  return "In Stock"
if $In_Stock + $QTY_on_Order > 0:
  return "Low Stock"
else:
  return "OUT OF STOCK"

Here, we have two different if-return statements; if x is true, return some_value. Once a statement is true and a value is returned, the formula stops. If both are false, OUT OF STOCK is returned.

First, if the value in the In Stock column plus the value in the Qty On Order column are greater than 5, return “In Stock”.

Next, if the value in the In Stock column plus the value in the Qty On Order column are greater than 0, return “Low Stock”. It’s implied that the value is less than or equal to 5 because the first statement would have to be false for this to be evaluated.

Last, if all statements are false, return “OUT OF STOCK”.

Internal Links Tracker for SEO template

The formula used in the Orphaned? column of the Orphaned Pages table is:

len(Links.lookupRecords(To=$id))<1

We use the lookupRecords function to find all records in the Links table where the link in the To column matches the link listed in the Slug column of this row.

We use len() to count the number of records found. If it’s less than 1, the formula is evaluated to be true and the checkbox will be checked. If it’s equal to or greater than 1, the formula is evaluated to be false.

Artwork Orders

The formula used in the Sale Price column is:

if $Appraisal_Value.endswith("k"):
  return float($Appraisal_Value.rstrip("k")) * 1000
return float($Appraisal_Value)

In this example, the Appraisal Value column is a text column that contains alpha-numeric characters. In order to use this value in mathematical formulas, we need to convert from string to float.

If the value in the Appraisal Value column ends with “k”, we first use rstrip() to strip “k” from the string in the Appraisal Value column.

Now that we only have numeric characters, we use float() to convert our string to a float.

Because K represents 1000 and we have removed this from the value, we multiply our float by 1000.

If the value in the Appraisal Value column does not end with “k”, and only contains numeric characters, we can simply use float() to convert our string to a float.

float() is only needed when dealing with alpha-numeric values like we see in the example.

TypeError: can’t multiply sequence by non-int of type ‘float’ This error occurs when a formula attempts to multiply values from multiple columns, at least one of which is not a Numeric type column. In the screenshot below, the Tax column is a Text column. When we change the column type to Numeric, the error is resolved.

TypeError: unsupported operand type(s) for /: ‘float’ and ‘str’ This error occurs when a formula attempts to divide values from multiple columns, at least one of which is not a Numeric type column. In the example seen above, the ‘# of Payments’ column is a Choice column.

When we change the column type to Numeric, the error is resolved.

TypeError: unsupported operand type(s) for +: ‘float’ and ‘str’ This error occurs when a formula attempts to add values from multiple columns, at least one of which is not a Numeric type column. In the example seen above, the Tax column is a Text column.

When we change the column type to Numeric, the error is resolved.

TypeError: unsupported operand type(s) for -: ‘float’ and ‘str’ This error occurs when a formula attempts to subtract values from multiple columns, at least one of which is not a Numeric type column. In the example seen above, the Discount column is a Text column.

When we change the column type to Numeric, the error is resolved.

The formula used in the Payment column is:

ROUND($Hours*$Per_Hour, 2)

The ROUND() function follows the format ROUND(value, places) which will round the given value to the number of places specified. Our formula finds the value for $Hours*$Per_Hour then rounds this value to 2 decimal places.

Mixing Products

The formula used in the Rounded Value column is:

mix_list_str = $Mix_Product.Lt_per_100_Lt
mix_list_float = [float(i) for i in mix_list_str]
x = [Lt * $Water/100 for Lt in mix_list_float]
round_x = [ROUND(num, 2) for num in x]
l = $Mix_Product.Product
' '.join('{} {}'.format(first, second) for first, second in zip(l, round_x))

Let’s break this down.

$Mix_Product represents the Mix Product column, a reference list column that pulls data from the Product column of Table1. We can use this column as a link to Table1 to pull other data. $Mix_Product.Lt_per_100_Lt uses the reference list column, Mix Product, to pull values from the Lt per 100 Lt column of Table1 for the products listed in the Mix Product column of Table2 then assigns this list of values to the variable mix_list_str. This is the same formula used in the Lt per 100 Lt column of Table2 so you can see the value it returns in row 1 of Table2. It returns a list: ['0.2355', '1.2579']. This list is evaluated as a string rather than numerical values. We need to convert each value in this list to a float.

In our next formula, [float(i) for i in mix_list_str], we iterate through the list that was assigned in the first equation to mix_list_str and convert each value to a floating-point number. We want to convert to a float rather than integer because not all values are whole numbers and contain decimals. i is a variable representing each value. So each value in mix_list_str is evaluated in the equation float(i). float(0.2355) converts 0.2355 to a float and float(1.2579) converts 1.2579 to a float. Now, we assign our list of floats to the variable mix_list_float.

We can now use our float values in a mathematical equation. Once again, we iterate through the list that was assigned to the variable mix_list_float. In our equation [Lt * $Water/100 for Lt in mix_list_float], Lt represents each value in mix_list_float and $Water represents the value found in the Water column which is 1000. We evaluate the equation Lt * 1000/100 when Lt = 0.2355 and Lt = 1.2579 which returns the list [2.355, 12.579]. We assign this list to the variable x.

To round the values in x to two decimal places, we need to evaluate the equation ROUND(num, 2) where num represents each value in our list and 2 specifies the number of decimal places we want to round to. This returns the list [2.36, 12.58] which we assign to the variable round_x.

In the first equation, we used our reference list column, Mix Product, as our link to Table1 in order to pull data from Table1 into Table2. We use this method again in $Mix_Product.Product to pull data from the Product column of Table1. This returns a list of products; [Prod A, Prod B]. We assign this list to the variable l.

Finally, we use the join() method to combine our two lists. ' ' is our starting (empty) string. We use Python’s format method to format our string. {} is a placeholder for each variable listed in .format(). Last, we use Python’s zip() function to pair the first values from each list together and then pair the second values in each list together. l is assigned as our first list and round_x is assigned as our second list. l = [Prod A, Prod B] and round_x = [2.36, 12.58]. Zipping our lists into '{} {}'.format(first, second) gives us Prod A 2.36 in our first iteration and Prod B 12.58 in our second iteration. Our final return value is Prod A 2.36 Prod B 12.58.

Community Example: Using Row ID

The formula used in the 5-digit ID column of the ID Examples table is:

'TCH{:0>5}'.format($id)

'{:0>5}'.format($id) takes the unique row ID and formats it to be a minimum of 5 digits. We then add this to our string "TCH" to get our final value. If the $id is longer than 5 digits, the formula returns the string unmodified.

We can do the same thing using the str.zfill() method.

The formula used in the zfill Method column of the ID Examples table is:

str($id).zfill(5)

str($id) converts the row ID to a string. .zfill(x) returns a copy of the string with leading zeros to make a string of length x. In our example, it adds leading zeros to make the string 5 characters in length. Again, if the $id is longer than 5 digits, the formula returns the string unmodified.

If you mean to combine a string and a numerical value, be sure to convert it to string using str().

The formula used in the Full Name column of the Students table is:

$Last_Name + ", " + $First_Name

Here, we are combining the value found in the Last Name column with a comma followed by a space followed by the value from the First Name column. When adding any extra characters or spaces, place these between double quotes, as we did in the example with ", ".

An alternative combination of these columns for Full Name could be $First_Name + " " + $Last_Name. For the example in row 1, First Name is Brockie and Last Name is Raddon so the value returned would be Brockie Raddon.

Inventory Manager template

The formula used in the SKU column of the All Products table is:

$Brand.Brand_Code+"-"+$Color.Code+"-"+$Size

Brand is a reference column that pulls data from the Name Brand column of the Add Products table. We use this reference column in $Brand.Brand_Code to pull data from the Brand Code column of the Add Products table.

Color is a reference column that pulls data from the Color column of the Color table. We use this reference column in $Color.Code to pull data from the Code column of the Color table.

Each of the values found in $Brand.Brand_Code and $Color.Codeare combined with the value in the Size column with a - between each of the three values to make up the SKU.

The formula used in the Project Name column of the Projects table is:

"{}: {}".format($Client.Name, $Name)

Let’s break this down.

Everything between double quotes " is our string. The curly brackets {} are placeholders for the values found using .format() which is Python’s string format() method.

The first set of curly brackets are replaced with the value found in $Client.Name. Client is a reference column that pulls data for a specific record from the Clients table. $Client.Name is using our reference column, Client to pull data from the Name column of the Clients table.

The second set of curly brackets are replaced with the value found in the Name column of this table.

Although the Client column shows the value that we want, we can’t use $Client like we did $Name. This is because the Client column is a reference column. It is referencing the entire record but uses the value from the Name column of the Clients table as a visual representation of that record. Under the column configuration panel on the right hand side, we can change what column value we see for the record. In the screenshot below, ‘Show Column’ was changed from Name to Email.

It doesn’t change the data, it just changes the label on that data in the Client column. It’s still pointing to the same record but now shows a different label. $Client.Name pulls the Name for the record that is referenced in the Client column, regardless of the label we see.

Custom Product Builder template

The formula used in the All Components column of the CONTRACT_BUILDER Card is:

'\n'.join(sorted(
  "{} — {:g} {}".format(comp.Component, quantity, comp.Unit)
  for (comp, quantity) in $Components.items()
))

We are using the join() method, sorted() function and format() method method all in one!

'\n'.join() adds a new line between each item in the list.

sorted() sorts the items in the list alphabetically.

This leaves us with the following:

"{} — {:g} {}".format(comp.Component, quantity, comp.Unit)
  for (comp, quantity) in $Components.items()

We’ll work through this backwards. First, we need to take a look at the Components column which is a hidden column in the All Contracts table.

This column is a list of components and their associated quantities for the contract. In the for loop, we assign each item in the list of components two variables, comp and quantity. For Components[3]: 6.0, comp = Components[3] and quantity = 6.0. Components[#] specifies a Component in the Components table by Row ID. Components[3] is the component assigned 3 as it’s row id.

Now, we run each item from the list above through the equation "{} — {:g} {}".format(comp.Component, quantity, comp.Unit).

comp.Component replaces the first set of {}. comp is the variable with our component ID so comp.Component finds the value in the Component column associated with that row ID. For Components[3], comp.Component is Nozzle.

quantity replaces the second set of {}. Again, the quantity is the second variable in our list. For Components[3]: 6.0, quantity is 6.0. Our second set of {} are not empty. They include :g*. This converts the value to a floating-point number.

comp.Unit replaces the last set of {}. comp is the variable with our component ID so comp.Unit finds the value in the Unit column associated with that row ID. For Components[3], comp.Unit is None.

*Note that {:g} formats floating point numbers in a particular way that omit decimals when they aren’t needed. There are many options available within placeholders for formatting numbers, dates, etc. Learn more about placeholders here.

Email Contacts template

The formula used in the Body column of the Advanced Compose table is:

"Dear %s,\n\nWelcome to the %s team!" % ($Contact_Name_as_Plaintext, $Team)

This technique uses the % operator instead of the format() method. Format specifiers begin with % followed by a character that represents the data type. %s is a placeholder for a string. The first %s is replaced with the value found in the “Contact Name as Plaintext” column which is a hidden column and the second %s is replaced by the value in the Team column.

\n adds a new line.

Community Example: Colors

The formula in the “Color Reference (Just URL)” column of Table 2 is:

split = $Color_Reference.Color.split(" ")
return split[-1]

$Color_Reference.Color uses the reference column, “Color Reference” to pull data from the table it is referencing, Table 1. Specifically, it pulls the value from the Color column of Table 1.

Color is a text column that contains a hyperlink with a label. We only see the label in Table 1 but as you can see in the screenshot above, the value in the ‘pink’ cell is expanded to show the entire string which contains “pink” followed by the URL. You can also see this in the “Color Reference” column of Table 2. We want to get the link by itself in “Color Reference (Just URL)”. We can do this using Python’s split() method.

.split(" ") allows us to split the string anywhere there is a space (" "). In the Color column, there is a label followed by a space followed by the URL. The value from the Color column is split into a list containing two items Label and URL. This list is assigned to the variable split.

We want to return the last item in the list split in order to get our URL. The last item in a list always has index [-1]. return split[-1] returns the last item in the list split.

The formula used in the Advertisement column of the 2022 Grand Openings table is:

"Coming soon to a city near you!\n" + " : ".join($New_Location_s_in_2022)

Here, we are joining multiple strings to create our advertisement.

"Coming soon to a city near you!\n" is returned almost exactly as we see it, minus the quotes "" and \n at the end of the string.

The quotes "" specify that this is a string and \n is actually a newline character that can be used to specify a new line within a string.

" : ".join($New_Location_s_in_2022) is also a string but uses Python’s join() method to join values from our choice list column, “New Locations in 2022”. What we see in quotes before .join is what will separate each value in our list.

In this example, each value is separated by a space, : and another space.

The formula used in the Duplicate? column of the Duplicates table is:

len(Duplicates.lookupRecords(Grocery_List=$Grocery_List))>1

Let’s break this down, working from the inside > out.

Duplicates.lookupRecords(Grocery_List=$Grocery_List)

This is a lookupRecords function that follows the format of:

[Table_Name].lookupRecords([A]=$[B])

Where [Table_Name] is the name of the table you want to lookup data in. [A] is the column in the table being looked up (named at the beginning of the formula) and [B] is the column in the current table / the table you are entering the formula in.

This formula looks up records in the Duplicates table where a record in the Grocery List column matches another record in the same column.

len() counts the number of records in our list. Since each duplicate will match with the other, it should appear twice in our list. This is why len() > 1.

if len() > 1, the formula is true. If len() <= 1, the formula is false.

This same formula can be used in conditional formatting. This can be seen in the ‘Grocery List’ column of the Duplicates table.

If len() > 1, our formula is true and the conditional cell color is applied to these cells.

If len() <= 1, our formula is false and the cell color is unchanged.

Custom Product Builder template

The formula used in the Contract No. column of the Contract Builder table is:

$id + 500

Here, we are using a trigger formula to create a unique Contract Number when a record is created.

Class Enrollment template

The formula used in the Count column of the Classes table is:

len(Enrollments.lookupRecords(Class=$id, Status="Confirmed"))

This is refered to as a Reverse Lookup. We can use the row id to match a record in another table where a reference column is used. LookupRecords follows the format [Table_Name].lookupRecords([A]=$[B]). [Table_Name] is the name of the table you want to lookup data in. [A] is the column in the table being looked up (named at the beginning of the formula) and [B] is the column in the current table / the table you are entering the formula in. Lookup Records creates a list of records that match the criteria listed. len() counts how many records are in that list.

Here, we are looking up records from the Enrollments table where the record called out in the Class column (our reference column) has the same row ID as the row in the table you are entering the formula. Additionally, the value in the Status column of the Enrollments table is Confirmed. We’ll walk through this.

The table we are looking up records in is the Enrollments table. Our criteria comes from the Class column and the Status column.The criteria for Status is straightforward; the value must be Confirmed in order to be included in our list of records. Class is a bit more complicated. As we see in the screenshot below, Class is a reference column that pulls data from the Classes table. Here, the Class column shows 2018F-Stars.

A reference column points to the entire record, not just the value you see here in the Class column. Using the configuration panel on the right hand side of the screen, you can pick any column from the originating table to show. For this example, the Class column shows the value from the Class Code column of the Classes table but it points to the entire record where the class code is 2018F-Stars.

As you can see in this screenshot, the Row ID for this particular record is 2 and because we are calculating the Count for the row with Row ID = 2, it will count all records in the Enrollment table where Class shows 2018F-Stars and Status is Confirmed.

Restaurant Custom Orders template

The trigger formula used in the BOM # column of the Bill of Materials table is:

MAX(o.BOM_ for o in Bill_Of_Materials.all if o.id != $id) + 1

First, we’ll walk through the formula inside the parenthesis then work outwards.

Here, o is a variable representing each record in our table. o.BOM_ represents the BOM # for each record and o.id represents the row ID for each record. This is a for loop that makes a list of the BOM # for each record in the table Bill of Materials when the record ID does not equal the ID of this row.

MAX() finds the maximum BOM # in the list then + 1 to get our final value.

This is a trigger formula that only applies to new records. When a new record is created, the formula finds the highest BOM # in the table then adds 1 so we have a unique BOM # for the new record.

Community Example: Removing Duplicates From a List

The formula in the All Divisions column of the Abroad Trips table is:

confirmed_div = $Attending_Confirmed.Role_Division.Division
pending_div = $Attending_Pending.Role_Division.Division
full_list = confirmed_div + pending_div
return sorted(set(full_list))

We will walk through this one line at a time.

Attending-Confirmed is a Reference List column that pulls data from the EMPLOYEES table. $Attending_Confirmed.Role_Division pulls the value from the Role Division column of the EMPLOYEES table. The Role Division column in the EMPLOYEES table is a reference column itself, which points to a record in the Divisions table. Chaining allows us to specify what information we want from this record. In this case, we want the Division. We expand our formula to $Attending_Confirmed.Role_Division.Division. The Division is found for each employee listed in the Attending-Confirmed column, creating a list. We assign this list of divisions to the variable confirmed_div.

Attending-Pending is also a Reference List column that pulls data from the EMPLOYEES table. $Attending_Pending.Role_Division.Division does the same as above except now we pull the division for each employee in the Attending-Pending column. We assign this list to the variable pending_div.

We create a list of all divisions by adding the two lists together and assigning this combined list to the variable full_list.

return sorted(set(full_list)) first uses Python’s set() method to create a list with no duplicate divisions. We then use the sorted() method to return our set of divisions, sorted alphabetically.

Note that the formula above can be simplified even further to:

sorted(
  set($Attending_Confirmed.Role_Division.Division) |
  set($Attending_Pending.Role_Division.Division)
)

The formula used in the Last Updated column of the Tasks table is:

NOW()

This is a trigger formula that triggers when a change is made to any field for this record. When a change is made, this formula runs its calculation. NOW() calculates the current time and date for the time zone selected.

The formula used in the Created By column of the Tasks table is:

user.Name

This is a trigger formula that triggers when a new record is created. When the record is created, this formula runs its calculation. user.Name looks up the user account that is logged into Grist and returns the name associated with that account.

Community Example: Filtering Data Within a 1-Month Range

The formula used in the Falls in 1 Month Range? column of the Interactions table is:

TODAY() >= $Date >=  DATEADD(TODAY(),months=-1)

TODAY() returns the current date.

$Date is the name of a column in our table, which is a Date type column.

DATEADD(start_date, days=0, months=0, years=0, weeks=0) returns the date that is the given number of days, months, years, or weeks before or after the start_date. In this example, it returns the date that is one month prior to the start date, TODAY().

This formula is true if the date value in the Date column falls between TODAY() and our DATEADD() date which is one month ago. If the date value in the Date column does not fall between these two dates, the formula returns false.

We can use this column to filter our data. If we only want to see interactions that fall within the 1 Month Range, we would filter to only include true values. If we want to see interactions that fall outside of the 1 Month Range, we would filter to only include false values.