Skip to main content

Formula Library

Copy common AnyDB formulas for rollups, lookups, conditions, dates, text formatting, and advanced record patterns.

Use these examples as starting points. Replace field names like {{Amount}}, {{Status}}, and {{Due Date}} with the fields in your own type.

Totals

Aggregation

Sum, count, average, group, and report across child records or records in a type.

Conditional Totals

Conditional Aggregation

Sum, count, average across child records with specific conditions

References

Lookups

Pull values from referenced, parent, child, and linked records.

Rules

Conditional Logic

Return different values, labels, colors, or validation results based on conditions.

Schedules

Dates & Time

Calculate age, duration, deadlines, readable dates, and date-based reports.

Display

Text & Formatting

Build names, clean text, format values, and generate readable labels.

Dashboard

Reports

Create Reports and Dashboards using our REPORT formula or cell

Power

Advanced

Work with reports, arrays, one-time values, sequence numbers, and validation helpers.

Audit

Record Audit Users

Capture the email of the user who created or updated a record.

Aggregation​

Sum child record values with a type name​

Total amount from child records of type Invoice

SUM(C@CURRREC!N@Invoice{{Amount}})

Count child records​

Number of attached child records of type Invoice

COUNT(C@CURRREC!N@Invoice!{{Name}})

Average child record amounts​

Average child amounts of type Invoice

MEAN(C@CURRREC!N@Invoice!{{Amount}})

Conditional Aggregation​

Packed Data (JSON) cell for conditional aggregations​

Packed Data cell in child records are needed when conditional aggregations are needed

{ type: {{Type}}, qty: {{Quantity}}, total: {{Total}} }

Sum child records that match a condition​

Total 'total' field when type is 'Open' using Packed Data cell

SUMBY(FILTER(C@CURRREC!N@Invoice!{{Packed Data}}, {type: "Open"}), 'total')

Find Max value from child records that match a condition​

Find max 'total' field when type is 'Open' using Packed Data cell

MAXBY(FILTER(C@CURRREC!N@Invoice!{{Packed Data}}, {type: "Open"}), 'total')

Count of Child Records that match a condition​

Find count of child records when type is 'Open' using Packed Data cell

COUNT(FILTER(C@CURRREC!N@Invoice!{{Packed Data}}, {type: "Open"}))

Lookups​

Read a field from first child record​

Invoice total from the first child

C@CURRREC!Invoice!{{Amount}}[0]

Read a field from all children records into an array​

Invoice totals from the list of all children

C@CURRREC!N@Invoice!{{Amount}}

Read a value from the parent record​

Parent project status from the first parent, (usually only one parent exists)

A@CURRREC!N@Parent Object Name{{Status}}[0]

Read a field from a referenced record​

Customer email from selected customer in A7

DYNREF(A7, {{Email}})

Read a field from a record by name​

Tax rate kept in a record named "Pricing Config", so it can be edited without changing the type

{{Subtotal}} * (1 + S@Pricing_Config!{{Tax Rate}})

Conditional Logic​

Return a status based on a condition​

Reorder status

IF({{Stock}} <= {{Reorder Level}}, "Reorder", "OK")

Conditional Formatting for a given cell based on value​

Cell background color

IF(CURRCELL > 100, 'red', 'green')

or

IF(CURRCELL>100, '#FF0000', '#00FF00')

Conditional Formatting based on multiple values​

IF(CURRCELL=='Urgent', '#FF7575', 
IF(CURRCELL=='High', '#FFB5A6', 
IF(CURRCELL=='Medium', '#FFF9C4', '#F1F1EF')))

Convert checkbox values to text​

Checkbox display label

IF({{Approved}} == 1, "Approved", "Pending")

Dates & Time​

Today's date​

Current date

TODAY()

Days until due​

Days remaining

DAYS(TODAY(), {{Due Date}})

Days overdue​

Overdue days

IF({{Due Date}} < TODAY(), DAYS({{Due Date}}, TODAY()), 0)

Add days to a date​

Follow-up date

DATEADD({{Created Date}}, 7, "days")

Format a date for display​

Readable due date

FORMATDATE({{Due Date}}, "MMM DD, YYYY")

Text & Formatting​

Build a record name​

Invoice record name

CONCAT("INV-", {{Invoice Number}}, " - ", {{Customer Name}})

Build a record name based on condition​

Invoice record name if customer name is set

CONCAT("INV-", {{Invoice Number}}, " - ", IF({{Customer Name}}, {{Customer Name}}, 'Not Set'))

Clean extra spaces​

Clean customer name

TRIM({{Customer Name}})

Convert text case​

Uppercase status

UPPER({{Status}})

Create initials​

Contact initials

CONCAT(LEFT({{First Name}}, 1), LEFT({{Last Name}}, 1))

Format a percentage​

Readable completion

READABLEPERCENT({{Completion}})

Replace text in a field​

Normalize department name

SUBSTITUTE({{Department}}, "Sales Team", "Sales")

Reports​

Sum records across a type​

Revenue this month

REPORT(
  'sum',
  'Invoice',
  'Total',
  [['Invoice Date', '>=', 'now/M'], ['Invoice Date', '<', 'now+1M/M']]
)

Count records from the last 30 days​

Recent ticket count

REPORT(
  'count',
  'Software Ticket',
  'Ticket ID',
  [['Created Date', '>=', 'now-30d']]
)

Record Audit Users​

Capture the record creator's email​

Store the email address of the user who created the record:

RECORD_USER("created", "email")

Capture the latest updater's email​

Store the email address of the user who most recently updated the record:

RECORD_USER("updated", "email")

Create a labeled audit value​

RECORD_USER is asynchronous, so use CONCAT_A when combining its result with text:

CONCAT_A("Created by: ", RECORD_USER("created", "email"))

These are snapshots taken when the formula evaluates. A later change to the user's account email does not automatically update an existing stored value. See RECORD_USER for details.

Advanced​

Generate a sequential number​

Record sequence number with INV as the unique sequence id starting at 1000

SEQNUM("INV", 1000)

Record sequence number with a prefix

CONCAT_A('INVOICE-',SEQNUM('INV', 1000))

Record sequence number with a prefix with leading zeros = INVOICE-001

CONCAT_A('INVOICE-', TEXT_A(SEQNUM('INV', 1), \"000\"))

Calculate once and keep the value​

Created timestamp snapshot

GETONCE(SUM(A1:A10))

Group records for a chart​

Tickets by priority

REPORT('terms', 'Software Ticket', 'Priority')

Extract chart labels from grouped report results​

Chart labels (X-Axis)

MAP({{Priority Report}}, "key")

Extract chart values from grouped report results​

Chart values (Y-Axis)

MAP({{Priority Report}}, "count")

Validate common field formats​

Email validation

ISEMAIL({{Email}})

Phone validation

ISPHONE({{Phone}})

URL validation

ISURL({{Website}})