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

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
   Using Global Script functions in InforCRM
I recently had a project that required me to be able to check if a user belonged to a particular team.
Posted on May 05, 2015 by Jason Buss to Jason Buss' Blog
   Telnet Sample to Send an SMTP email
I am always forgetting how to test sending an email through an SMTP gateway using the Telnet client. 
Posted on Dec 12, 2014 by Kris Halsrud to Kris Halsrud's Blog
   Problem setting SLXTextbox enabled property / Setting unexposed base control properties for a custom control
I recently had an issue with a Quickform where I was attempting to set the Enabled property of a TextBox
Posted on Dec 05, 2014 by Jason Buss to Jason Buss' Blog
   Adding a report link to the Common Tasks pane in Infor CRM (formerly Saleslogix)
When you are viewing an Entity Detail page, the Common Tasks pane on the right of the page provides a nu
Posted on Nov 12, 2014 by Jason Buss to Jason Buss' Blog
   Problem with the Clean Build Folders option in Application Architect
On occasion, it becomes necessary to clean out the build folders and deployed website prior to a build/de
Posted on Sep 16, 2014 by Jason Buss to Jason Buss' Blog

No Comments

Leave a Comment

All contents Copyright © 2015 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