|Posted: 2016-06-26 19:31|
It's possible to create a report that contains the information of your interest and store it on the relevant Record Types. For example, when you open an Organisation record, you may want to see the list of all Marketing Campaigns that the employees of that Organisation belong to and their relevant membership status. The information below describes how to achieve that.
NOTE: You can either build a report from scratch using the steps in this forum post or you can use the template available within Workbooks. To find the template choose to Create a report from a template report, rather than Create a new blank report. Then, use the filters to find a template called 'View Marketing Campaigns for Employees'.
To build the report from scratch: Start by building a report based on Marketing Campaigns. Click Create a new blank report and name it "View Marketing Campaigns for Employees'"On the Details tab, add the following columns: Campaign Name and Campaign Status. You also need to add Member Name and Status columns, which you'll find if you drill through to Members. Since this report will be used in a custom report field and constrained by the Organisation Reference Number, you also need to add Employer Object Reference as shown below. If you don't want the Reference field to be displayed in the report, you can simply hide the column in the report view.
Your report should now look similar to this below;
This report doesn't restrict the results to a specific Marketing Campaign, but you can apply your own criteria if you want to.
You may want to use Calculated Conditional Styling (using CSS styling) to make it easier to see the status of each Member for a particular Marketing Campaign. To achieve that, add a calculated column, name it Status Colour and use the following formula:
IF( members.status = 'Unknown' , 'background-color: DarkOrange; color: White',
IF( members.status= 'Not Interested' , 'background-color: red; color: White',
IF( members.status= 'Interested' , 'background-color: green; color: White',
'background-color: white; color: Black')))
You may need to amend the formula if you have different Member Statuses in your system. To add the colouring to the Status column, simply open it up, go to the Style tab and select "Calculated conditional styling" > Status Colour as shown below.
Once you've added colouring to your report, you can then add it to the Main tab in your Organisation records. To do that, you need to add a new custom field in Configuration. Click Create new custom field of type "Report" in Organisations. Name the field appropriately and select the report that you've just created. Make sure to constrain by the Organisation Reference number using the Employer Object Reference column from the report as shown below;
The report will now appear on the Main tab in the Organisation on a default form layout, but you may want to amend which form layouts this field appears on.