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'
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!