Sage CRM

Hi all,

Extending on Video #1, here's the next video where we take information from Sage 100's AR_CustomerSalesHistory table and bringing it into Sage CRM. This is only one example of what can be brought into Sage CRM to enable users within Sage CRM to have visibility of Sage 100 data without having to be in Sage 100.

Video Link: https://youtu.be/j3vadUPIhgI

I did move a bit quick, so if you need the scripts from the video - see below:

-- Pulling AR_CustomerSalesHistory data from Sage 100 company via a Linked Server
Select * FROM [ABX]...AR_CustomerSalesHistory 
Select * FROM [EEC]...AR_CustomerSalesHistory 
Select * FROM [MFG]...AR_CustomerSalesHistory 

-- Packaging the current year's period entries in the AR_CustomerSalesHistory table and bringing it into Sage CRM
-- Erase and re-populate company ABX-related current YTD information where it exists
Update Company Set comp_mas_currentytd = 0, comp_mas_currentytd_CID = 1
from Company 
where comp_mas_currentytd > 0 and comp_mas_companyCode = 'ABX'

Update Company
Set comp_mas_currentytd = s.comp_dollarssold
from Company as c 
INNER JOIN 
(
	Select ARDivisionNo, FiscalYear, SUM(DollarsSold) as comp_dollarssold, customerno FROM [ABX]...AR_CustomerSalesHistory GROUP BY ardivisionno, customerno, FiscalYear
) as s on (c.comp_mas_customerno = s.customerno COLLATE SQL_Latin1_General_CP1_CI_AS AND c.comp_mas_ardivisionno = s.ardivisionno  COLLATE SQL_Latin1_General_CP1_CI_AS) WHERE c.comp_mas_customerno IS NOT NULL AND s.FiscalYear = DATEPART(yyyy,GETDATE());

------------------------------
Basil Malik
e: basil@ss-adv.com
p: (918)600-8700

"Whatever you are, try to be a good one."
------------------------------