Understanding Act! Multi-Select Fields
Act! includes an option to set picklist fields as multi-select. With this option turned on for a field, the user is able to select more than one option from the list. This is a very useful feature, but it presents some difficulties for reporting. This topic will discuss these issues, as well as the special features Reporting4Act! has to handle them. The stock ID/Status field is set as multi-select, so this topic will use the ID/Status field as an example.
When a multi-select field has more than one value selected, these values are reported as a semicolon-separated list. For example, if a contact has both Friend and Reseller selected under ID/Status, then the ID/Status value for this contact is reported as Friend;Reseller. This works fine for simple reporting of this value, but requires special handling in other cases.
Filtering
When filtering on these fields, generally it's necessary to use the contains operator, rather than the equals operator. For example, we might like to have a simple summary report showing the number of friends in our database. To do this, we might try creating a filter of ID/Status equals Friend, giving these results:
| ID/Status | Count |
|---|---|
| Friend | 1 |
| Totals: | 1 |
It looks like there is only one friend in the database. This is happening because the only records that will appear here are those marked as Friend and nothing else. Instead, if we change to the correct filter of ID/Status contains Friend, we get the correct results:
| ID/Status | Count |
|---|---|
| Customer;Decision Maker;Friend | 1 |
| Customer;Friend | 2 |
| Employee;Friend | 1 |
| Friend | 1 |
| Friend;Customer | 1 |
| Friend;Influencer;Prospect | 1 |
| Friend;Prospect | 1 |
| Friend;Reseller | 1 |
| Totals: | 9 |
A final wrinkle is the case where one option in the picklist is contained within another option. For example, the ID/Status picklist might contain both Customer and Former Customer as options. In this case, a filter of ID/Status contains Customer would include Former Customers as well, as the text "Customer" is indeed contained in an ID/Status of "Former Customer". In this case, another filter of and ID/Status does not contain Former Customer would need to be added to produce the proper results.
Grouping and Charting
A similar issue appears when trying to group report data based on a multi-select field. As you can see in the second example output above, the totals are given based on each distinct ID/Status value that is reported; Friend;Prospect is reported as a separate category from Friend;Influencer;Prospect. There is no way to break out the individual options into their own totals. Fortunately, Reporting4Act! includes a feature to handle this case for Act! 2010 and later databases.
Reporting4Act! "Multi" Fields
When Reporting4Act! is connected to a database using the Act! 2010+ data provider, it makes available a second "multi" version of all multi-select fields in the Contact, Company, Group, and Opportunity tables, as well as in any custom tables the database may have defined. These multi fields treat each selected item in the multi-select field as an individual value.
This is easiest to understand using an example of a normal multi-select field and the corresponding Reporting4Act! multi field. First, here are the results of the same ID/Status summary report used above, with no filter applied.
| ID/Status | Count |
|---|---|
| 4 | |
| Consultant | 6 |
| Consultants | 4 |
| Customer | 54 |
| Customer;Decision Maker;Friend | 1 |
| Customer;Friend | 2 |
| Customer;Influencer | 4 |
| Customer;Vendor | 1 |
| Doctor | 1 |
| Employee | 10 |
| Employee;Friend | 1 |
| Friend | 1 |
| Friend;Customer | 1 |
| Friend;Influencer;Prospect | 1 |
| Friend;Prospect | 1 |
| Friend;Reseller | 1 |
| Gate Keeper | 2 |
| Influencer;Customer | 1 |
| Influencer;Prospect | 7 |
| Influencer;Reseller | 1 |
| Influencer;Vendor | 1 |
| Prospect | 86 |
| Prospect;Vendor | 5 |
| Vendor | 3 |
| Totals: | 199 |
This does give some information, but it's more likely that we want to see the totals of each individual option. Using the ID/Status (Multi) field, rather than the ID/Status field, the results are as follows.
| ID/Status (Multi) | Count |
|---|---|
| 4 | |
| Consultant | 6 |
| Consultants | 4 |
| Customer | 64 |
| Decision Maker | 1 |
| Doctor | 1 |
| Employee | 11 |
| Friend | 9 |
| Gate Keeper | 2 |
| Influencer | 15 |
| Prospect | 100 |
| Reseller | 2 |
| Vendor | 10 |
| Totals: | 229 |
This is the output we want, with the totals for each option. Note also that the grand total here of 229 is different than the previous one of 199. In this case, the 229 is a count of the different ID/Status selections, and in the previous case, the 199 is a count of the number of contacts. For example, there is one contact with an ID/Status of Customer;Decision Maker;Friend. This contact is counted once in the first report, but three times in the second - once in each of the Customer, Decision Maker, and Friend totals.
Reporting4Act! uses an Act! database view to identify these multi-select fields. Occasionally, this view becomes corrupted. The result of this is that the "multi" version of the field does not appear in Reporting4Act!'s field list. In this case, the problem can be resolved by recreating the Act! database views. An Act! SDK connection is required to identify these fields as well, so if the SDK connection has failed then the "multi" fields will not appear.
© Keystroke.ca, 2018 • Updated: 04/27/18
Comment or report problem with topic