How to use formula fields to leverage and enhance your data
Below we highlight some formula use cases that may help manage and maintain your data. Pitchly supports an extensive list of pre-built formulas including text, arithmetic and comparison operations among others. There are also two custom Pitchly functions: REFJOIN and AI that further enhance core Pitchly features such as multi-reference field and AI app capabilities. Similar to other field types, filter and sort conditions can be applied to formula fields to segment and arrange data in a desired format. Referenced data from other tables can also be incorporated into formula arguments through the use of lookup fields.
To add a formula field to your table, review our article on how to create and input function syntax. Admin level permissions are required to create a formula field.
Built-in Functions
An extensive collection of pre-built formulas are available here. The formula logic mirrors the functions found in Excel and other spreadsheet software, spanning categories from mathematical operations to lookup and referencing.
Text
Specific formulas like CONCATENATE prove beneficial for merging field values or text strings into a single field. When inputting arguments, existing field names will auto-populate as you type, enclosed in curly brackets upon selection. Enclose the text within quotation marks to link text strings.
Example:
CONCATENATE({Field Name 1}, ", ", {Field Name 2},)
CONCATENATE("Text String 1", ", ", "Text String 2",)
CONCATENATE({Field Name 1}, ", ", "Text String 1", ", ", …"Text String 30",)
Arithmetic Operations
Basic arithmetic operations, including addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^), can be performed using formulas.
Example:
{field a} + {field b}
Operator | Meaning | Example | Description |
+ | Addition | a + b | Add the two arguments |
- | Subtraction | a - b | Subtract the second argument from the first argument |
* | Multiplication | a * b | Multiply the two arguments |
/ | Division | a / b | Divide the first argument by the second argument |
^ | Exponentiation | a ^ b | Raise the first argument by the power of the second argument |
Comparison Operations
You can utilize comparison operators like =, , >, =, >=, and > (not equal to) within formula arguments. These operators prove valuable when combined with the built-in IF function. Suppose you're working with a dataset of deal sizes, and you want to classify them based on their value. You can use comparison operators to assign appropriate labels.
Example:
IF(({Deal Estimate} + {Adjustment Lookup}) > 1000000, "Large Deal","Small Deal")
Operator | Meaning | Example | Description |
= | Equal to | a = b | True if a is equal to b |
| Less than | a b | True if a is less than b |
> | Greater than | a > b | True if a is greater than b |
= | Less than or equal | a = b | True if a is less than or equal to b |
>= | Greater than or equal | a >= b | True if a is greater than or equal to b |
> | Not equal to | a > b | True if a is not equal to b |
Custom Pitchly Functions
Pitchly's custom function types are: RECORD_ID, REFJOIN , AI, CLEANHTML, FORMATDATE and FORMAT CURRENCY.
RECORD_ID is formatted as RECORD_ID() and provides a unique, read-only identifier stored on Pitchly’s backend. This ID is useful for integrations and field mappings. Previously, the auto-increment feature compromised uniqueness because users could overwrite the field. Using this formula prevents that issue.
Please note: This field cannot be designated as a table's primary key because it is of the formula field type.
REFJOIN is formatted as REFJOIN( {MREF FIELD} , DELIMITER ). It combines primary keys from a multi-reference field into a string utilizing the field and a specified string delimiter (designated within quotation marks). This function allows multi references to display in Elements. For a comma delimiter, enter "," in the formula syntax.
Example:
REFJOIN({Deal Parties},",") will combine all values tagged to the multi reference field Deal Parties. The values will display as a comma separated list in the Deal Parties Formula field.
AI formulas are a great way to keep data current. Formulas will automatically adjust when a dependent field value changes. This functionality extends to lookup fields that include a subfield from another table. The AI function is formatted as AI("AI String"). The quoted string, which can include multiple fields designated by {field_name}, undergoes queries by Pitchly’s AI application. These AI functions can subsequently serve as arguments for other functions.
Example:
A formula field entitled Capital City could use the AI function:
AI("Capital city of {State}? City name only")
The function value could then be included in another function argument: CONCATENATE(AI("Capital city of {State}? City name only"), " is the capital city of {State}.")
CLEANHTML is formatted as CLEANHTML( {FIELD}/”STRING” ) and accepts both a field or a string (enclosed in quotes " ") as a parameter. It removes any foreign characters or HTML tags.
FORMATDATE is formatted as FORMATDATE( DATE, “STRING”) and applies a specific date format. Use one “D” or one “M'' to eliminate leading zeros:
YYYY-MM-DD: "2024-01-01"
MM/DD/YYYY: "01/01/2024"
DD/MM/YYYY: "01/01/2024"
DD/MM/YY: "01/01/24"
DD MMM YYYY: "01 Jan 2024"
DD MMM. YYYY: "01 Jan. 2024"
DD.MM.YYYY: "01.01.2024"
DD-MMM-YY: "01-Jan-24"
DD-MMM-YYYY: "01-Jan-2024"
MMM DD, YYYY: "Jan 01, 2024"
MMM. DD, YYYY: "Jan. 01, 2024"
MMM-YY: "Jan-24"
MMM-YYYY: "Jan-2024"
MMM. YYYY: "Jan. 2024"
MMM YYYY: "Jan 2024"
MM/DD/YY: "01/01/24"
MM/DD/YYYY: "01/01/2024"
MM.YYYY: "01.2024"
MMMM DD, YYYY: "January 01, 2024"
DD-MMMM-YYYY: "01-January-2024"
DD MMMM YYYY: "01 January 2024"
MMM ‘YY: "Jan ‘24"
YYYY: "2024"
‘YY: "‘24"
FORMATCURRENCY is formatted as FORMATCURRENCY(FIELD, "String") and applies a specific currency format. String is the currency code, example: “USD” for U.S. Dollars.
Available currency codes:
U.S. Dollars- Code: USD- Symbol: $
British Pounds- Code: GBP- Symbol: £
Euros- Code: EUR- Symbol: €
Australian Dollars- Code: AUD- Symbol: $
Brazilian Real- Code: BRL- Symbol: R$
Canadian Dollars- Code: CAD- Symbol: $
Czech Koruna- Code: CZK- Symbol: Kč
Danish Kroner- Code: DKK- Symbol: kr
Hong Kong Dollars- Code: HKD- Symbol: $
Hungarian Forints- Code: HUF- Symbol: Ft
Israeli Shekels- Code: ILS- Symbol: ₪
Indian Rupee- Code: INR- Symbol: ₹
Japanese Yen- Code: JPY- Symbol: ¥
Malaysian Ringgits- Code: MYR- Symbol: RM
Mexican Pesos- Code: MXN- Symbol: $
New Zealand Dollars- Code: NZD- Symbol: $
Norwegian Kroner- Code: NOK- Symbol: kr
Philippine Pesos- Code: PHP- Symbol: Php
Polish zloty- Code: PLN- Symbol: zł
Singapore Dollars- Code: SGD- Symbol: $
Swedish Kronor- Code: SEK- Symbol: kr
Swiss Francs- Code: CHF- Symbol: CHF
Taiwan New Dollars- Code: TWD- Symbol: $
Thai Baht- Code: THB- Symbol: ฿
Turkish Liras- Code: TRY- Symbol: TL
Saudi Riyals- Code: SAR- Symbol: ر.س
Chinese Yuan- Code: CNY- Symbol: ¥
Apply Filter Conditions or Sorts
To filter on a formula field, click the Filter button and select the desired formula field from the dropdown. The filter operators “is more than” or “is less than” can be applied for formula fields that yield a number value.
Follow a similar process to sort by formula field values.
Please be aware that when filtering or sorting on currency amounts, currency types are not considered in the total value.
Formula Fields in Pitchly Apps
Formula fields operate similarly to other field types in Pitchly apps and can be manipulated in templates to display values in a particular format. For example, in the Elements app, you can select and apply a specific number or currency format from the menu. This format option is not applicable for non-numerical values.
Please note: The Forms app does not support formula fields.
Formula Errors and Limitations
The parsing error message #ERR!: Parsing Error will display in the formula field if incorrect operators, parentheses or misplaced characters are included in the formula input.
Tips for Avoiding Errors:
Use numbers without commas, ex. 3433 rather than 3,433.
An asterisk (“*”) serves as the multiplication operator not “x.”
Confirm that you have accurately positioned both the opening and closing brackets ("{}") and parentheses ("()").
Rather than typing field names directly into formulas, select field references that will appear next to the formula input.
Test formulas incrementally when using multiple arguments. Add one component at a time and check results as you go.
Only one currency type will display when multiple arguments are included in a formula. The currency will default to the currency type included in the first argument.
Document your formulas to explain the purpose and logic. Utilize the field description to include comments or instructions for users.
Verify data types. Arithmetic formulas applied to text value fields will result in errors.
Formula Field Limitations:
A formula field cannot be used in another formula.
Data stored in other tables can only be referenced via formulas using a lookup field.
It is not possible to sum a field value on a number of records in a table to create an aggregated value (ex. using the same company ID to aggregate a deal value without a lookup field).
Pitchly formula fields do not support ARRAY and VLOOKUP functions.
We’d love to hear from you. If you have any feedback on product enhancements or additional questions, email the team at PitchlySupport@pitchly.com.