Thursday, December 20, 2018

Create a Customer Saved Search to Show Subscription Categories

Problem:

Create a Saved Search that will list the Customers in one row and the Subscription Categories to which they are subscribed to with the following scenario showing either or both subscription categories.

Subscription Categories:
-Subscription A
-Subscription B

Solution:

1. Create a Customer Saved Search
2. On the Criteria tab > Filters use the filter below:

Field: Formula (Numeric)
Formula: CASE WHEN {subscriptionstatus}='T' THEN 1 ELSE 0 END
Formula (Numeric): Equal to
Value: 1

3. Set other filters that you need.
4. On the Results tab, add the fields that you need plus the following:

Field: Formula (Text)
Summary Type: Maximum
Formula: CASE WHEN {subscription}= 'Subscription A' THEN 'Subscription A' END

Field: Formula (Text)
Summary Type: Maximum
Formula: CASE WHEN {subscription}= 'Subscription B' THEN 'Subscription B' END

5. Save and Run the Search

Note that in the Results tab of the Saved Search, you need to set the Summary Type of other Fields (such as Name, Email, etc) to "Group" and the Formula (Text) should be set to "Maximum"

Results showed one line per Customer and the Subscription Categories shows per column. If a Customer is not subscribed to a particular Category, the column will be blank for that Customer.

No comments:

Post a Comment