If you are running QuoteWerks Corporate Edition hosted to SQL, along with
GoldmineSQL then it is relatively straightforward to automatically update
fields in GoldMine against the contact record to show the number and totals
of Quotes, Orders and Invoices. This will be in addition of the normal 'out of the box' QuoteWerks functionality that links Sales, History etc to the contact.
It is a straightforward trigger that can be installed in the QuoteWerks database, that will automatically update the GoldMine fields without any user interaction. Although the example here is provided for GoldMine any other SQL hosted CRM system could be linked in this way.
Here is the trigger:
Create Trigger QWUpdateInsert on DocumentHeaders after insert,update
as
declare @CMAccountno varchar(50)
declare @DocType varchar(30)
declare @QGrandtotal float
declare @OGrandTotal float
declare @IGrandTotal float
declare @countdocs int
select top 1 @CMAccountno=isnull(SoldToCMAccountno,''),
@docType=doctype from inserted
if @CMaccountno!=''
begin
-- don't want to have to calculate the whole lot each time
-- only process for the type of document actually being worked on.
if @docType='QUOTE'
begin
select @QGrandtotal=sum(Grandtotal), @countdocs=count(*) from
DocumentHeaders where DocType='QUOTE' and SoldToCMAccountno=@CMAccountno
update Goldmine..Contact2 set uqwQTot=@QGrandtotal,uqwQcnt=@countdocs
where accountno=@CMAccountno
end
if @DocType='ORDER'
begin
select @OGrandtotal=sum(Grandtotal), @countdocs=count(*) from
DocumentHeaders where DocType='ORDER' and SoldToCMAccountno=@CMAccountno
update Goldmine..Contact2 set uqwOTot=@OGrandtotal,uqwOcnt=@countdocs
where accountno=@CMAccountno
end
if @DocType='INVOICE'
begin
select @IGrandtotal=sum(Grandtotal), @countdocs=count(*) from
DocumentHeaders where DocType='INVOICE' and SoldToCMAccountno=@CMAccountno
update Goldmine..Contact2 set uqwITot=@IGrandtotal,uqwIcnt=@countdocs
where accountno=@CMAccountno
end
end
The above trigger expects the existence of GoldMine fields uqwITot, uqwQTot, uqwQTot all defined as numeric large enough to hold the total value of all documents for that customer, and fields uqwIcnt, uqwQcnt, uqwOcnt again defined as numeric to hold the count of documents for that customer.
The database that GoldMine is hosted to in the above example is Goldmine, but this can be changed to suit your own requirements.
|
