Saturday, July 13, 2013

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:

image

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.

image

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

image

However, the real trick is in the Update step:

image

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:

image

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.

image

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.

13 comments:

Unknown said...

Hi Leon,

First of all its a great post and thankyou for sharing it . I want to import Recurring Appointments with a specified recurrence and on the CSV there is no place to specify the Recurring sequance . Do we have to follow a similar process as you have suggested in this post ?

Leon Tribe said...

No worries, glad you liked it. Just having a tinker with workflows, it seems you cannot update a recurring appointment with workflows, which is a shame because it means you probably cannot use the same trick.

Unknown said...

Hi Leon,

Thanks for the reply . Lets say we leave the Multiple Attendees aside for a bit . How about importing Recurring Appointments to CRM via a CSV is it possible . I am trying to do it and it fails saying an invalid Recurring Frequency.

Leon Tribe said...

I understand. I cannot see anywhere on the appointment fields when using an workflow update step and workflows do not allow for the updating of a recurring appointment. So it seems the recurring frequency is inaccessible via workflow which means we cannot update post-import without resorting to a plugin.

Jukka Niiranen said...

Another great import trick! I didn't know that you could actually append the activity party fields with workflow by using the "increment by" step.

Connections really are handy for mapping these more complex relationships between records being imported. Here's an example of how I recently used them in importing and mapping primary contact data to accounts: http://niiranen.eu/crm/2013/07/how-to-import-primary-contacts/

Scott Jackson UK said...

Thanks Leon, That is such a great trick, I hadn't thought of importing connections to append items onto a meeting and it doesnn't even need a developer so I can get this deployed far quicker

Anonymous said...

Hi Leon,

Thanks for this great post.
I have recreated this solution in a CRM 2013 online environment. However I am running into some strange results. The workflow that updates the required (or optional) attendees field value does work just fine when I do separate imports of single records. However, as soon as I import a .csv with multiple records, only one record gets added to the requires attendees. It seems as if there is a timing(?) issue. As I said, it is working when I import one record at a time. The attendee is added to the list of attendees (not substituted).
Now I am wondering if there is something I am doing wrong, or if it's CRM 2013 that has an issue with the increase by with multiple records at a time (and if there is a work around).
Can you please confirm?

FYI: my .csv file has 5 records all related to 1 appointment.

Kind regards,
Bertil

Anonymous said...

I would like to mention COZYROC SSIS+ library does include Dynamics CRM adapters, supporting all CRM deployment types. The price is better compared to Kingswaysoft and COZYROC is actually the original pioneer.

Leon Tribe said...

Hi Bertil,

Sorry for the delayed response. I am not sure why you're seeing this behaviour. If it is 2013 you might want to try changing the workflow to a synchronous one and see how this works out or vice versa.

Gustaf Westerlund said...

Creative solution and great post!

Tofumon said...

THANK you for this guide. This helped me a bunch.

Unknown said...

Thank You Leon for the great information!

I would like to know the process I should take to import attendees to an event?

Leon Tribe said...

Hi Natalie,

It depends how you are storing them. Are you looking to create Campaign Responses?

If so, if they are not available as part of the Create Record step, it might not be possible.

Leon