Overview
When using the user property actions in Workflows + Rules, you have the capability to add custom-calculated fields by employing formulas, unlocking powerful data manipulation possibilities:
Set User's Custom Date Property
Set User's Custom Property
Set User's Property
Adding a Formula
📙 Note: However, it's classified as an advanced feature, necessitating proficiency with the formula syntax similar to that used in our Import/Export functionality. Prior experience with this syntax is highly recommended to leverage this feature effectively.
When you select one of the user property actions, the Formula field will automatically display, allowing you to add your own formula. The formula field uses the same formulas as our Import/Export functions.
Testing Your Formula
Once you have added your code, you can use the Test Formula section to preview the results based on a specific user.
Select a user from the drop-down menu.
Click the Evaluate button
.
Review the evaluated formula output.
Writing to Fields Backed by Data Sources
When your formula sets a field that is backed by a data source, the formula's output must match a value that exists in that field's data source. If the formula returns a value the field does not support, the value may not be saved, and the field can appear blank on the person's profile even though the formula evaluated successfully.
Keep in mind that some system fields are locked to a fixed data source. For example, the Are You Hispanic/Latino? system field only accepts Yes/No values, even if a different data source appears to be selected in Field Management.
If you need to store options beyond what a system field supports (such as a "Not answered" value), create a custom field backed by a custom data source that includes those options, and have your formula write to that custom field instead.
Use Cases
Create Prospect Scores
Use a formula to evaluate multiple conditions of a Prospect's record, then assign a score all within a Rule.
Condense Multiple Steps into One
Store multiple conditions in one formula instead of creating one step per condition. This can drastically reduce the number of steps in a Rule that populates a Person's Assignee or populates custom properties.
Clean up Data without Export/Import
Clean up data at scale by using a formula to transform data or correct it.
Formula Examples
Age Calculation
Current Age Calculation
DATE_DIFF("year", DATE_FORMAT([user-dob], "m/d/Y"), DATE_DEFAULT("m/d/Y"))
Calculated Age at Term Start
IF(DATE_DIFF("year", DATE_FORMAT([user-dob], "m/d/Y"), DATE_FORMAT(DS_MAP([user-education-term],"data_source.terms","guid","start_date",""), "m/d/Y")) >= 24, "nontrad", "trad")
Custom Scoring Calculation
Major
IF( DB_MAP("major", [user-education-prefered-major], "guid", "code", "") = "BUSI" | DB_MAP("major", [user-education-prefered-major], "guid", "code", "") = "HS11", 5, IF(DB_MAP("major", [user-education-prefered-major], "guid", "code", "") = "ACC", 3, 1))
Citizenship
IF([user-citizenship-country] = "USA", "5", IF([user-citizenship-country] = "ENG" | [user-citizenship-country] = "FRA", "3", "1"))
Denomination
IF([user-religion-name] = "ME", "5", IF(CONTAINS([user-religion-other], "Methodist") = TRUE, "5", IF([user-religion-name] = "BT" | [user-religion-name] = "EP", "4", IF([user-religion-name] = "EV" | [user-religion-name] = "LU", "3", "1"))))
Rank
IF(
SUM([user-custom-training-score-major], [user-custom-training-score-relig], [user-custom-training-score-cit])>10
,"ideal"
,IF(
SUM([user-custom-training-score-major], [user-custom-training-score-relig], [user-custom-training-score-cit])>5
,"compatible"
,"potential"))