Getting this error:

Error: Invalid Data. 
Review all error messages below to correct your data.
Formula(s) that reference this field are no longer valid: Syntax error. Found 'end of formula'

Saw some posts mentioning that it could be exceeding the character limit, but the syntax check outputs this:

No syntax errors in merge fields or functions. (Compiled size: 2,012 characters)

Account_Type__c is a picklist, so this formula should not have any issues with compounding references to other formulas.

Any ideas on why this isn't working?


Here's the code:

CASE( 1,
IF(ISPICKVAL( Account_Type__c ,'93-Profit Sharing'),1,0), 'Retirement',
IF(ISPICKVAL( Account_Type__c ,'97-Other Retirement Accounts'),1,0), 'Retirement',
IF(ISPICKVAL( Account_Type__c ,'90-IRAs'),1,0), 'Retirement',
IF(ISPICKVAL( Account_Type__c ,'92-Agent - Pension'),1,0), 'Retirement',
IF(ISPICKVAL( Account_Type__c ,'69-IRA'),1,0), 'Retirement',

IF(ISPICKVAL( Account_Type__c ,'41-Revocable - Sole'),1,0), 'Trust',
IF(ISPICKVAL( Account_Type__c ,'43-IRREV - Sole'),1,0), 'Trust',
IF(ISPICKVAL( Account_Type__c ,'45-Term Trust '),1,0), 'Trust',
IF(ISPICKVAL( Account_Type__c ,'31-Trustee U/W'),1,0), 'Trust',

IF(ISPICKVAL( Account_Type__c ,'61-Investment Agency - Sole'),1,0), 'Agency',
IF(ISPICKVAL( Account_Type__c ,'63-Cemeteries'),1,0), 'Agency',
IF(ISPICKVAL( Account_Type__c ,'66-Investment Agent for Executor/Administration'),1,0), 'Agency',
IF(ISPICKVAL( Account_Type__c ,'65-Investment Agency for Trustee'),1,0), 'Agency',
IF(ISPICKVAL( Account_Type__c ,'62-Partnerships'),1,0), 'Agency',
IF(ISPICKVAL( Account_Type__c ,'Discretionary Investment Advisory Account'),1,0), 'Agency',

IF(ISPICKVAL( Account_Type__c ,'11-Personal Representative'),1,0), 'Estate',
IF(ISPICKVAL( Account_Type__c ,'12-Co-Personal Representative'),1,0), 'Estate',
IF(ISPICKVAL( Account_Type__c ,'19-Agent for Personal Representative'),1,0), 'Estate',

IF(ISPICKVAL( Account_Type__c ,'71-Custodian, Tax Service, Limited Agent'),1,0), 'Custodial',
IF(ISPICKVAL( Account_Type__c ,'72-Custodian, No Tax Service, Limited Agent'),1,0), 'Custodial',
IF(ISPICKVAL( Account_Type__c ,'75-Escrow'),1,0), 'Custodial',

I tested your Formula in a clean Developer Edition and it works correctly with no errors. This means that its not the new formula that is wrong. To help further can you answer these questions:

  • What Formula was in this field before?
  • Are there any fields that are based or reference this field from other objects? (Such as a Formula field in the Contact object?)
  • The formula field previously had a formula that was the same, except with different picklist values. I changed the values in Account_Type__c, so I am now trying to update the formula to reflect the new picklist values. No, I don't think there are any other fields that reference this field. Thanks! Aug 16 '12 at 14:41
  • Are you able to create a brand new field and use the above formula to see if it will compile?
    – Jon Hazan
    Aug 16 '12 at 14:48
  • This worked. I'm just going to use the new field from now on, thanks. Any idea why it didn't work before? Aug 16 '12 at 16:30
  • 1
    Its very likely you have another field that referenced Account_Type__c in some way and the process of removing the old formula and adding the new triggered some sort of safety in Salesforce. If you try deleting Account_Type__c do you get any error messages?
    – Jon Hazan
    Aug 16 '12 at 16:35
  • Thanks, I'm not going to try deleting it on the off-chance that it would screw something up, but it seems to be working now so thanks for your help! Aug 21 '12 at 16:26

I know this is super old, but I'd recommend using the CASE function without the IF statements. In looking at the formula, I don't think this will do what you need it to. No matter what the picklist value, you only get a 1 or 0 and so there's bound to be a conflict since all values such as agency, trust, etc are being mapped to both 1 and 0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.