SQL Recommended Synchronization

What information to integrate from source to destination

The next section gives guidelines on how to better synchronize data from ERP to HubSpot with some best practices. The names of the properties on both ERP and HubSpot are an example, we suggest you to use customized properties according to your specific needs.

Start by creating a new view with customers ERP data. Below is an example query to create the view with generic ERP_Field to be replaced with the specific ERP field.

USE [ERPBridge]
GO

CREATE view [dbo].[V_Deals] as 

select
	off.OfferNumber + ‘ - ’ + off.CustomerName as DealName
	,off.SalesAgent as SalesAgent
	,off.OfferNumber as OfferNumber
	,off.OfferDate as OfferDate
	,off.OfferRevisionNumber as OfferRevisionNumber
	,off.OfferFileURL as OfferFileURL
	,off.OfferAmount as OfferAmount
	,ord.OrderNumber as OrderNumber
	,ord.OrderFileURL as OrderFileURL
	,ord.OrderAmount as OrderAmount
	,off.CustomerERPCode as CustomerERPCode
	,off.Status as DealStatus
	,GETDATE() as LastSync
	
from
	ERP_Table_Offer off
	left join ERP_Table_Order ord on off.OfferNumber=ord.OfferNumber 
	
where
	LastModifiedDate=GETDATE()  	
	-- for incremental synchronization once/twice a day
	

GO

The result is the creation of a SQL table that collects data from the query.

ERP Information

Property Name on HubSpot	Details	Field type on HubSpot
Deal Name	Property provided by HubSpot	Single-line text
SalesAgent	Custom Property - To use for associate a Deal Owner with a Workflow	Single-line text
Offer Number		Single-line text
Offer Date	Custom Property	Date picker
Offer Revision Number	Custom Property	Number
Offer file URL	Custom Property - File PDF to insert in the Attachments section	File
Offer Amount	Offer amount corresponding to the amount of the open deal	Number - Currency
Order Number	Custom Property	Single-line text
Order file URL	Custom Property - File PDF to insert in the Attachments section	File
Order Amount	Order amount corresponding to the amount of the closed deal	Number - Currency
Customer ERP Code	Custom property created to match HubSpot and ERP records, copy the ID code of the record from the ERP	Single-line text
Deal status	To move the deal to an exact stage via workflow	Dropdown select
Last Sync	For keeping track of the last sync made with the ERP	Date picker

If you decide not to consider orders in your integration, we suggest you determine the deal stage from the offer status.

To optimize the integration, prepare a custom view in SQL with the data you want to integrate on HubSpot. To this view, you can add or remove fields at any time, perform format conversions (casts) and correctly manage the entries of the properties checkboxes, multiple checkboxes, dropdown select, radio select and HubSpot user.

When creating the properties on HubSpot that will be used in ERP Bridge, carefully consider the type of data you are bringing to the CRM. If the options are few, select a choosing options property type, if the options are many and updated frequently, select a single-line text.

For property type choosing options is essential to understand how often the property will be updated, because any unalignment between ERP fields and HubSpot will result in an error. If the property updates on a regular basis the advice is to select single-line text.