Explore How You Can Display Sage X3 Data in Sage CRM

 

One of the most distinct advantages for choosing Sage CRM over all of the other mid-market CRM solutions is the benefit of enjoying seamless integration with a suite of ERP applications.

In 2015, Sage released the X3 Connector, and this opened up countless opportunities for Sage X3 customers to get into the CRM game. The X3 Connector user experience includes some of the expected integration points, like Master Data (Customers) and Quotes and Orders to name a few, while enjoying an integration framework that allows the consultant to create custom integration points and additional fields between Sage X3 and Sage CRM.

The ability to enter and update X3 Data from within Sage CRM is an exceptional leap forward for many of our clients, as their salespeople wanted to track their opportunities and document their communications while entering new Customers, Quotes and Orders using a “single pane of glass”, aka, Sage CRM.

As the user experience matured from the basics, our clients became more sophisticated and demanded more from this customer experience. In turn, they wanted to appreciate and analyze their Sage X3 data from within Sage CRM.

Let me share with you how we delivered to this request.

First of all, the root of all reporting within Sage CRM is a MS SQL Database query.  These queries define the tables and data fields that are exposed to the native Sage CRM reporting tool and, in turn, what data is exposed to the user. As Sage CRM and Sage X3 both utilize MS SQL on their backend then shouldn’t one logically assume that a query that joins both databases should accomplish this request.

Listed below is an example of a custom view (data query) that we created in Sage CRM to join/return X3 data.

 

CREATE VIEW vSalesHistd

AS

SELECT CO.Comp_Name, CO.Comp_CompanyId, CO.Comp_SecTerr, CO.Comp_createdBy, CO.Comp_PrimaryUserId, CO.Comp_ChannelID, ID.ITMREF_0, ID.GROPRI_0, ID.SOHNUM_0, ID.NUM_0, IH.BPAPAY_0,  IH.ACCDAT_0 as Comp_LEACCDAT_0, comp_x3_bpcord, CAST(YEAR(IH.ACCDAT_0) AS NCHAR(4)) repo_YYYY

FROM CRM.dbo.Company AS CO

                  INNER JOIN [ABCsql\sagex3].[x3].[XYZ].[SINVOICED] AS ID ON CO.comp_x3_bpcord=BPCINV_0 COLLATE DATABASE_DEFAULT

                  RIGHT JOIN [ABCsql\sagex3].[x3].[XYZ].[SINVOICE] AS IH ON ID.NUM_0=IH.NUM_0 COLLATE DATABASE_DEFAULT

WHERE Year(IH.ACCDAT_0) = Year(GetDate())

GROUP BY ID.NUM_0, ID.ITMREF_0, ID.SOHNUM_0, CO.Comp_Name, CO.Comp_CompanyId, CO.Comp_SecTerr, CO.Comp_CreatedBy,

                      CO.Comp_ChannelID, CO.Comp_PrimaryUserId, ID.GROPRI_0, IH.BPAPAY_0, IH.ACCDAT_0, comp_x3_bpcord

 

As you can see from the above query, we are joining the Sage X3 Invoice Detail table on the Sage CRM Company table using the Customer Number {comp_x3_bpcord} as the unique identifier. It is also important to note that as Sage CRM and Sage X3 are typically installed on different instances of MS SQL, we are required to define the Sage X3 SQL Instance, Database and Schema (Folder) in our query. In the above query we are utilizing he following:

  • SQL Instance: ABCsql\sagex3
  • Database: X3
  • Schema: XYZ

In turn, the above query can be used to expose customer data in the My CRM and Company dashboards. If the Sage CRM user leverages the “Filter by Current User” option when creating the report, Sage CRM will return data to My CRM Dashboard using the Account Manager as the filter against the current user.

Alternative, the identical report and query from above can be used to expose the current year sales for a customer in the Company dashboard.

Finally, fields translation will need to be added to Sage CRM for the X3 fields that are being returned to Sage CRM.

Try it out and take it for a spin. You’ll see that it’s easy to expose Sage X3 data in Sage CRM.