# How-To ERP Bridge

Check out the most useful and popular topics to get you started with ERP Bridge.

The following sections provide information about which data import from your ERP and how to use them with ERP Bridge. All of this sections are thought as insights to exploit the software 100%: for example you will see a standard integration that we recommend tu use with best practice cases as creator.

[For all technical information, please refer to the software documentation section.](https://docs.erpbridge.io/)

After completing the software installation and configuration, it's time to set up all synchronization logic, called Operation, from the software homepage.

{% hint style="info" %}
Our advice is to follow this synchronization logic:

* [Companies](/readme/companies)
* [Deals](/readme/deal)
* Contacts
* Products
* Line Items
* Tickets
* CustomObject
  {% endhint %}


# Companies

How to integrate companies data from ERP Bridge

Let’s see how to set a basis configuration on the entity named Companies. The names of the properties on both ERP and HubSpot are a suggestion, we suggest to customized properties according to your specific needs.

**Sections:**

* [Synchronization from ERP to HubSpot](/readme/companies/synchronization-from-erp-to-hubspot)
* [Synchronization from HubSpot to ERP](/readme/companies/synchronization-from-hubspot-to-erp/recommended-synchronization-sql-from-hubspot-to-database)


# Synchronization from ERP to HubSpot

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.

**Sections:**

* [SQL Recommended Synchronization](/readme/companies/synchronization-from-erp-to-hubspot/sql-recommended-synchronization)
* [Operation Configuration in ERP Bridge](/readme/companies/synchronization-from-erp-to-hubspot/operation-configuration-in-erp-bridge)
* [Advices and Best Practices](/readme/companies/synchronization-from-erp-to-hubspot/advices-and-best-practices)


# 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](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-views?view=sql-server-ver16) 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.

```plsql
USE [ERPBridge]
GO

CREATE view [dbo].[V_Companies] as 

select
	ERP_Code as ERPCode
	,ERP_Field as CompanyName
	,ERP_Field as StreetAddress
	,ERP_Field as City
	,ERP_Field as ZIP
	,ERP_Field as Country
	,ERP_Field as Vat
	,ERP_Field as FiscalCode
	,ERP_Field as SalesAgent
	,ERP_Field as Email
	,ERP_Field as CompanyDomain
	,ERP_Field as PEC
	,ERP_Field as Phone
	,ERP_Field as PriceList
	,ERP_Field as Discount
	,ERP_Field as PaymentTerms
	,ERP_Field as CustomerCategory
	,ERP_Field as LastOrder
	,ERP_Field as OrderedCurrentYear
	,ERP_Field as Ordered_year_1
	,ERP_Field as Ordered_year_2
	,ERP_Field as RevenueCurrentYear
	,ERP_Field as Revenue_year_1
	,ERP_Field as Revenue_year_2
	,ERP_Field as Unpaid
	,GETDATE() as LastSync
	
from
	ERPCustomerTable

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.

<figure><img src="/files/d4fIKieqlaDjB9uXmUIZ" alt="" width="351"><figcaption></figcaption></figure>

### General Information

<table><thead><tr><th width="213">About this company</th><th width="298">Details</th><th>Field type on HubSpot</th></tr></thead><tbody><tr><td>ERPCode</td><td>Custom field created to match HubSpot and ERP records, captures the ID code of the record from the ERP</td><td>Single-line text</td></tr><tr><td>CompanyName</td><td></td><td>Single-line text</td></tr><tr><td>CompanyDomain</td><td></td><td>Single-line text</td></tr><tr><td>VAT / Partita IVA</td><td></td><td>Single-line text</td></tr><tr><td>FiscalCode</td><td></td><td>Single-line text</td></tr><tr><td>SalesAgent</td><td>The reference agent for that customer, useful to associate a company owner via Workflow</td><td>Single-line text</td></tr><tr><td>Email</td><td></td><td>Single-line text</td></tr><tr><td>PEC</td><td></td><td>Single-line text</td></tr><tr><td>Phone</td><td></td><td>Phone number</td></tr></tbody></table>

### Geographical Information

<table><thead><tr><th width="515">Geographical Information</th><th>Field type on HubSpot</th></tr></thead><tbody><tr><td>StreetAddress</td><td>Single-line text</td></tr><tr><td>City</td><td>Single-line text</td></tr><tr><td>ZIP</td><td>Single-line text</td></tr><tr><td>Country</td><td>Dropdown select</td></tr></tbody></table>

### ERP Information

<table><thead><tr><th width="241">HubSpot Property Name</th><th width="279">Details</th><th>Field type on HubSpot</th></tr></thead><tbody><tr><td>PriceList</td><td>The price list information we want to transfer to HubSpot</td><td>Dropdown select</td></tr><tr><td>Discount</td><td></td><td>Dropdown select</td></tr><tr><td>PaymentsTerms</td><td></td><td>Dropdown select</td></tr><tr><td>CustomerCategory</td><td>Useful for profiling the company as the ERP items</td><td>Dropdown select</td></tr><tr><td>LastOrder</td><td>To be used as trigger in Workflows to notify Sales Agent about lost customers</td><td>Date picker</td></tr><tr><td>OrderedCurrentYear</td><td>Total money raised in the current year - Still to be paid</td><td>Number - Currency</td></tr><tr><td>Ordered_year_1</td><td>Total money raised last year - Could be used to compare the orders amount in different years</td><td>Number - Currency</td></tr><tr><td>Ordered_year_2</td><td>Total money raised two years ago - Could be used to compare the orders amount in different years</td><td>Number - Currency</td></tr><tr><td>RevenueCurrentYear</td><td>Actual Client Revenue</td><td>Number - Currency</td></tr><tr><td>Revenue_year_1</td><td>Total Revenue last year - Could be used to compare the revenue amount in different years</td><td>Number - Currency</td></tr><tr><td>Revenue_year_2</td><td>Total Revenue two years ago -<br>Could be used to compare the revenue amount in different years</td><td>Number - Currency</td></tr><tr><td>Unpaid</td><td>Amount not paid by the customer resulting from the due date of a payment associated with an invoice - The data can inform about the amount not paid by the customer and can block new orders or activate the credit recovery process</td><td>Number - Currency</td></tr><tr><td>LastSync</td><td>For keeping track of the last sync made with the ERP</td><td>Date picker</td></tr></tbody></table>

{% hint style="info" %}
To optimize the integration, prepare a custom view in SQL with the data you want to integrate on HubSpot Companies. 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.
{% endhint %}

{% hint style="warning" %}
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.
{% endhint %}


# Operation Configuration in ERP Bridge

[Please refer to this section](https://docs.erpbridge.io/the-operation) for technical details on how to configure ERP Bridge.

## Database Mapping

In the Database Mapping section you decide which fields to connect from the SQL view to HubSpot.

On the left-hand column select the data from SQL that you want to synchronize and link it with the related property on HubSpot. For more information about which properties to synchronize, please refer to the [synchronization section](/readme/companies/synchronization-from-erp-to-hubspot/sql-recommended-synchronization).

<figure><img src="/files/qdDjJBw5lAFRMl2t9OSV" alt=""><figcaption><p>On the left-hand column select the data from SQL, on the right-hand column select the properties on HubSpot</p></figcaption></figure>

{% hint style="info" %}
We recommend to create custom section for the new properties:

* Create a property group called ERP Information for all custom company properties
* Create a custom section called ERP Information on the left sidebar of the companies record, where insert all the created custom properties
  {% endhint %}

## Decision rules

The following section is Decisions rules, where you set all the actions that the agent will perform once the synchronization will be on. For general guidelines, please refer to the [specific section](https://docs.erpbridge.io/the-operation/3-decision-rules).

<figure><img src="/files/nbOEkV56yGumwzWX2v9U" alt=""><figcaption></figcaption></figure>

If you set ERP Code as primary key:

* Insert =0 in the first line means that if the ERP code does not exist, the agent will update the record,
* With several records with the same ERP code (=1), the agent will update the record,
* if there are several identical records (>1), the agent will skip the record.

{% hint style="danger" %}
It's fundamental to choose a field as 'primary key' that will always be uniquely populated, otherwise records will be duplicated in the next synchronization.
{% endhint %}

## Associations

In the [Associations section](https://docs.erpbridge.io/the-operation/4-db-greater-than-hs-associations) for the Companies object, the advice is to leave the fields empty and click on Done.

<figure><img src="/files/aG81OK5IUDSupM68i0FI" alt=""><figcaption></figcaption></figure>

Now the new operation Company will appear on the Homepage, to activate the synchronization make sure the button is on.

<figure><img src="/files/KgLB82peYKax1Bou3Sai" alt="" width="375"><figcaption></figcaption></figure>


# Advices and Best Practices

How to use imported data - recommendations for use

Now that the data are synchronized on HubSpot, you can use them inside workflows, views or lists.

Please find below our tips and advices to use ERP Bridge to the fullest potential:

* [Sending internal communication to Company Owner in case of unpaid situation](#sending-internal-communication-to-company-owner-in-case-of-unpaid-situation)
* [Assign a contact owner from an ERP field](#assign-a-contact-owner-from-an-erp-field)
* [How to use data in report](#how-to-use-data-in-report)
* [Create lists to manage sleeping customers / active customers](#create-lists-to-manage-sleeping-customers-active-customers)

## Sending internal communication to Company Owner in case of unpaid situation

Could be interesting build an automation to monitor the unpaid clients and notify in real time the company owner of that customer.

<figure><img src="/files/YB01Nw23rdkbKNpEAdO1" alt=""><figcaption><p>Workflow example - How to build an automation to notify the company owner for changes in important property</p></figcaption></figure>

As you can see from the image above, the automation shows as enrollment trigger every changes on the property "Unpaid" that is syncronized from ERP Bridge, and every time it will send automatically an internal email notification to the company owner of that record.

<figure><img src="/files/9ALiq42pOyntZgmOkCIB" alt=""><figcaption></figcaption></figure>

The company owner will receive an email showing the customers detail:

* Company Name
* Email
* Unpaid Amount

## Assign a contact owner from an ERP field

One of the tips we propose is to create a workflow that automatically attributes the agent - field imported from the ERP in HubSpot as single-line text, to the corresponding user on HubSpot. In this case as an input trigger of the workflow you will have the property 'Sales Agent', a property integrated from the ERP. If it is populated and the company owner is unknown, the company will be assigned to the HubSpot user set from the Workflow.

<figure><img src="/files/dtqf4xuujTQiKLyJ5a8E" alt=""><figcaption></figcaption></figure>

Then following the workflow:

* Records that correspond to Veronica Bresciani on ERP, on HubSpot will be assigned to Veronica Bresciani,
* Records matching Gianluca Giorgini on ERP, on HubSpot will be assigned to Gianluca Giorgini,
* Records matching Francesco Fabbri on ERP, on HubSpot will be assigned to Francesco Fabbri,
* For all the anomalies, an email alert will be sent to a specific user admin.

<figure><img src="/files/FrF3jTJ4rfwCMquTsTwf" alt="" width="355"><figcaption><p>Result of the Workflow, just populated Sales Agent field from ERP Bridge triggers automatism on Company Owner property</p></figcaption></figure>

<figure><img src="/files/BUb9lycw9a6t8VewkQKz" alt=""><figcaption><p>Latest Sync from ERP Bridge with automatic association of Company Owner</p></figcaption></figure>

## How to use data in report

Thanks to HubSpot data integration you can now create custom dashboard and reports. For example, we recommend analysis with data of current and previous orders for each company.

<figure><img src="/files/N87b4RRPu4ub5NpWyF2D" alt=""><figcaption><p>Total Orders by Company of the current year, last year and two year ago</p></figcaption></figure>

With these data we can create different type of graph to show ordered amount as the comparison of the best customers in different years.

<figure><img src="/files/pJXA2yKcfLNyEugdc7jn" alt=""><figcaption><p>Comparison of ordered amount in different years</p></figcaption></figure>

We recommend analysis also with data of current and previous revenue for each company.

<figure><img src="/files/nBw3NF3AEab3xPp4dzWm" alt=""><figcaption><p>Comparison of revenue amount in different years</p></figcaption></figure>

It is also possible to build a report with the analysis of the unpaid situation divided by company.

<figure><img src="/files/Klchz0QCbRt0ow7woykw" alt=""><figcaption><p>Most insolvent companies</p></figcaption></figure>

You can choose other properties to combine, for example Sales Agent and unsolved clients.

<figure><img src="/files/KFTFyqcujG1U34vFRuQB" alt=""><figcaption></figcaption></figure>

## Create lists to manage sleeping customers / active customers

For your company could be interesting divide the customer base into active and sleeping clients, to have a clear view about which companies are regular buyer or which you are loosing.

To do this, you can create active lists on HubSpot.

First, in the main menù select Lists and create a folder named ERP Bridge, where insert all the lists related to the integration.

<figure><img src="/files/FqKHWa68cRKnRZicsOIY" alt="" width="165"><figcaption></figcaption></figure>

Then create a list of sleeping customers, insert as filter the property "last order" to manage who is entering or not in the selection. In our example we decide to insert all the companies with an order older than 180 days. For active customers the filter would be the contrary, less than 180 days.

<figure><img src="/files/DATKbkrFqGQ64jEcvqCq" alt="" width="352"><figcaption></figcaption></figure>


# Synchronization from HubSpot to ERP

In the next section are detailed company data guidelines from hubspot to ERP with some case studies on how to use synchronized data. Property names on both ERP and HubSpot are indicative, you can also add custom properties based on your specific needs.

**Arguments**:

* [Recommended Synchronization SQL](/readme/companies/synchronization-from-hubspot-to-erp/recommended-synchronization-sql-from-hubspot-to-database)
* [Operation Synchronization ERP Bridge](/readme/companies/synchronization-from-hubspot-to-erp/operation-synchronization-erp-bridge)
* [Best Practices](/readme/companies/synchronization-from-hubspot-to-erp/advices-and-best-practices)


# Recommended Synchronization SQL - From HubSpot to Database

What information to integrate from source to destination

This section gives guidelines on how to better synchronized data from HubSpot to ERP with some best practices. The names of the properties on both ERP and HubSpot are a suggestion, we suggest to customized properties according to your specific needs.

Create a [new table](https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-tables-database-engine?view=sql-server-ver16) for extract companies HubSpot data to be passed to ERP, below an example of query for create the table.

```sql
USE [ERPBridge]
GO

/****** Object:  Table [dbo].[T_Companies] ******/

CREATE TABLE [dbo].[T_Companies](
	[ERPCode] [nvarchar](max) NULL,
	[CompanyName] [nvarchar](max) NULL,
	[StreetAddress] [nvarchar](max) NULL,
	[City] [nvarchar](max) NULL,
	[ZIP] [nvarchar](max) NULL,
	[Country] [nvarchar](max) NULL,
	[VAT] [nvarchar](max) NULL,
	[FiscalCode] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
	[PEC] [nvarchar](max) NULL,
	[Phone] [nvarchar](max) NULL,
	[PriceList] [nvarchar](max) NULL,
	[Discount] [nvarchar](max) NULL,
	[PaymentTerms] [nvarchar](max) NULL,
	[LastModifiedDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
```

<figure><img src="/files/qWI9CPbp42PdhcWRpGj8" alt="" width="290"><figcaption></figcaption></figure>

{% hint style="info" %}
To optimize the integration, prepare a custom view in SQL with the data you want to integrate on HubSpot Companies. 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.
{% endhint %}

### General Information

<table><thead><tr><th width="213">About this company</th><th width="298">Details</th><th>Field type on HubSpot</th></tr></thead><tbody><tr><td>ERPCode</td><td>Custom field created to match HubSpot and ERP records, captures the ID code of the record from the ERP</td><td>Single-line text</td></tr><tr><td>CompanyName</td><td></td><td>Single-line text</td></tr><tr><td>VAT</td><td></td><td>Single-line text</td></tr><tr><td>FiscalCode</td><td></td><td>Single-line text</td></tr><tr><td>Email</td><td></td><td>Single-line text</td></tr><tr><td>PEC</td><td></td><td>Single-line text</td></tr><tr><td>Phone</td><td></td><td>Phone number</td></tr></tbody></table>

### Geographical Information

<table><thead><tr><th width="515">Geographical Information</th><th>Field type on HubSpot</th></tr></thead><tbody><tr><td>StreetAddress</td><td>Single-line text</td></tr><tr><td>City</td><td>Single-line text</td></tr><tr><td>ZIP</td><td>Single-line text</td></tr><tr><td>Country</td><td>Dropdown select</td></tr></tbody></table>

### ERP Information

<table><thead><tr><th width="241">HubSpot Property Name</th><th width="279">Details</th><th>Field type on HubSpot</th></tr></thead><tbody><tr><td>PriceList</td><td>The price list information we want to transfer to HubSpot</td><td>Dropdown select</td></tr><tr><td>Discount</td><td></td><td>Dropdown select</td></tr><tr><td>PaymentsTerms</td><td></td><td>Dropdown select</td></tr></tbody></table>


# Operation Synchronization ERP Bridge

Please [refer to this section](https://docs.erpbridge.io/the-operation/2-hs-greater-than-db-filters) for technical details on how to configure ERP Bridge.

## Filters

In our example in the "Filters" section we set the Lifecycle Stage as HubSpot property that conveys the selection of data to be synchronized from HubSpot to SQL view. So every time on HubSpot a contact has the Lifecycle Stage property populated as a Customer at the time of synchronization with ERP Bridge will pass into the SQL view which in turn is synchronized in the ERP.

<figure><img src="/files/2jfO2DyO0jveHy7wttV4" alt=""><figcaption></figcaption></figure>

## Database Mapping

In the Database Mapping section you decide the fields to synchronize to SQL from HubSpot CRM.

In the left column select the HubSpot data you want to sync to SQL and choose in the right column to which SQL column to link them. For advice on which properties synchronize please refer to the [recommended synchronization section](/readme/companies/synchronization-from-hubspot-to-erp/recommended-synchronization-sql-from-hubspot-to-database), while for information on how to build the Database Mapping please refer to the [documentation](https://docs.erpbridge.io/the-operation/2-database-mapping).

<figure><img src="/files/ofNwlWrRUFQHst5E1omF" alt=""><figcaption><p>Select the fields on the left on HubSpot to connect with its SQL column to indicate on the right column</p></figcaption></figure>

## Decision rules

The following section is Decisions rules, from here you set the actions that the agent will perform once synchronization is launched. For general information, please refer to [the appropriate section](#decision-rules).

<figure><img src="/files/SifXwJYqupFUYYB3yb9y" alt=""><figcaption></figcaption></figure>

In case the choice falls on the field ERP code:

* indicating =0 means that if the ERP code does not exist, the agent will update the record,
* in case of multiple records with the same ERP code (=1), the agent will update the record,
* in case of multiple equal records (>1), the agent will skip the record.

{% hint style="danger" %}
Remember the importance of choosing as a field "primary key" a field that will always be populated in a unique way otherwise the records will be duplicated in the new synchronizations.
{% endhint %}

The new Company operation will now appear on the Homepage, to enable synchronization make sure the connector is colored.

<figure><img src="/files/xr52Kyzc1sor1RAPqvbY" alt="" width="375"><figcaption></figcaption></figure>


# Advices and Best Practices

How to use imported data - recommendations for use

Now that the data is regularly synchronized on ERP Bridge Database, you can see them in a SQL summary table with all data.

<figure><img src="/files/StKT2Jrq8ECCEjGXImZP" alt=""><figcaption><p>Data from HubSpot on the SQL table</p></figcaption></figure>

You find below an example of stored procedure that you can use for your daily synchronization:

```sql
USE [ERPBridge]
GO
/****** Object:  StoredProcedure [dbo].[StoredProcedureName] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[StoredProcedureName] 
AS

BEGIN
	SET NOCOUNT ON;

	DECLARE @CodiceERP varchar(max)
	DECLARE @CompanyName varchar(max)
	DECLARE @StreetAddress varchar(max)
	DECLARE @VAT varchar(max)


	DECLARE cursor_companies CURSOR FOR
    
	select distinct
		ERPCode
		,CompanyName
		,StreetAddress
		,VAT

	from
		ERPBridge.dbo.T_Companies

	where
		LastModifiedDate>GETDATE()-1


	OPEN cursor_companies

	FETCH NEXT FROM cursor_companies INTO @ERPCode, @CompanyName, @StreetAddress, @VAT

	WHILE @@FETCH_STATUS=0
	BEGIN
		IF(@ERPCode IS NULL OR @ERPCode = '')
		BEGIN
			UPDATE NameTable_ERP SET CompanyName_ERP=@CompanyName, StreetAddress_ERP=@StreetAddress, VAT_ERP=@VAT WHERE ERPCode_ERP=@ERPCode	
		END
		ELSE
		BEGIN
			INSERT INTO NameTable_ERP(ERP_Code, CompanyName_ERP, StreetAddress_ERP, VAT_ERP VALUES (@ERPCode, @CompanyName, @StreetAddress, @VAT)
		END

		FETCH NEXT FROM cursor_companies INTO @ERPCode, @CompanyName, @StreetAddress, @VAT
	END

	CLOSE cursor_companies
	DEALLOCATE cursor_companies
END

```


# Deal

How to integrate Deal data from ERP Bridge

Let’s see how to set a basis configuration on the entity named Deal. The names of the properties on both ERP and HubSpot are a suggestion, we suggest to customized properties according to your specific needs.

**Sections:**

* [Synchronization from ERP to HubSpot](/readme/deal/synchronization-from-erp-to-hubspot)
* [Synchronization from HubSpot to ERP](/readme/deal/synchronization-from-hubspot-to-erp)


# Synchronization from ERP to HubSpot

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.

**Sections:**

* [SQL Recommended Synchronization](/readme/deal/synchronization-from-erp-to-hubspot/sql-recommended-synchronization)
* [Operation Configuration in ERP Bridge](/readme/deal/synchronization-from-erp-to-hubspot/operation-configuration-in-erp-bridge)
* [Advices and Best Practices](/readme/deal/synchronization-from-erp-to-hubspot/advices-and-best-practices)


# 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](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-views?view=sql-server-ver16) 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.

```plsql
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.

<figure><img src="https://lh7-us.googleusercontent.com/n7f4-uVqWBPHbjmQBeCPCC6Z0-BQ9-1q9ITCU4VT_sLAGwGLexgu0oEU4urEpaiNzzgnnpHE9ZodKf9KeX6ef-KbwOrVC2rC1mtjfhK9HcjSmrI5AU4WQgneZhw0vYT0kxqdEI0mzO6fhzhiQUH2zoI" alt="" width="375"><figcaption></figcaption></figure>

### ERP Information

<table><thead><tr><th width="213">Property Name on HubSpot</th><th width="298">Details</th><th>Field type on HubSpot</th></tr></thead><tbody><tr><td>Deal Name</td><td>Property provided by HubSpot</td><td>Single-line text</td></tr><tr><td>SalesAgent</td><td>Custom Property - To use for associate a Deal Owner with a Workflow</td><td>Single-line text</td></tr><tr><td>Offer Number</td><td></td><td>Single-line text</td></tr><tr><td>Offer Date</td><td>Custom Property</td><td>Date picker</td></tr><tr><td>Offer Revision Number</td><td>Custom Property</td><td>Number</td></tr><tr><td>Offer file URL</td><td>Custom Property - File PDF to insert in the Attachments section<br><img src="https://lh7-us.googleusercontent.com/xAkhn9UgrpCeLRwanVlgDh6ZAUUzCRBblBiY6UO1tKY3Sk0htWTp6mHWOYOoRtfCRdiaPJTRrM_uIL_1b-xLen3iQ2PRwa5PyhIjcwNIrz5et-2h5goLe3cGgtyG-UGmzB5p_pU8k3tbwLaSyLCz3po" alt=""></td><td>File</td></tr><tr><td>Offer Amount</td><td>Offer amount corresponding to the amount of the open deal</td><td>Number - Currency</td></tr><tr><td>Order Number</td><td>Custom Property</td><td>Single-line text</td></tr><tr><td>Order file URL</td><td>Custom Property - File PDF to insert in the Attachments section</td><td>File</td></tr><tr><td>Order Amount</td><td>Order amount corresponding to the amount of the closed deal</td><td>Number - Currency</td></tr><tr><td>Customer ERP Code</td><td>Custom property created to match HubSpot and ERP records, copy the ID code of the record from the ERP</td><td>Single-line text</td></tr><tr><td>Deal status</td><td>To move the deal to an exact stage via workflow</td><td>Dropdown select</td></tr><tr><td>Last Sync</td><td>For keeping track of the last sync made with the ERP</td><td>Date picker</td></tr></tbody></table>

{% hint style="danger" %}
If you decide not to consider orders in your integration, we suggest you determine the deal stage from the offer status.
{% endhint %}

{% hint style="info" %}
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.
{% endhint %}

{% hint style="warning" %}
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.
{% endhint %}


# Operation Configuration in ERP Bridge

[Please refer to this section](https://docs.erpbridge.io/the-operation) for technical details on how to configure ERP Bridge.

## Database Mapping

In the Database Mapping section you decide which fields to connect from the SQL view to HubSpot.

On the left-hand column select the data from SQL that you want to synchronize and link it with the related property on HubSpot. For more information about which properties to synchronize, please refer to the [synchronization section](/readme/companies/synchronization-from-erp-to-hubspot/sql-recommended-synchronization).

<figure><img src="https://lh7-us.googleusercontent.com/X-Ro0TjIIsx5cQgnOvw2kRblYdoDfL7dECBgIb8vCCvT5giqx5krMyW8peBkhD7X0D5SeHEGtOyLVt1g8HZTc5t3U2xb2sQXZ-ZMmQHvR2XFBjU8i5ZJrOovnNISvNJ4SPof4YoylDEDbuz-PvfmJ44" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}
We recommend to create custom section for the new properties:

* Create a property group called ERP Information for all custom company properties
* Create a custom section called ERP Information on the left sidebar of the Deal record, where insert all the created custom properties
  {% endhint %}

## Decision rules

The following section is Decisions rules, where you set all the actions that the agent will perform once the synchronization will be on. For general guidelines, please refer to the [specific section](https://docs.erpbridge.io/the-operation/3-decision-rules).

<figure><img src="https://lh7-us.googleusercontent.com/15bWeHo5zdXB5B7xjRdtETd-UM9q7zA6pk6ZQAgCGKNWOfY-b-bSV1h_5fsvNn7z71xvq8rzPgmWBFYTY93TBMzNHcQhIcKCYkOB69XIELLBdBuXzpKAxKmEVFKaMF-kTrEsj0A4lVXhmtrhms0s8Wo" alt=""><figcaption></figcaption></figure>

In our example we set Deal Name as primary key, because in SQL we connect in the same field Offer Number and Deal Name:

* Insert =0 in the first line means that if that specific Deal Name does not exist, the agent will add the record,
* With several records with the same Deal Name (=1), the agent will update the record,
* If there are several identical records (>1), the agent will skip the record.

{% hint style="info" %}
We connect Offer Number and Deal Name in the same property because then you can directly use the search bar on the deal section to find a specific offer number or deal name.
{% endhint %}

{% hint style="danger" %}
It's fundamental to choose a field as 'primary key' that will always be uniquely populated, otherwise records will be duplicated in the next synchronization.
{% endhint %}

## Associations

Now we want to associate the object Deal with an important object in our database, our advice is to create a link with the object “Companies”, and the property used to connect these two is “Customer ERP Code” and “ERP Code”.

<figure><img src="https://lh7-us.googleusercontent.com/_79y4Dojz-AS7wo_2pKPcO3zc4IjAOphblXiJgjkqDqtm6PBCAN25jtzogZFPlj0UZzWaSBmF-VYbEAPfvQNVJ57JLUjTAacBLco93seKw1ZpDSTkO-zbA2vb2BrSkLxIH-sE2wp6Qz0jt-GRaLPh5E" alt="" width="563"><figcaption></figcaption></figure>

Please refer to the [Associations section](https://docs.erpbridge.io/the-operation/4-db-greater-than-hs-associations) for more information.

<figure><img src="/files/uicozModzyGRo3aipkWE" alt=""><figcaption></figcaption></figure>

Now the new operation Deal will appear on the Homepage, to activate the synchronization make sure the button is on.

<figure><img src="https://lh7-us.googleusercontent.com/fnHtABGj5Mv_VDZeMJ3X9386zIqtxJpqseyMJEPFsXXkCFgEr8DuNzXtHAKjB9jIuevYRHp25Te_tt9DQRwoApzN6XphoAJ5gV_uA_-7nFBFZsii6NNgueAS38BAloromcVticQgpgOAQ6RUesjxre4" alt=""><figcaption></figcaption></figure>


# Advices and Best Practices

How to use imported data - recommendations for use

Now that data are synchronized on HubSpot, you can use them inside workflows, views or lists.

Please find below our tips and advices to use ERP Bridge to the fullest potential:

* [Let's see synchronized Deals](#lets-see-synchronized-deals)
* [Manage Amount for orders and offers in CRM by ERP Bridge](#manage-amount-for-orders-and-offers-in-crm-by-erp-bridge)
* [Assign a deal owner from an ERP field](#assign-a-deal-owner-from-an-erp-field)
* [Move the Deal in the pipeline based on the property "Order Number"](#move-the-deal-in-the-pipeline-based-on-the-property-order-number)
* [How to use data in report](#how-to-use-data-in-report)

## Let's see synchronized Deals

In the image below you can see how deals should appear in the deal pipeline after the synchronization with ERP Bridge.

<figure><img src="https://lh7-us.googleusercontent.com/0TGM0G5LcNKFQ2phUu32iQF9INB_vqG48URm7ZqDwOZ93wJoUQ0sh-8eqwsQkIDCs8jKNaSzik4QiE-5AqDvz7fYeGdBy6m-ImHRYTzd3NbgkkYW6Vv1scIEfoyXgdFnyvP3GNPKjcEHTobqLXWCP18" alt=""><figcaption><p>Deals pipeline after sync - board view</p></figcaption></figure>

Here there is another view of the pipeline with the property we sync correctly match in the CRM.

<figure><img src="https://lh7-us.googleusercontent.com/dN4-7vJjZBsxmW5m_VLIXgAi9XtrTNQdOP_eQ4qmVGb3A9W28CF840OqMJtVGi9G2HfQEOU-vG7ap4wxqkDUgXdy21jKxqQUqk9SSrnIrSblpXWDAXVAswflH-yZE74W_s8s7XSkr9N8HO0JIjG_Vgg" alt=""><figcaption><p>Deals pipeline after sync - list view</p></figcaption></figure>

If we open one of these record, you will see in the attachment section the file pdf correctly match.

<figure><img src="https://lh7-us.googleusercontent.com/ZdZxz6_IiOCGPn2rMRMh71QDtBJDv8BdtZoh4RZOY2-4n_jsVvY8TYMPqVXfdgVknmZvEAwl1KJEgiSOscqaga4wgJZetkQ3EcCJ8D1r00pqzyBzTAC9lXE-aMILJLqaaAoMuNK0DLxDWG5d729tRo8" alt=""><figcaption></figcaption></figure>

If you click on the file, you can see the preview of the order sent.

<figure><img src="https://lh7-us.googleusercontent.com/p5kwnhM2rtrEB7TGw8Cn7CMBSkhkl9J6B87OM-1gE1j8tmYs31vCcfKZwUGAj1g9tfw_9RX8Ix9LxGufxZU8kQBECmiv-Lo53cFhdUSc8h6MXYRSvA5EE_7A77st7DRgFdOZnDOq3BNLFRWeN1km9-Q" alt=""><figcaption><p>Quote preview - file pdf attached on the deal record</p></figcaption></figure>

As the same time, some of these offers has been converted into orders.

<figure><img src="https://lh7-us.googleusercontent.com/5zBSUkdspwXT1FKAwRqcUxuVdcPJIu3KmxaeHCYDrS3wW7qde_2yfkQsIxHYlXkJPp2TThUNDLQMfrUmBlPn6yeUjVf4edcFep-_5e9iH3UA6SNqOuGdxQDqm_sPfuValozz2eLQh8Upn5C5KHLA230" alt=""><figcaption></figcaption></figure>

If we open one of them you will see two pdf; one with the offer and one with the order's pdf.

<figure><img src="https://lh7-us.googleusercontent.com/7vZ9KJhRdgUo7KtDXDMaW7t54Li6QX2zAEBTF5z4mUWwJZssKvBMjkwF9A6FoQp4pXzfSH1o8jqWVBgO1bHVqcabg2UXrsRUKWooF_So4KKZyk_OxEXpuBmWWQgYDQDMUDtez_DP0M5fXaKrsCLlKSg" alt=""><figcaption></figcaption></figure>

Click on the file to see the preview with all information about the order.

<figure><img src="https://lh7-us.googleusercontent.com/6Xq_ZkDDnq2MPCkNOK2cAxv9_T-UUr5xRY7_sA7geIbaljce5fIlIMIzm0QgPND2XMJh8Do8DJtlryfN-8sZ0Yz9tcbdoMf8w-Z1izuwDDOArOG_1FfNvIrqi4gZwbth5WWBGOI-oEeEJIAkCXq7I7o" alt=""><figcaption></figcaption></figure>

## Manage property Amount for orders and offers in CRM from ERP Bridge

This automation is an example to manage the Default property "Amount" and updating it depending on offers and orders.

* If the property "Order Amount" is knows the field "Amount" will be populated with the "Order Amount";
* If the property "Order Amount" is unknows the field "Amount" will be populated with the amount in the property "Offer Amount".

This means that every time an offer switch to order the property Amount will be updated consequently.

<figure><img src="/files/pLv7xZ32VYx96fYK2eIU" alt="" width="370"><figcaption><p>Workflow example - How to build an automation to set the amount depending on company's offer and order.</p></figcaption></figure>

## Assign a deal owner from an ERP field

One of the tips we propose is to create a workflow that automatically attributes the agent - field imported from the ERP in HubSpot as single-line text, to the corresponding user on HubSpot. In this case as an input trigger of the workflow you will have the property 'Sales Agent', a property integrated from the ERP. If it is populated and the company owner is unknown, the company will be assigned to the HubSpot user set from the Workflow.

<figure><img src="https://lh7-us.googleusercontent.com/pBBd7kvicUSW0x-AYGs0JN-T6J0Px7Zh7KV2tkvWb6tr1XR27c5RQN4FviAyYFUDBnXKiQ6WAdl7omZdKlfWBeTBvav_vbPrgXE-zSLpDKXoTnQ37m7f88LXTBXzPb1V9zOLyecVA1hSfy0eeveoD7E" alt=""><figcaption></figcaption></figure>

Then following the workflow:

* Records that correspond to Gianluca Giorgini on ERP, on HubSpot will be assigned to Gianluca Giorgini,
* Records matching Veronica Bresciani on ERP, on HubSpot will be assigned to Veronica Bresciani,
* Records matching Francesco Fabbri on ERP, on HubSpot will be assigned to Francesco Fabbri,
* For all the anomalies, an email alert will be sent to a specific user admin.

<figure><img src="https://lh7-us.googleusercontent.com/WvtCBbHsygx9hp0FeDv8IDTA-J_TGbrdfYJpWK3WVXFxUmM8__ea7hg_zzB3a7y1oVKTdg4mXwrhVeaDOJc9U-998dbYgo-TENjYEZfDYdh27Nn55wwqF3fwqd4pJum8LG52p4-behtYFCRKhr45Fcg" alt=""><figcaption><p>Latest Sync from ERP Bridge with automatic association of Deal Owner</p></figcaption></figure>

## Move the Deal in the pipeline based on the property "Order Number"

Integration with ERP Bridge could be useful also to manage deal pipeline because based on strategic properties the deals can be moved automatically depending on changes on these fields.

For example, in the automation below all the deals with the property "Order number ERP Bridge" are enrolled and if the property is known the deal moves in the stage "Order acquired", because this property (Order number) is populated only when a deal is won, meaning is time to move the record in the pipeline.

<figure><img src="https://lh7-us.googleusercontent.com/aQlSpJ2SDeVEdGfOEbVQBy9ijYgyGeaN1MI4qyC-7zuco9GXK0nLQUyXDNFPQ6OGoQ8XT4JY3CLjIVp1iE-4m-J_3EoxYnNomSnPaq48DwhO6Jz9uVk3CRFuQJsqeZxIRTNbJLQnUJjWiGCFndtigK0" alt="" width="375"><figcaption><p>Automation to manage deal stage in pipeline</p></figcaption></figure>

## How to use data in report

Thanks to HubSpot data integration you can now create custom dashboard and reports. For example, we recommend analysis with data of current and previous deals in the last 90 days.

<figure><img src="https://lh7-us.googleusercontent.com/y2dmwnLfOpR-MABT5E6sDolW6MP_52KScTNy-rv-XWad1vXJYotDNlBipOnMuRqoas3857cd2tEGUQLXqXfmJYm4gcjcRZ3TJoZq_rG1L_tFzsdcnA-FyhjD5DjhYNGiio1HQfjzgh92qVidYpIqa4M" alt=""><figcaption><p>Number of Open Deal in the last 90 days</p></figcaption></figure>

Another graph useful for comparison could be the total number of close deals in the last 90 days.

<figure><img src="https://lh7-us.googleusercontent.com/Bu-Z48bR_GzSAZaQ1WDXAdeCFYFTbdMJl4hi4amCvK4XXcG6-54splMT9q3-fdDlsDtk-FdbWMsWMmRRRP5DGWgDjQwWo-467tlBVcYGTsJa1QVoKm9AdcENoRGo6WF798XHNPCADE3M9GMSKcpLmv4" alt=""><figcaption><p>Number of Cloise Deals in the last 90 days</p></figcaption></figure>


# Synchronization from HubSpot to ERP

It is possible to transfer deals within the ERP but it's a very rare occurrence therefore for the moment there are no guidelines for this section.