Skip to main content

Creating Activity Records

To create activities such as phonecalls, appointments, tasks and emails in Dynamics you need to provide the GUID of the item you are adding and sync this to the corresponding entity. For example tasks will need to be synced to the Task entity, phone calls to the Phonecall entity, appointments to the Appointment entity and so on.

Below we will cover a few of the considerations for each activity type and how you might map the columns.

Requirements

Before getting started you need to ensure you have the following:

Considerations

If you are regularly updating activity records in Dynamics then we would recommend containing all of your projects within an Ouvvi project. This will enable you to run each project on a schedule and run them in sequence. You can additionally add a step to notify you that the project has completed and the details of what changed.

You can find more details on the project status report step in our documentation. Alternatively you could also use the Run Tool to schedule your projects.

If your source data is a selection of different activity types then you can filter the source records to only return the activity type you are trying to update. For example you could filter the source with the expression activitytype == "phonecall" to filter the results to return only phonecalls.

The below examples assume you have already connected to Dynamics and saved the connection to the connection library, and that you have your source connected to your source data.

Adding Phonecall Activities

To write phone call activities you must connect the target to the Phonecall entity and provide the data in the format Entity Name| GUID of the Item. Don't worry if your source data isn't currently in this format as we cover how to use Calculated Columns to present your data in the way Dynamics is expected. We do recommend adding a record manually in Dynamics so then you can preview the format Dynamics is expecting.

For phone call activities you need to supply the following fields at a minimum:

  • from - The user that made the call in the format systemuser|systemuserid
  • to - The account or contact that the call relates to in the format account|accountid or contact|contactid
  • subject - The subject of the call
  • activityid - A guid to uniquely identify each activity

You can then additionally add in other metadata such as a description, duration, date, ownerid, ownertype etc.as you need.

A sample of the data we are returning for this can be seen below:

Data Preview

Lookup IDs

You will need to lookup the account name in your Dynamics site to return the account id so that we can present the to field in the format expected in a few moments.

If your data is linking to a contact record rather than account then you can follow the same details as below but lookup the contactid in the Contact entity and return the id in the format contact|contactidGUID e.g. contact|14daefde-d3d1-ec11-a7b5-00224880a494.

To lookup the Account ID, drag and drop the account entity onto the source column that contains the account name, e.g. Account.

Drag and Drop Lookup Account

This will open the lookup configuration window where you will need to specify the column within the account entity to lookup the source values, in this case it's name, check the data type is correct and then click OK to save this and create the lookup.

Account Lookup Config

Now repeat this for the systemuser entity to lookup the user by fullname.

System User Lookup Config

Format the Data

We can then use calculated columns to return the entity name and GUID in the format we mentioned earlier entity|GUID.

To do this click onto the column button in the datasource toolbar.

Add Calculated Columns

Then enter in name for the column, make sure the data type is set appropriately, and enter in the function. You will need to do this for both the to and from fields, so that you end up with two calculated columns.

CONCAT("account|", STR(Lookup1_accountid))

Calculated Column format to field

Alternatively you can do the lookup in the function rather than creating it separately. This function would look something like this:

CONCAT("account|", STR(LOOKUPB("accountid", "account",WHEN("name", Account))))

Schema Mapping & Sync Results

Once they have been created we can then map these columns to the schema map and link them to their corresponding field in Dynamics. You can now add any additional columns you might need and make sure to set a unique key column that can be used to identify each phone call activity.

Schema Mapping

Click Run Compare to view the changes that need to be made, and check the preview to ensure the data looks as you expect. Here we have 5 phonecalls to add,

info

Please note that deletes show in the results but are disabled by default. To enable them please set EnableDeletes to True in the target connection properties.

Enable Deletes

Compare Results

Once you are ready click Synchronise and Start to begin the sync.

You can now save you project and move on to creating the projects to sync other activities.

Adding Appointment Activities

Adding appointments as activities to your Dynamics site is very similar to adding phonecalls, the main difference being you connect to the appointment entity as your target and there are different required fields.

At a minimum the columns you need are:

  • activityid - A guid to uniquely identify the activity.
  • organiser - The user the appointment is with in the format systemuser|systemuserid
  • requiredattendiees - The contact or account that the appointment is with in the format contact|contactid or account|accountid
  • scheduledstart
  • scheduledend

If you want to change the status of the appointment you can do so by adding statecode: 1 is completed, 2 is cancelled and 3 is scheduled.

A preview of the data we are using for this example can be seen below:

Appointment Source Data

Lookup IDs

We need to lookup the contactid and the systemuserid and then format the values.

First start with the lookups. Drag and drop the contact entity onto the contact column in the source, and then configure the lookup to target the contact fullname as this is the data we have in the source file.

Lookup Format - Contact

We can then repeat this for system user. Drag and drop the systemuser entity onto the organiser column and then configure the lookup to target the fullname of the user.

Lookup Format - System User

Format the Data

Now we can create the calculated columns to format the values. Lets start with the organiser. Create a calculated column and use the following expression to concatenate systemuser| with the systemuserid.

CONCAT("systemuser|",STR(Lookup2_systemuserid))

Calculated Column - Organiser Format

For the contact, create a calculated column and use the following expression to concatenate contact| with the contactid.

CONCAT("systemuser|",STR(Lookup1_contactid))

Calculated Column - Contact Format

Schema Mapping & Sync Results

You can now add these new column to the schema map alongside the other required fields and any other fields you want to include. Make sure the ActivityID is set as the key column, and your schema mapping should now look similar to this:

Appointments Schema Mapping

To finish simply compare, preview the results, and then synchronise the changes to import your appointment activities.

Appointment Compare Results

Adding Email Activities

To synchronise emails as activities in Dynamics you need to connect your target to the email entity and include a few more required columns. The example below uses a document to contain the message details rather than connecting the source to exchange.

The columns you need to include are:

  • activityid - A guid to identify each activity as unique
  • from - The system user who sent the email in the format systemuser|systemuserid
  • to - The contact that the email is being sent to in the format contact|contactid
  • subject - The email subject
  • description - The body of the email in either HTML or plain text
  • regardingobjecttype - The type of object the email relates to in this case a "contact"
  • regardingobjectid - The ID of the contact it relates to

By default the email will be set to draft status, however if you want to define this then you can do so by setting the statuscode: 1 is draft and 3 is sent.

Below is a preview of the data we are using in this example.

Data Preview

Lookup IDs

We need to start by looking up the contactid and the senderid and then format this into the format object|objectid.

To add the lookup to contact drag the contact entity from the connection library onto the contact column in the source.

Drag Drop Lookup Contact

Then configure the lookup so that the value is being looked up in the correct column. In this example as we have the full name we join the lookup on fullname.

Contact Lookup Config

To add the lookup to the system user, drag the systemuser entity from the connection library onto the sender column in the source.

Drag Drop Lookup System User

Then configure the lookup so that the value is being looked up in the correct column. In this example as we have the email of the sender we join the lookup on domainname.

System User Lookup Config

Format the Data

We now need to use the lookups to format the to and from columns. This can be done using calculated columns and the functions described below.

To create a column, click onto the column button in the datasource toolbar.

Add Calculated Columns

Then enter in a name and set the datatype as needed. To format the to column we can use the expression:

CONCAT("contact|",STR(Lookup1_contactid))

Calculated Column - To Formatting

Then repeat this for the from column using the expression:

CONCAT("systemuser|", STR(Lookup2_systemuserid))

Calculated Column - From Formatting

Schema Mapping & Sync Results

These columns can now be added to the schema map and linked to their corresponding column in Dynamics. Your schema map should look something similar to below with a key column selected (activityid).

You can get the contactid to map to the target regardingobjectid column from the lookup columns. Just expand the lookup columns and select the Lookup1_contactid column.

Schema Mapping - Emails

info

If you need to add multiple recipients you can do so by supplying them separated by semi colon e.g. contact|77962dc3-fcd5-ec11-a7b5-000d3abdf148;contact|969924e0-7c2c-4e85-be9d-000d3abdf148

You can now compare and sync as normal to import your email activities.

Email Sync Results

Don't forget to save your project so that you can use it again at a later date. If you upload it into Ouvvi or the Run Tool it can be scheduled to run on a regular basis.

If you have any questions please send us an email at support@simego.com.