Common Formula Patterns

A collection of frequently used formula patterns for Precision Bridge field mappings. Each example shows the formula expression and explains when to use it.

String Manipulation

1. Combine first and last name

[first_name] + ' ' + [last_name]

Concatenates two fields with a space separator. Returns None if either field is null — use if_null() for safety.

2. Safe concatenation with null handling

if_null([first_name], '') + ' ' + if_null([last_name], '')

Returns a clean result even if one or both fields are null.

3. Extract domain from email

[email].split('@')[1] if [email] is not None and '@' in [email] else ''

Splits on @ and takes the second part. Guards against null and missing @.

4. Truncate to a maximum length

[description][:255] if [description] is not None else ''

Truncates to 255 characters. Useful when the target field has a shorter max length than the source.

5. Clean and normalize whitespace

' '.join([description].split()) if [description] is not None else ''

Collapses all whitespace (tabs, newlines, multiple spaces) into single spaces.

6. Replace characters

[phone].replace('-', '').replace(' ', '').replace('(', '').replace(')', '')

Strips common phone formatting characters, leaving only digits.

7. Prefix with a constant

'MIG-' + str([sys_id])

Adds a migration prefix to an ID field. Useful for identifying migrated records.

Conditional Logic

8. Map a boolean to a string

'Yes' if bool([active]) else 'No'

Converts truthy/falsy values to display strings.

9. Priority mapping with fallback

{1: 'Critical', 2: 'High', 3: 'Medium', 4: 'Low'}.get(int([priority]), 'Medium') if [priority] is not None else 'Medium'

Maps numeric priority to text labels using a dictionary, with a default of 'Medium'.

10. Conditional field selection

[work_notes] if [work_notes] is not None and len([work_notes]) > 0 else [comments]

Uses the work notes if available, otherwise falls back to comments.

11. Null-safe chain with first_with_value

first_with_value([assigned_to], [opened_by], [sys_created_by])

Returns the first non-null, non-empty value from multiple fields.

12. Set a field based on another field's value

'Resolved' if [state] == '6' else 'Closed' if [state] == '7' else 'Open'

Chained ternary expression for simple state mapping.

Date and Time

13. Format a datetime for display

datetime.strptime([sys_created_on], '%Y-%m-%d %H:%M:%S').strftime('%d %b %Y') if [sys_created_on] is not None else ''

Converts 2024-06-15 14:30:00 to 15 Jun 2024.

14. Current date as a string

date.today().isoformat()

Returns today's date as '2024-06-15'. Useful for audit fields.

15. Current datetime as a string

datetime.now().strftime('%Y-%m-%d %H:%M:%S')

Returns the current timestamp. Useful for migrated_on fields.

16. Calculate age in days

(datetime.now() - datetime.strptime([created_on], '%Y-%m-%d %H:%M:%S')).days if [created_on] is not None else 0

Returns the number of days since a record was created.

17. Check if a date is within the last 90 days

(datetime.now() - datetime.strptime([sys_updated_on], '%Y-%m-%d %H:%M:%S')).days <= 90 if [sys_updated_on] is not None else False

Returns True if the record was updated within the last 90 days.

ID Translation and Lookups

18. Translate a reference field using correlation_cache

correlation_cache("users", [assigned_to])

Looks up the target system ID for a source user reference. Equivalent to an ID mapping but usable in formula expressions.

19. Translate with fallback

if_null(correlation_cache("users", [assigned_to]), "UNKNOWN_USER")

Provides a default value when the source user was not migrated.

20. Check if a record was already migrated

[sys_id] in correlation_keys("incidents")

Returns True if the record already has a correlation entry. Useful for conditional logic.

Lists and Collections

21. Extract first item from comma-separated field

[tags].split(',')[0].strip() if [tags] is not None and ',' in [tags] else if_null([tags], '')

Extracts the first tag from a comma-separated list.

22. Count items in a comma-separated field

len([tags].split(',')) if [tags] is not None and len([tags]) > 0 else 0

Returns the number of items in a comma-separated list.

23. Remove duplicates from a comma-separated field

','.join(sorted(set([tags].split(',')))) if [tags] is not None else ''

Deduplicates and sorts a comma-separated list.

24. Build a comma-separated list from multiple fields

','.join([x for x in [[category], [subcategory], [type]] if x is not None and x != ''])

Combines multiple fields into a single CSV string, skipping empty values.

Data Transformation

25. Convert bytes to megabytes

round(float([size_bytes]) / (1024 * 1024), 2) if [size_bytes] is not None else 0

Converts a file size from bytes to MB with 2 decimal places.

26. Generate a unique key from multiple fields

[table_name] + ':' + [sys_id]

Creates a composite key by combining table name and record ID.

27. HTML to plain text

extract_text_from_html([html_content])

Strips all HTML tags and returns plain text. Uses the built-in PB function.

28. JSON field extraction

json_path([additional_info], '$.email') if [additional_info] is not None else ''

Extracts a specific value from a JSON string field.

29. Default value for empty strings

[description] if [description] is not None and len([description].strip()) > 0 else 'No description provided'

Treats both null and whitespace-only strings as empty.

30. Mask sensitive data

[email].split('@')[0][:2] + '***@' + [email].split('@')[1] if [email] is not None and '@' in [email] else '***'

Partially masks an email address: jo***@example.com.

Related Documentation

• Introduction to Formulas — Formula syntax basics
• Working with Types — Data types and type conversions
• String Operations — String method reference
• Conditional Logic — if/else patterns
• Available Built-in Functions — Complete built-in reference
• Functions Reference — PB-specific function reference