Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

Adding Billing and Shipping addresses to SageCRM merge forms

I was recently doing some work on merge forms in SageCRM and discovered that for Cases, SageCRM didn't take into account different address types in the OOTB Case merge fields.  Normally, Address information is derived by joining the case_PrimaryCompanyID to the Company table, and then joining from Comp_PrimaryAddressID to the address tables.  With these joins, you could only include the primary address for the Case's account, however I wanted to display both Shipping and Billing addresses as well.

In this post, I'll go through the view changes I made to include Billing and Shipping Addresses on this merge form.

Sage CRM has a table named  Address_Link which holds the address type as well as IDs for Company and Person records.  We're going to create 2 new views, one for the billing address, and one for the shipping address.  These are defined simply as:

 CREATE View [dbo].[vCompanyBillingAddress_Link] as
select * from dbo.Address_link
where AdLi_PersonID is null and AdLi_Type = 'Billing'
and
CREATE View [dbo].[vCompanyShippingAddress_Link] as
select * from dbo.Address_link
where AdLi_PersonID is null and AdLi_Type = 'Shipping'

 

By grabbing records where the PersonID is null, we should only be getting Company address records.  Once these views are created, then we'll need to modify the Case merge view (named vMailMergeCase) to include the new views and fields.  This needs to be defined in the case view definitions, but I find it easier to figure out what I am doing in SQL Management Studio and then copy the code over to SageCRM.  We will make two changes to the VMailMergeCase view.  first of all, we need to join to the new views we created:

 LEFT OUTER JOIN
dbo.vCompanyBillingAddress_Link ON dbo.Company.Comp_CompanyID = dbo.vCompanyBillingAddress_Link.AdLi_CompanyID LEFT OUTER JOIN
dbo.Address Adr2 on dbo.vCompanyBillingAddress_Link.AdLi_AddressID = Adr2.Addr_AddressID LEFT OUTER JOIN
dbo.vCompanyShippingAddress_Link ON dbo.Company.Comp_CompanyID = dbo.vCompanyShippingAddress_Link.AdLi_CompanyID LEFT OUTER JOIN
dbo.Address Adr3 on dbo.vCompanyShippingAddress_Link.AdLi_AddressID = Adr3.Addr_AddressID

Once the joins are included, we want to add the new address fields.  I used a CASE statement so that if there was not a billing or shipping address defined, it would just present the companies primary address as it was before.
BillingAddress1 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address1 else Adr2.Addr_Address1 END,
BillingAddress2 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address2 else Adr2.Addr_Address2 END,
BillingAddress3 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address3 else Adr2.Addr_Address3 END,
BillingAddress4 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address4 else Adr2.Addr_Address4 END,
BillingAddress5 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address5 else Adr2.Addr_Address5 END,
BillingCity = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_City else Adr2.Addr_City END,
BillingState = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_State else Adr2.Addr_State END,
BillingCountry = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Country else Adr2.Addr_Country END,
BillingPostCode = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_PostCode else Adr2.Addr_PostCode END,


after the billing information fields, I do the same with the Shipping address fields from the Adr3 alias. Once these view changes are done in SageCRM, you should have the billing and shipping address available in the case merge fields.

I've attached the entire modified MergeView so you can see how I incorporated everything together.  Again, any changes to the Merge view must be defined within SageCRM, under  Administration->Customization->Cases->Views tab. 

Thanks for reading! Geeked

 

  Attachment: CaseMergeView.sql

What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Configuring and Packaging secured actions in Saleslogix 8.0
Using Secured Actions, you can restrict access to user interface elements in the Saleslogix web client.
Posted on Aug 12, 2014 by Jason Buss to Jason Buss' Blog
 
   Debugging Custom Assemblies Using Visual Studio
Developing for Saleslogix web using external assemblies provides you with a lot of freedom when developin
Posted on Jun 10, 2014 by Jason Buss to Jason Buss' Blog
 
   Installing a Saleslogix Web Bundle
I realized that we didn't have any sort of simple write up of how to install a Saleslogix web bundle,
Posted on May 08, 2014 by Jason Buss to Jason Buss' Blog
 
   Setting the Defaults on the Insert Account/Contact Duplicate Record View
When creating a new Account/Contact record in Saleslogix web, you have the ability to check for duplicat
Posted on Mar 10, 2014 by Jason Buss to Jason Buss' Blog
 
   Adding string formatting to values using WebEntityBinding
In a recent project, I needed to round numeric values displayed in the Opportunity Snapshot.  This
Posted on Feb 05, 2014 by Jason Buss to Jason Buss' Blog
 
Comments

No Comments

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register