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.

Note that Reporting4Act!'s special handling of multi-select fields makes use of the Act! SDK. If an SDK connection is not available, the special Multi fields will not be available in Reporting4Act!.

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/StatusCount
Friend1
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/StatusCount
Customer;Decision Maker;Friend1
Customer;Friend2
Employee;Friend1
Friend1
Friend;Customer1
Friend;Influencer;Prospect1
Friend;Prospect1
Friend;Reseller1
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/StatusCount
4
Consultant6
Consultants4
Customer54
Customer;Decision Maker;Friend1
Customer;Friend2
Customer;Influencer4
Customer;Vendor1
Doctor1
Employee10
Employee;Friend1
Friend1
Friend;Customer1
Friend;Influencer;Prospect1
Friend;Prospect1
Friend;Reseller1
Gate Keeper2
Influencer;Customer1
Influencer;Prospect7
Influencer;Reseller1
Influencer;Vendor1
Prospect86
Prospect;Vendor5
Vendor3
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
Consultant6
Consultants4
Customer64
Decision Maker1
Doctor1
Employee11
Friend9
Gate Keeper2
Influencer15
Prospect100
Reseller2
Vendor10
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, 2023 • Updated: 01/18/22
Comment or report problem with topic