2

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?

Thanks!

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',
'')
Improve this question

2 Answers 2

Reset to default
3

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?)
Improve this answer
5
  • 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!
    – jackerman09
    Commented Aug 16, 2012 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
    Commented Aug 16, 2012 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?
    – jackerman09
    Commented Aug 16, 2012 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
    Commented Aug 16, 2012 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!
    – jackerman09
    Commented Aug 21, 2012 at 16:26
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .