Manipulate CRM ActivityParty Field Values

In most integration and migration projects, there may arise a scenario where the format of specific data values does not match the source and target instances. In these situations, we need to manipulate the input data, which can be challenging when working with the JSON format of CRM's ActivityParty fields.

In this blog post, we discuss one approach to change the input values of ActivityParty (partylist) fields to match the target instance's requirements using some of the components included in our SSIS Productivity Pack.

ActivityParty (PartyList) field format

When working with an ActivityParty (partylist) field, the output of the CRM Source Component will be in a JSON format (when using v5.0 or later version of our SSIS Integration Toolkit for Microsoft Dynamics 365). This output would allow a single cell value to consist of a list of parties. For each party record in the JSON structure, there will be a PartyId, which is a GUID, a Name, which is the party's name, and a Type, which indicates the party's entity type. The following is a sample of such output consisting of an account record and a systemuser record.

[
    {
        "PartyId":"c2cb1adc-cec4-e311-80e0-7845c4c5c272",
        "Name":"ABC Inc.",
        "Type":"account"
    },
    {
        "PartyId":"61f78159-c1ce-441e-9eb4-e4c84206d89f",
        "Name":"Joe Smith",
        "Type":"systemuser"
    },
]

Also, we can use this format to assign values to an ActivityParty field using our CRM Destination Component. In many integration scenarios, the GUID values of records do not match between the Source and Target instances, or the GUID values are not available in the Source input data. In this case, if the name matches the Source and Target instance as well as the target lookup entity, then we can map the previously mentioned JSON structure directly to the ActivityParty field in the CRM Destination Component and configure the Text Lookup feature on that field.

Challenges When Editing ActivityParty Fields

There are some scenarios where even the Name field doesn't match the Source and Target instances. Even the Text Lookup operation would fail as it would not be able to find an exact match in the target instance. In these situations, some string manipulation might be needed to change the format of the name field or change the target lookup entity, if required. For instance, consider the following example where we have the format of the fullname of the systemuser entity to be "Lastname, Firstname". However, the target CRM instance might have the fullname field in the format of "Firstname Lastname". In this case, we need to change the format of the Name field for the Text Lookup operation to succeed.

[ 
{              
"PartyId":"00000000-0000-0000-0000-000000000000", 
"Name": "LastName01, FirstName01", 
"Type":"systemuser" 
}, 
{ 
"PartyId":"00000000-0000-0000-0000-000000000000", 
"Name":"LastName02, FirstName02", 
"Type":"systemuser" 
}
]

This could be challenging as the JSON format could be fairly complex to edit, and an SSIS expression by itself wouldn't be enough in this case. For instance, there could be different parties in the same JSON array, and you would need to change the format for all of them or just for one target entity, while the array could have a variable number of party members. Therefore, a more reliable and flexible approach is required to manipulate the values of the incoming ActivityParty values.

Editing the JSON Values of the ActivityParty Field

Our approach, in this scenario, would include three main steps.

1. Parse the JSON structure into a tabular format work with the data values easily in the dataflow task.
2. Change the format of the Name field or to change the target entity depending on our use case.
3. Reconstruct the JSON structure to pass it to the CRM Destination component after the required modifications are completed.

In our example, we will use a Data Spawner to generate a test record that includes the ActivityParty JSON snippet we mentioned earlier, however, you can use any other Source component based on your integration scenario. It is important to note that we need to rely on a unique ID field in the source input data, which is going to be used to link the changed ActivityParty field back to the original input record.

The next step would be to use a Multicast component to take a second output of the input data and work on the JSON structure to change it into the desired format. Then, we use our JSON Extract component to parse the JSON structure into a tabular format. This way, we would have a record for each partylist member along with the upstream unique ID as shown below:

The following step is to use a Conditional Split component to change the format of the Name field for only the selected target entities we want to work with. In our current example, we are working with the systemuser entity, so that's what is going to be included in the expression in the Conditional Split component so that all other entities of partylist members can pass through unchanged. Then, we would use our Premium Derived Column component to change the format of the Name field, which is going to set the Firstname before the Lastname. The following expression is an example of how this can be done; however, further adjustments might be needed depending on your use case:

SubString( [Name], FindString( [Name], ", ", 1 )+2, Len( [Name]) ) +" " + SubString( [Name], 1, FindString( [Name], ", ", 1 )-1 )

At this point, we were able to change the format of the Name field successfully, and we can now reconstruct the JSON structure with the new values. To do so, we can use our JSON Merge component for this purpose. However, to able to link back each new ActivityParty field with its original input record, we will add the unique ID within the JSON structure itself. Then, on the output of the JSON Merage component, we would use another Premium Derived Column component to extract this ID field and completely remove it from the JSON structure to put it back in its standard form. Finally, we use a Premium Lookup component to link each new ActivityParty field to its input record from the Source component. So, the complete data flow design would be as follows:

By executing the package, we see that the Name format has been successfully changed, and the JSON structure has been put together in its original format:

Closing Notes

This is one of the approaches to change the values inside of an ActivityParty field for a given use case. However, the same approach can be used with any other scenario which requires the manipulation of partylist members. The change would be in the SSIS expression used in the Premium Derived Column to accommodate the different requirements. The sample SSIS package we used in this blog post is available for download here. The package was created under SSIS 2017.