Formula Syntax

Classic Experience Functions & Formulas
,
1

Formula basics

Formulas are expressions or equations that can be used to perform calculations and return information. They can range from basic math calculations to complex data manipulation.

Formulas can contain references to:

  • Cell values
  • Operators
  • Tables & columns
  • Functions
  • Conditions

In Honeycode, the biggest game-changer is that formulas are also used to reference objects in your app.

:hammer_and_wrench: Builder tip: It is important to note that formatting table columns to include formula and formats must be applied to the entire column. To that end, Honeycode helps in two ways. When applying formats to a column using the Format button, Honeycode automatically selects the entire column to ensure your data remains uniform down the column, not just one cell.

In addition, Honeycode will mark cells that are not of the same format with a small blue triangle in the corner of the cell. Often called “trace errors,” Honeycode uses a blue marker to note where we detect a format or formula mismatch in the column. A menu allows you to easily apply formats and formula across the entire column. One way this mismatch can happen is when data is copied in from a cell with different formatting or formula.

Apply Formula to Column_Image
Apply Formula to Column_Image756×827 39.5 KB

For example, say you’ve added a formula directly to a single cell in a column. Honeycode will mark that asking if you want to apply that formula to the entire column. For data integrity, you sure do! Or maybe you’ve formatted a column to display as currency, but you’ve copied in plain text. Honeycode will note that and give you the opportunity to apply the currency format to the entire column.

Formula syntax

Syntax refers to the structure and order of a formula, including functions, references, operators, and parameters. If you’re a spreadsheet user, you’ll find much of the formula syntax in Honeycode familiar.

But, since Honeycode formulas also support app objects (the components that make up your app), we have some some cool, new surprises for you, too.

Equals sign =

Formulas always start with an equal sign (=). This tells Honeycode that what follows after is part of a formula. So, when you see a field labeled "Enter formula", the (=) sign is always your starting point.

Operators

Once you've got your equal sign, you're ready to move on to other parts of the formula, like operators. Operators indicate the type of calculation that the formula is running. Operators include:

Mathematic

  • Exponents (^)
  • Multiplication (*)
  • Division (/)
  • Addition (+)
  • Subtraction (-)

Comparison

  • Equals (=)
  • Less than (<)
  • Greater than (>)
  • Less than or equal to (<=)
  • Greater than or equal to (>=)
  • Not equal to (<>)

Honeycode calculates formulas from left to right, using the PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) order of operations.

Columns [ ]

Square brackets reference columns. In many cases, a column will be referenced following a table.

Example: Table1[ColumnA]

However, there are some cases where columns are referenced without a table; in these cases the table is inferred by the context.

Example: [ColumnA]

This occurs when adding a formula to a column or cell within a table, and when configuring an app object whose source is already set to a table.

Functions ( )

Functions are built-in formulas. Functions are denoted by parentheses following the name of the function.

Example: NOW() will return the time at the moment the function is executed.

Some functions take parameters, these are denoted within the parentheses.

Example: SUM(A1, A2) will add the two cells together.

Check out all the functions that Honeycode supports.

Conditions and text values

Many Honeycode functions, like FILTER() and FINDROW(), take conditions. Use double quotes " " to wrap conditions in a formula.

Example: FILTER(TableName,"TableName[ColumnA] > 0")

Quotations marks are also used to reference text values. In the case that a formula references a condition and a text value, simply use two double quotation marks "" "" at the end of the condition.

Example: FILTER(TableName,"TableName[Column A] = ""text value""")

Conditions can also be parameterized by using the percentage symbol %. This can be helpful to reference other cells or simply to improve readability.

Example: FILTER(TableName, "TableName[Column A] = %", A1)

App objects $

When using Builder, any objects placed in your app screen have names that you can use to reference those objects in formulas. For syntax, use a dollar sign followed by brackets $[ ] to reference objects.

Example: $[DataCellName]
Example: $[ContentBoxName]
Example: $[CustomVariableName]

Structured references

If you’re coming from a spreadsheet application, you’ll likely familiar with using cell coordinates in formulas, e.g., =Sheet1!A2. While Honeycode supports the use of cell coordinates in formulas, we encourage you to reference the asset themselves whenever possible.

Example: =TableName
Example: =[ColumnName]
Example: =$[A1]

We call these “structured references,” and they’re preferred because they are more robust and efficient than cell coordinates.

Was this article helpful?
  • Yes
  • No

0 voters

4 Likes
2