Importing Appointments with Multiple Attendees

I have written a few blogs on tips for importing in the past (2011, versions 3.0 and 4.0, changing the lookup reference and a different approach to data enrichment). One issue I have always struggled with was importing activities. The biggest problem are the attendees. The template CRM provides does have the required and optional attendee columns but they can only handle one reference in them. In other words, if your appointment has more than one person attending, you cannot import the appointment without culling the invite list.

This is something of a drawback and one that has hit me squarely between the eyes in a recent project where appointments had up to 40 attendees. I have found a solution and, as if often my way, no code is needed. In fact, it is similar to my data enrichment approach from before.

Other ways to tackle the problem

There were a few ways I considered for dealing with this. The first was bringing the appointments into Outlook and then tracking them into CRM. Unfortunately the import tool for Outlook also dismisses the attendees if the import uses a CSV. Apparently the import tool for iCal format does bring in the attendees but all the online CSV to iCal convertors I found also dropped the attendees when converting to the iCal format.

The commercial solution, as recommended by the ever relaxed CRM MVP Joel Lindstrom, was the Kingswaysoft add-on to SSIS. We were using the Kingswaysoft add-on for the integration on the project, so it was an option, but to code the mappings was going to take longer than figuring out a way to do it via the import wizard. This was the fallback option though and an excellent one at that. Using the Kingswaysoft product for the integration literally cut our import mapping time to one third over coding SSIS directly, all for a license price equivalent to a day’s consulting, crazy good value.

My Trick: The Setup

It turns out you can get multiple attendees onto an appointment using the standard import wizard, connections and a workflow.

First, we need to set up a couple of connection roles: attending and attendee:

I made these as matching roles so I do not have to be too concerned about which is the ‘to’ and which is the ‘from’, which always trips me up.

Now we need a workflow which will convert this connection into an attendee. Here it is:

However, the real trick is in the Update step:

Rather than using the ‘Set by’ operator, I am using the ‘Increment by’ operator. This means the workflow will add the Contact to the existing list of Required Attendees, rather than resetting it.

The Importing

First we import our Appointments (without Attendees), ensuring we have a field we can use to uniquely identify the Appointment. Then we import our Contacts again ensuring we have a field to uniquely identify them.

Finally, we import the Connections between them. The template can be downloaded from CRM, from the Settings-System-Data Management area. You then populate it (using the unique values to link to the Contact and Appointment and maybe my ‘changing the lookup reference’ article to work out how to set the mapping. The spreadsheet will look something like this:

The Result

The result is when the Import Wizard adds the Connection, the workflow kicks in and updates the linked appointment, adding the Contact to the Required Attendees.

Conclusions

The inability to import appointments (or other activities) with multiple attendees has always limited the value of the Import Wizard. With this trick, a small amount of effort means we can use the Import Wizard for Activities without resorting to code or third party import tools.