I have a formula field that assigns a code based on a picklist selection. This then populates a text field on a record created in a flow.
I added a new code that starts with a zero (07). What I noticed though is when it populates the text field during record creation, it leaves the zero off the code.
How can I ensure that the zero remains?
TEXT(IF(
ISPICKVAL(Type_of_Grant__c, "Rent")
||
ISPICKVAL(Type_of_Grant__c, "Utility")
||
ISPICKVAL(Type_of_Grant__c, "Pharmacy")
||
ISPICKVAL(Type_of_Grant__c, "Move-in")
||
ISPICKVAL(Type_of_Grant__c, "Aging in Place")
,
23, IF(ISPICKVAL(Type_of_Grant__c, "Other") && Funding_Source__c = "Marc Berman", 23,
IF(ISPICKVAL(Type_of_Grant__c, "PHP-Security Deposit (Non-Subsidized)"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "PHP-Security Deposit (Subsidized Housing)"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "PHP-First Month's Rent (Non-Subsidized)"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "PHP-First Month's Rent (Subsidized Housing)"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "PHP-Utility (Non-Subsidized)"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "PHP-Utility (Subsidized Housing)"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "STRMU-Rent"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "STRMU-Mortgage"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "STRMU-Utilities"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "C19R-Rent"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "C19R-Mortgage"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "C19R-Utilities"), 20,
IF(ISPICKVAL(Type_of_Grant__c, "Measure A"), 07,
IF(ISPICKVAL(Type_of_Grant__c, "DHSP Emergency Financial Assistance"), 20,
NULL)))))))))))))))))
#Formulas
Steven Trumble (Strum Consulting) 
Forum Ambassador
Two things:
1) your formula structure is quite strange. You are saying "if this condition, then return a number, but hten convert that number back to text"
If you want text, just return text immediately e.g.
IF(ISPICKVAL(Type_of_Grant__c, "Other") && Funding_Source__c = "Marc Berman", 23,
IF(ISPICKVAL(Type_of_Grant__c, "PHP-Security Deposit (Non-Subsidized)"), 20,
can simply become:
IF(ISPICKVAL(Type_of_Grant__c, "Other") && Funding_Source__c = "Marc Berman", "23",
IF(ISPICKVAL(Type_of_Grant__c, "PHP-Security Deposit (Non-Subsidized)"), "20",
Then you no longer need to wrap everything in a TEXT() function.
2) You should avoid multiple IF statements on picklist functions. It will massively increase your compile size. CASE() is far more efficient and easier to read.
CASE(
Type_of_Grant__c,
"Rent", "23",
"Utility", "23",
"Pharmacy", "23",
"Move-in", "23",
"Aging in Place", "23",
"PHP-Security Deposit (Non-Subsidized)", "20",
"PHP-Security Deposit (Subsidized Housing)", "20",
"PHP-First Month's Rent (Non-Subsidized)", "20",
"PHP-First Month's Rent (Subsidized Housing)", "20",
"PHP-Utility (Non-Subsidized)", "20",
"PHP-Utility (Subsidized Housing)", "20",
"STRMU-Rent", "20",
"STRMU-Mortgage", "20",
"STRMU-Utilities", "20",
"C19R-Rent", "20",
"C19R-Mortgage", "20",
"C19R-Utilities", "20",
"Measure A", "07",
"DHSP Emergency Financial Assistance", "20",
"Other",
IF(
Funding_Source__c = "Marc Berman",
"23",
NULL
),
NULL
)
