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!

Improve this question

1 Answer 1

Reset to default
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
)
Improve this answer
2
  • 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
    Commented Jun 16, 2020 at 4:14
  • @nueve So it's a number field. You'll need to remove all the quotes around the numbers.
    – sfdcfox ♦
    Commented Jun 16, 2020 at 4:32

You must log in to answer this question.

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