0

having a little trouble following the necessary logic to get this formula field syntax correct. i need to accomplish the following:

  • If source_system__c="Adobe" AND
    • If type__c="Deliverability" AND

      • If Email_Event_Status_Code__c="127" then "Not applicable"
      • If Email_Event_Status_Code__c="0" then "Ignored"
      • If Email_Event_Status_Code__c="1" then "Sent"
      • If Email_Event_Status_Code__c="2" then "Failed"
      • If Email_Event_Status_Code__c="6" then "Pending"
      • If Email_Event_Status_Code__c="7" then "Delivery canceled"
      • If Email_Event_Status_Code__c="8" then "Prepared"
    • If type__c="Email Engagement" AND

      • If Email_Event_Status_Code__c="0" then "Not defined"
      • If Email_Event_Status_Code__c="1" then "Email click"
      • If Email_Event_Status_Code__c="2" then "Open"
      • If Email_Event_Status_Code__c="3" then "Opt-out"
      • If Email_Event_Status_Code__c="4" then "Web"
      • If Email_Event_Status_Code__c="5" then "Transaction"
      • If Email_Event_Status_Code__c="6" then "Mirror page"
      • If Email_Event_Status_Code__c="10" then "Click on mobile notification"
      • If Email_Event_Status_Code__c="11" then "Offer click"
      • If Email_Event_Status_Code__c="12" then "Impressions for In-App"
      • If Email_Event_Status_Code__c="13" then "In-App Click"
      • If Email_Event_Status_Code__c="14" then "In-App Dismissal"
      • If Email_Event_Status_Code__c="15" then "Impressions for Push"
    • If type__c="Subscription" AND

      • If Email_Event_Status_Code__c="0" then "Unsubscribe"
      • If Email_Event_Status_Code__c="1" then "Subscribe"

all the if's and and's are confusing me. this is what i have so far and its returning a syntax error:

IF(AND(AND(IF(Source_System__c = "Adobe"), (Type__c = Deliverability),(Email_Event_Status_Code__c = "127", "Not applicable))))

any support is greatly appreciated!

thanks!

1

This is much more easily solved with the CASE statement; this eliminates the 20+ extra parentheses you'd need, plus all of the AND operators. Here's the revised logic based on your nested bullet list:

CASE(Source_System__c,
    'Adobe',
    CASE(Type__c,
        'Deliverability',
        CASE(Email_Event_Status_Code__c, 
            127, 'Not Applicable',
            0, 'Ignored',
            1, 'Sent',
            2, 'Failed', 
            null
        ),
        'Email Engagement',
        CASE(Email_Event_Status_Code__c,
            0, 'Not Defined',
            1, 'Email Click',
            2, 'Open',
            3, 'Opt-Out',
            4, 'Web',
            5, 'Transaction',
            10, 'Click on mobile notification',
            11, 'Offer click',
            12, 'Impressions for In-App',
            13, 'In-App Click',
            14, 'In-App Dismissal',
            15, 'Impressions for Push',
            null
        ),
        'Subscription',
        CASE(Email_Event_Status_Code__c,
            0, 'Unsubscribe',
            1, 'Subscribe',
            null
        ),
        null
    ),
    null
)
  • thanks for informing me of the CASE function, had no idea it existed. however, i got this error "Error: Incorrect parameter type for function 'CASE()'. Expected Number, received Text" on the '127' – nueve Jun 16 '20 at 4:14
  • @nueve So it's a number field. You'll need to remove all the quotes around the numbers. – sfdcfox Jun 16 '20 at 4:32

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.