Opportunity & Quote Import

Sage CRM Opportunity Quote Import (SAMSAGECRMIM)

The SageCRM import integration creates & updates Opportunities and Quotes in SageCRM. It takes data from an Excel spreadsheet, applies a multi-level hierarchical transformation, some expression based transformation, and finally imports the data into SageCRM.

It is important that SageCRM is correctly setup as per the IMan Setup and CRM Setup sections in the CRM User Guide document.

Flow of the integration:

  1. Read
  2. Hierarchy
  3. Map
  4. Connector

Read Transform

The read transform extracts data from the Excel spreadsheet.

Hierarchy Transform

To import the data into SageCRM it must be in a form or structure that corresponds to the type of data that is being imported. The data being imported consists of an Opportunity, with a Quote and QuoteItems.

The hierarchy transform will apply a 3 three level hierarchical structure to the data, one level for each entity.

  1. To view the hierarchical transform press the Refresh button on the Field Mapping tab > click ‘+’ to expand the record.

  1. To view how the hierarchical transform is setup click Edit button (pencil).

  1. The selected fields are those that make-up the resulting Opportunity transaction.
  2. The Key column is used to specify the field(s) used to define the relationship between each of the transaction types. In this example the ‘Company’ field is being used to uniquely identify each ‘Opportunity’ transaction.

  1. To return to the previous screen press either the Green Tick or the Red Cross below the grid.
  2. To view the Quote record, change the drop-down from Opportunity to Quote and press Edit.

  1. To uniquely identify the Quote record the Company and QuoteId fields are used.
  2. Return to the previous screen by pressing the Green tick or Red Cross.

  1. Change the drop-down from Quote to QuoteLines; you will see the Parent Id is set to Quote. Now press the Edit button to view the setup.

  1. To uniquely identify QuoteLines the previous two key fields are used (Company & QuoteId) in addition to the LineNo field.

Map Transform

The Map transform allows expressions and formulas to transform the field values.

This transform performs two lookups against CRM to obtain the value of some key fields.

CompanyId Field

To associate the imported opportunity with a company, we require the ID of the company record corresponding to its name. To achieve this, the SageCRMLookup function is used.

  1. Double click the CompanyId field to show the formula.

  1. The formula below queries the Company table to return the company ID field using the Company field in the dataset.
  2. A more detailed explanation of the lookup formula(s) can be found in both the SageCRM Guide as well as the User Guide.

  1. Once complete press the Green Tick or Red Cross.

The following table describes the other fields with expressions:

Field

Notes

OpportunityDetails

A static text value set to ‘Web Site Inquiry’

AssignedTo

A static integer value set to a specific user. Instead of using a static value, a lookup could be used to translate the name of the user to its underlying ID.

CloseBy

A formula to calculate the CloseBy date. The formula adds 21 days to the current date.

OpportunityId

A lookup to obtain the ID of an opportunity within SageCRM. The lookup queries the Opportunity table using the Description field. If no match, the function does not return a value and the field remains empty.

 

When importing into SageCRM, if the ID field of the entity is empty or set to ‘0’ a new record will be created. When the field is populated with a valid ID the corresponding record is updated.

 

This lookup should return no result on the first instance the entire import is run; the second and subsequent times will the lookup will return a value and consequently an update will be performed to the existing record.

ImportSuccess

An empty field that will be used to store a textual value to denote if the record has been successfully imported or not.
   

SageCRM Connector Transform

The connector transform defines how the dataset fields are mapped into the SageCRM.

The connector transform for the SageCRM import will map fields in all three of the dataset records to entities within SageCRM.

  1. When opening the SageCRM connector, it may take up to 20 seconds to complete whilst a connection is made to SageCRM.
  2. The ‘Options’ tab defines some keys for the import.
  3. The SageCRM System selects which SageCRM instance to connect to; the settings are found in the System Connectors setup in the Setup area of IMan.
  4. SageCRM Import Type selects the top-level entity that is being imported. This is set to Opportunity as this is the top-level entity in the dataset.
  5. Update Operation defines how records are inserted/updated in SageCRM.

  1. The Field Mapping tab defines how the entities and fields are mapped to the ‘Import Type’ selected on the Options tab.

  1. To map the fields, press the Edit button, and select the field from the field dropdown.

  1. Once complete press the Green Tick or Red Cross at the bottom of the grid.
  2. Now change the ‘Transaction Id’ drop down at the top of the page from Opportunity to Quotes. The Quote transaction is mapped to the Quote entity, so the SageCRM Transaction Type dropdown changes accordingly:

  1. Change the Transaction Id to QuoteLines; the Transaction Type is now set to Quote Items.
  2. Pressing Refresh will initiate the import the transactions into SageCRM.

  1. At the top of the preview grid, check the Generation Status; initially this will be Partial Result Set. Press refresh until it changes to complete; this may take upwards for 20 seconds as SageCRM may need to startup.
  2. When the import is complete, scroll the preview area to the right and you will find the OpportunityId field populated the auto-generated ID value. The import success field will also be populated with either Success or Failure.

  1. If successful the Opportunity records with their quotes should all be imported.