The standard Microsoft™ Dynamics CRM integration with Acoustic Campaign only synchronizes contact and lead records. If additional record types or entities need to be added to Acoustic Campaign, they will be referred to as relational tables.
To help build an Acoustic Campaign relational table that mimics an MSCRM entity it is best to build a view for the entity, outlining what fields you would like to put into Acoustic Campaign, within MSCRM and export a sample of the data in excel csv format to import into Acoustic Campaign.
- In MSCRM, create an advanced find or personal view for an entity you want to add in Acoustic Campaign. The entity will become the Relationship Table. Select Edit Columns to customize the view. Make sure you add all the columns you would like to include. These will serve as the fields for the Relational Table.
Note: One of the fields/columns needs to be something that can relate to a field on the main Contact\Lead Database in Acoustic Campaign. For instance if you are adding Opportunities for Accounts as a relational table make sure the Contact/Lead database in Acoustic Campaign has an Account/Company/Customer Name field and the Opportunities relational Table has a similar Account/Customer/Account Name field that can be associated with each other in Acoustic Campaign.
- Save and Name the new View using Save As.
Note: Selecting Make this data available for re-importing by including required column heading ensures the CRM database field names and GUIDs are exported as well.
- After saving the file in the xls format, open it and save in the csv format.
- Open the newly created csv file in Excel. Clean up the headers to make them appropriate for importing into Acoustic Campaign as suggested by Acoustic Campaign support.
- Save the file.
- Go to Data > view data > Relational Tables and click on Import New. Name the table and save it in the shared folder.
- Update the Field Types as needed.
- After creating the table, relate it to the main contact/ lead database, Go to Data > View Data > Select the Acoustic Campaign database and click on Associate Table.
- Go to the Shared folder and select the relational table.
- Choose a field from the database and a field from the relational table. These chosen fields will connect two data sources.
- A green check mark confirms the mapping of the relationship.