If you use CTAs for managing and reporting on risk, you might get requests like this from the business:

Can you add the open risks to xyz] report?

How can I see a summary of the open risks on the account from the company level] report?

How do you report on risks using objects not sourced from Call to Action?

Here’s how we accomplished this using a multi-select field!

Step 0: Add your new multi-select field to Company object

Ours is called “Open Risks”

Step 1: Create a custom object that will be your “flattened object”

I titled mine “Flattened Object for Open Risks.” The purpose of this object is hold one record per company with each “open risk” being its own column. The dataset columns will be each of your risk types.

Be sure to include a string field called “Null value.” You’ll use this later when creating the multi-select.

Step 2: Create a rule to load to your flattened object

To get data in your new custom flattened object, create a rule to fetch from Call to Action.

First, fetch from the Call to Action object (or your source object) and filter to find open CTAs. Then, add a transform step to group by your Risk Type field and Company GSID. Add a field to your Show Me (you won’t use it, but it’s required when doing a Group By, so ours is just COUNT of Company Name).

You could probably do this through a Pivot, but we chose to do a Transform task with several actions.

In your Setup Actions, you’ll have one branch per risk type. The branch criteria will look to see if the account has the Risk Type in question, and if so, the action will be to load the name of that Risk Type to the specific column in the flattened object.

After running the rule, your flattened object will look something like this:

In the above example, Company 1111 has two open risk CTAs -- Adoption and Sentiment. Company 2222 has one open risk CTA, Engagement

Step 3: Create a rule to Null out your flattened object

Any time a risk CTA is closed, the flattened object will not remove that risk on its own. To ensure your flattened object removes any values once closed, you need to Null out your flattened object each time before the load action. Essentially, you’re wiping the flattened object clean each time before loading it with the current values. 

To create this rule, fetch from your Flattened object. In Setup Action do an Update, and “set to Null” for all of the fields with Company GSID as your identifier.

This rule will ultimately run before loading to your flattened object, but we’ll get into scheduling these rules later in the post.

Step 4: Create your concat & load to your multi-select field

Now comes the good part, where you’ll create the field value that will be loaded to your multi-select field. You’ll do this through a concatenation function. But first, you need to set up your data for this concat to work properly.

1. Fetch from your flattened object the Company GSID, all of the risk types, AND your “Null value” field

   1. Transform step to create case fields that add a semi-colon to the risk type name. You’ll have one case field per risk type. This is where the “Null value” you created in Step 1 comes in handy!
      
      As an example, for our Adoption risk type, the case will look like this: Adoption is null THEN NULL Value DEFAULT Custom Adoption; 
      
      Repeat this “Add Case Field” for each risk type you have. If you have 10 risk types, you’ll have 10 case fields each adding the semi-colon to each one.

   2. Transform step to create a formula field that concats your new risk types with semi-colons, putting a comma between each field. 

2. Fetch from Company the Company GSID and your Open Risks field created in Step 0

3. Merge on Company GSID

For your Actions, Load to Company. You’ll have two actions: one for when your concat field is null, and the other for when your concat field is not null. If the former, set your multi-select field to Null, and in the latter, load your concat field to your multi-select!

Step 5: Create a rule chain and schedule to run

Whether you create a new rule chain or add to an existing, just make sure the three rules you created run chained together. They need to happen in the following order:

1. Null out your flattened object
2. Load to your flattened object
3. Concat & load to your multi-select

There ya go! You’ve now just created a single field on your most reported-on object that will help the business summarize risk.

This is also a handy way to load a “Products Purchased” field based off of a subscriptions dataset, if your company has multiple products that can be purchased.

Does this sound like something you can implement in your org? Is your use case different or the same? Have you done this but in a different way than how I accomplished it? Would love to hear about it!