Skip to main content
Salesforce

Return to Answer

deleted 36 characters in body
Source Link
sfdcfox ♦
sfdcfox ♦
  • 501.7k
  • 21
  • 473
  • 828

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'127, 'Not Applicable',
            '0'0, 'Ignored',
            '1'1, 'Sent',
            '2'2, 'Failed', 
            null
        ),
        'Email Engagement',
        CASE(Email_Event_Status_Code__c,
            '0'0, 'Not Defined',
            '1'1, 'Email Click',
            '2'2, 'Open',
            '3'3, 'Opt-Out',
            '4'4, 'Web',
            '5'5, 'Transaction',
            '10'10, 'Click on mobile notification',
            '11'11, 'Offer click',
            '12'12, 'Impressions for In-App',
            '13'13, 'In-App Click',
            '14'14, 'In-App Dismissal',
            '15'15, 'Impressions for Push',
            null
        ),
        'Subscription',
        CASE(Email_Event_Status_Code__c,
            '0'0, 'Unsubscribe',
            '1'1, 'Subscribe',
            null
        ),
        null
    ),
    null
)

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
)

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
)
Source Link
sfdcfox ♦
sfdcfox ♦
  • 501.7k
  • 21
  • 473
  • 828

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
)