Hello,
I'm not sure this has a solution, but I figured I'd throw it out there just in case. I am tracking subscriptions to a number of email lists in Salesforce using picklist fields with the values "Subscribed", "Unsubscribed" or blank, each list with its own custom field. I would like to build a report that shows the number of contacts with each status for each field, together - something like the attached is what I'm imagining. I thought maybe I could pull it off by building a couple row-level formulas and grouping by status and list name, but I'm running into some trouble making it work. My first attempt at building something to group by status, I tried:
CASE(OR(TEXT(Contact.list_1__c) ="Unsubscribed", TEXT(Contact.list_2__c) = "Unsubscribed"), "Unsubscribed", OR(TEXT(Contact.Contact.list_1__c) = "Subscribed", TEXT(Contact.list_2__c) = "Subscribed"), "Subscribed",OR(ISBLANK(TEXT(Contact.list_1__c)), ISBLANK(TEXT(Contact.list_2__c))),"")
And I'm getting the error "Incorrect argument type for function 'CASE()'. A little searching tells me that this usually happens because I'm trying to return a TRUE or FALSE value, but at least to my reading of the above I'm not.
I'd be thinking of building something similar for the grouping by list, in principle.
Not sure where to go from here, or honestly if this is even possible...also occurs to me that people being subscribed to more than one list might complicate matters. The easier way to handle this would be to create a separate report for each list showing these numbers, but given that we have something like 15 lists I don't particularly want to do that if I can avoid it.
#Reports & Dashboards #Formulas
Hi,
You can't use OR in a CASE function, and it wouldn't work here anyway. You could use IFs though:
IF(OR(TEXT(Contact.list_1__c) ="Unsubscribed", TEXT(Contact.list_2__c) = "Unsubscribed"), "Unsubscribed",
IF(OR(TEXT(Contact.Contact.list_1__c) = "Subscribed", TEXT(Contact.list_2__c) = "Subscribed"), "Subscribed",
NULL))
Now, bear in mind that, if you have 1 subscribed and 1 unsubscribed, this formula would return "Unsubscribed". If you want to return "Subscribed" since one of the field is, you need to change the order of the formula:
IF(OR(TEXT(Contact.Contact.list_1__c) = "Subscribed", TEXT(Contact.list_2__c) = "Subscribed"), "Subscribed",
IF(OR(TEXT(Contact.list_1__c) ="Unsubscribed", TEXT(Contact.list_2__c) = "Unsubscribed"), "Unsubscribed",
NULL))
