Creating a Vendor Data Integration Project between Sales and Finance
Creating a Vendor Data Integration Project between Sales and Finance Murray Fife, July 2017
Page 1
Creating a Vendor Data Integration Project between Sales and Finance
Creating a Vendor Data Integration Project between Sales and Finance The Data Integration tool for Dynamics 365 is the default tool that we can use to synchronize data between Dynamics 365 and also other services like SalesForce.com and even generic SQL databases. It comes with a whole slew of templates that we can use to set up the standard integrations, but it also is designed to allow us to create our very own integration projects as well. In this example we will show how we can create new integration project that allows us to transfer Vendor accounts from Sales over to Finance and Operations, so that we can use Sales for the relationship and business process management, and then have all that information available within a mirror record in the Vendors tables.
Topics Covered •
Creating a new Data Integration Project
•
Synchronizing Vendors
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project To do this, we will just need to create a new Data Integration project that links the Accounts records within Sales over to the Vendors table within Finance and Operations. This may sound hard, but it’s not really.
Here is what you need to do.
How to do it… Start off by opening up the PowerApps Admin center to see all of the Integration Projects that have been set up between Dynamics 365 Sales and Finance. All we need to do here is click on the + New project button.
This will take us to the Organizations page, Click on the Organizations dropdown list and select the Organizations that we want to move the data from and to. After you have done that, click on the Next button.
This will open up the New data integration project page where we will be able to see all of the template projects that are available for us.
Finally we will see the Privacy notice for the data.
Scroll down to the bottom of the page and you will see that there are some generic projects that we can use to move the data between Sales and Finance and Operations.
This will take us to the blank project page for our Vendors integration.
Select the Sales to Fin and Ops project. Then scroll back to the top of the page and give the project a Name. In this case we will set the Name to “Vendors 8.1 (1.0.0.1)”. After we have done that, click on the Next button. This will take us to the Connections page of the setup wizard. Select the Connection that you want to use for the integration. After you have done that, click on the Next button.
All we need to do here is click on the Create button.
We can start building the integration by adding a Task to the project. To do this, just click on the Add task button in the header. This will open up a dialog box where we can start defining the task. Start off by giving your Task a Task name. Here we set the Task name to “Vendors”. Next, we will want to select the entity within Sales that we want to select our data from. In this case we will be pulling the vendor information from the Accounts table in Sales.
Creating a Vendor Data Integration Project between Sales and Finance Then we will want to select the destination entity that we want to put the data into within Finance & Operations. In this case we will want to select the Vendors entity as the destination location. Finally we will want to select the Organization that we want to use for the company mapping. After we have done that, we can click on the Create button. This will create a new task for us that will allow us to move the data from the Sales.accounts entity into the Fin and Ops.Vendors entity. We need to make a few changes though before we are done, so we will click on the task to see the task details. This will open up the task details where we will be able to see the fields that are mapped. By default it adds the creditlimits field as part of the mapping. Click on the … button and then select the Delete option because we don’t need this field for the Vendors. Now we will want to start adding in the fields that we want to map with the integration. We will start by selecting the first Source field by clicking on the dropdown list and selecting the accountnumber field from Sales. This will add the new line for us. Now we want to select the target field that we want the accountnuimber to be deposited. Filter down the list of fields until you can see the VENDORACCOUNTNUMBER field and select it. Congratulation. You have just set up your first field mapping. Next we will click on the Source field dropdown list and then select the name field. And we will want to map this to the VENDORNAME field within Finance and Operations.
Now we have the base data for our mapping. But we are not done quite yet. We will want to change the query that is being made to Sales to filter out some of the data and also add some additional data to the query that we will use later on. To do this we will want to click on the Advanced Query and Filtering button. This will open the Advanced Query and Filtering form where we can see some sample data that is being returned from the entity and also see that we can further refine the query. We will start off by scrolling over to the right until we can find the customertypecode column. This column is used to identify the type of account this is, and we know that anything that has a customertypecode of “11” is marked as a vendor. So we will want to filter out the data just to those accounts so that all of the customers are not copied over during the sync. To do this we will click on the filter dropdown button and we will see the different filtering options. We will change the selection just to select the accounts with a customertypecode of “11” and then click OK. That will filter out the accounts and just return back the ones that are marked as vendors. Before we finish with the query, we will want to add a couple of additional columns that we will use to make the mapping go a little smoother. To do this we will click on the Add column menu item and then select the Add conditional column option. This will open the Add conditional column dialog box. Start of by giving the new column a name. Here we will set the New column name to “vendorgroupid”.
Creating a Vendor Data Integration Project between Sales and Finance Next we will want to add a condition for the column value. Set the condition field to “customertypecode” and the condition value to “11”.
Now we have another custom field that we can use for Party Type mapping.
And then set the resulting value to “10” which is the general vendor group.
If a warning is displayed that says that we want to leave the page, just click on the Stay on this page button.
Also set the Otherwise value to “10” as well.
When we return to the project mapping form, click on the Save button to save the updates that we have done.
Then click on the OK button. This will add a new column for us with the default Vendor Group ID of “10”. Click on the Add column menu item and then select the Add conditional column option again so that we can add one more default field. This will open up the Add conditional column form again. Set the New column name to “dirpartytype”. Again, we will want to add a condition for the column value. Set the condition field to “customertypecode” and the condition value to “11”. For this field set the resulting field to “Organization”. And set the Otherwise result to be “Organization” as well. After that is done, we can click on the OK button.
All we need to do is click OK.
Now we will want to add the additional field mappings to the project. Start off by selecting the vendorgroupid field for the source field. And then select the VENDORGROUPID for the destination field. We will add one more field map and select the dirpartytype custom field that we created earlier. And then map that to the VENDORPARTYTYPE field in Finance and Operations. Now we can just click on the Save button again to save the changes. After we have done that we can click on the back arrow. This will return us back to the project tasks form.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Start off by opening up the PowerApps Admin center to see all of the Integration Projects that have been set up between Dynamics 365 Sales and Finance. All we need to do here is click on the + New project button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will open up the New data integration project page where we will be able to see all of the template projects that are available for us.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Scroll down to the bottom of the page and you will see that there are some generic projects that we can use to move the data between Sales and Finance and Operations. Select the Sales to Fin and Ops project.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
Then scroll back to the top of the page and give the project a Name. In this case we will set the Name to “Vendors 8.1 (1.0.0.1)”. After we have done that, click on the Next button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will take us to the Connections page of the setup wizard.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Select the Connection that you want to use for the integration. After you have done that, click on the Next button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will take us to the Organizations page,
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Click on the Organizations dropdown list and select the Organizations that we want to move the data from and to. After you have done that, click on the Next button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Finally we will see the Privacy notice for the data. All we need to do here is click on the Create button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will take us to the blank project page for our Vendors integration. We can start building the integration by adding a Task to the project. To do this, just click on the Add task button in the header.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will open up a dialog box where we can start defining the task.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
Start off by giving your Task a Task name. Here we set the Task name to “Vendors”.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Next, we will want to select the entity within Sales that we want to select our data from. In this case we will be pulling the vendor information from the Accounts table in Sales.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Then we will want to select the destination entity that we want to put the data into within Finance & Operations. In this case we will want to select the Vendors entity as the destination location.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Finally we will want to select the Organization that we want to use for the company mapping. After we have done that, we can click on the Create button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will create a new task for us that will allow us to move the data from the Sales.accounts entity into the Fin and Ops.Vendors entity. We need to make a few changes though before we are done, so we will click on the task to see the task details.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will open up the task details where we will be able to see the fields that are mapped.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
By default it adds the creditlimits field as part of the mapping. Click on the … button and then select the Delete option because we don’t need this field for the Vendors.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Now we will want to start adding in the fields that we want to map with the integration. We will start by selecting the first Source field by clicking on the dropdown list and selecting the accountnumber field from Sales.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will add the new line for us.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Now we want to select the target field that we want the accountnuimber to be deposited.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
Filter down the list of fields until you can see the VENDORACCOUNTNUMBER field and select it.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Congratulation.
You have just set up your first field mapping.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Next we will click on the Source field dropdown list and then select the name field.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
And we will want to map this to the VENDORNAME field within Finance and Operations.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Now we have the base data for our mapping. But we are not done quite yet. We will want to change the query that is being made to Sales to filter out some of the data and also add some additional data to the query that we will use later on. To do this we will want to click on the Advanced Query and Filtering button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will open the Advanced Query and Filtering form where we can see some sample data that is being returned from the entity and also see that we can further refine the query.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
We will start off by scrolling over to the right until we can find the customertypecode column. This column is used to identify the type of account this is, and we know that anything that has a customertypecode of “11” is marked as a vendor.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
So we will want to filter out the data just to those accounts so that all of the customers are not copied over during the sync. To do this we will click on the filter dropdown button and we will see the different filtering options.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
We will change the selection just to select the accounts with a customertypecode of “11” and then click OK.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
That will filter out the accounts and just return back the ones that are marked as vendors.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Before we finish with the query, we will want to add a couple of additional columns that we will use to make the mapping go a little smoother. To do this we will click on the Add column menu item and then select the Add conditional column option.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will open the Add conditional column dialog box.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
Start of by giving the new column a name. Here we will set the New column name to “vendorgroupid”.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
Next we will want to add a condition for the column value. to “customertypecode” and the condition value to “11”.
Set the condition field
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
And then set the resulting value to “10” which is the general vendor group.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
Also set the Otherwise value to “10” as well. Then click on the OK button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
This will add a new column for us with the default Vendor Group ID of “10”.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Click on the Add column menu item and then select the Add conditional column option again so that we can add one more default field.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will open up the Add conditional column form again.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
Set the New column name to “dirpartytype”.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
Again, we will want to add a condition for the column value. field to “customertypecode” and the condition value to “11”.
Set the condition
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
For this field set the resulting field to “Organization”.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
And set the Otherwise result to be “Organization” as well. After that is done, we can click on the OK button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Now we have another custom field that we can use for Party Type mapping. All we need to do is click OK.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
If a warning is displayed that says that we want to leave the page, just click on the Stay on this page button.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
When we return to the project mapping form, click on the Save button to save the updates that we have done.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Now we will want to add the additional field mappings to the project. Start off by selecting the vendorgroupid field for the source field.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
And then select the VENDORGROUPID for the destination field.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
We will add one more field map and select the dirpartytype custom field that we created earlier.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it…
And then map that to the VENDORPARTYTYPE field in Finance and Operations.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
Now we can just click on the Save button again to save the changes.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
After we have done that we can click on the back arrow.
Creating a Vendor Data Integration Project between Sales and Finance
Creating a new Data Integration Project How to do it‌
This will return us back to the project tasks form.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors Now that we have set up our integration project, let’s see it in action by creating a new vendor record within Dynamics Sales and see it move over to Finance and Operations.
How it works‌ To see this in action we just need to go into Sales and look at the Accounts form. Here we have selected the Vendors view that only shows us the accounts that have been marked as Vendors.
To show that we have nothing up our sleeves here are the vendors that are within Finance and Operations.
To add a new account to the vendors list, select the All accounts system view. This will let us see all of the accounts that we have in the system.
This will start a process of running the project synchronization and within a minute or so we will see that it completes successfully with a number of Upserts into the destination entity.
If we drill into the account that we want to set up as a vendor then we will see that there is a Relationship type field on the account.
When we return back to the Vendors within Finance and Operations we will see that we now have a new Vendor record.
If we select the dropdown list for the Relationship type we will be able to select the Vendor option.
If we drill into the record we will see that it has some base data loaded for us from Sales.
Now our vendor is ready to be synchronized.
To manually synchronize the records, just click on the Run project button.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
To see this in action we just need to go into Sales and look at the Accounts form. Here we have selected the Vendors view that only shows us the accounts that have been marked as Vendors.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
To add a new account to the vendors list, select the All accounts system view.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
This will let us see all of the accounts that we have in the system.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
If we drill into the account that we want to set up as a vendor then we will see that there is a Relationship type field on the account.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌ If we select the dropdown list for the Relationship type we will be able to select the Vendor option. Now our vendor is ready to be synchronized.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
To show that we have nothing up our sleeves here are the vendors that are within Finance and Operations.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
To manually synchronize the records, just click on the Run project button.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
This will start a process of running the project synchronization and within a minute or so we will see that it completes successfully with a number of Upserts into the destination entity.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
When we return back to the Vendors within Finance and Operations we will see that we now have a new Vendor record.
Creating a Vendor Data Integration Project between Sales and Finance
Synchronizing Vendors How it works‌
If we drill into the record we will see that it has some base data loaded for us from Sales.
Creating a Vendor Data Integration Project between Sales and Finance
Conclusion How easy was that? We just created a completely new integration project that moved the data from Sales over to Finance and Operations. As well, we only moved over the data that we wanted to select by using the Advanced Query and Filter feature within the Data Integration. We don’t need to stop here – we can add some additional fields to the mapping and also create entirely new mappings for any of the other entities within Sales and Finance and Operations. How cool is that?