A customer had a business requirement to be able to search for people and filtering them by either their business or home address.
When an address is entered into the system for a person a user can specify the type of address as can be seen in the screen below:
The default choice for address type is 'Business' or 'Home'. This information is stored in the address_link table that links the address to the person. The field is called 'adli_type'.
Because the information is stored in an intersection table Sage CRM uses a special translation technique to control the ability to select the address type on the screen.
The choices for the person address type are stored as translations. You can access these via
Administration -> Customization -> Translations
The person address types belong to the caption family 'Link_PersAddr'.
I have discussed customizations involving the address_link table before in these articles
Working with a Person's Address Type
How do default addresses and persons get created for a Company?
The standard screen, shown below, used for searching for a person only references the default address for a person
This is because the search screen and the results grid are based on views that only link the person and address table through a direct relationship rather than through the address_link table.
In order to allow searching for person by address type either the existing views would need to be changed or new functionality needs to be added. But since the existing views would need to be changed extensively which would have impacts elsewhere within the system it is easier to add a new view which links the tables. These new views can then be used for the new search behaviour, and for creating reports and groups. Because a user can create reports and groups on the new view the user would also be able to create new List gadgets for Interactive Dashboards.
New Search Screen
Below is the image of a new search screen that allows for search based on business or home address.
The customizations have been added to a new component which members of the Developer Program can download.
The search page was built using only Meta Data and RunBlock. The follow are added:
- A new view - vPersonAddressSearch. This view joins the Person, Company, Address_Link and Address tables. The Address_Link table holds the data (adli_type) that describes the type of address for the person.
- A new List for Person - PersonAddressSearchGrid.
- A new screen for Person - PersonAddressSearchBox
The creation of the new view is the key point. The view has been created using the following SQL defined through the interface under
Administration -> Customisation -> Person
CREATE VIEW vPersonAddressSearch AS SELECT RTRIM(ISNULL(dbo.Person.Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(dbo.Person.Pers_LastName, '')) AS Pers_FullName,
epd_pers.epd_EmailAddress AS Pers_EmailAddress, epd_pers.epd_PhoneCountryCode AS Pers_PhoneCountryCode,epd_pers.epd_PhoneAreaCode AS Pers_PhoneAreaCode,
epd_pers.epd_PhoneNumber AS Pers_PhoneNumber,epd_pers.epd_PhoneFullNumber AS Pers_PhoneFullNumber, epd_pers.epd_FaxCountryCode AS Pers_FaxCountryCode,
epd_pers.epd_FaxAreaCode AS Pers_FaxAreaCode, epd_pers.epd_FaxNumber AS Pers_FaxNumber, epd_pers.epd_FaxFullNumber AS Pers_FaxFullNumber,
epd_comp.epd_EmailAddress AS Comp_EmailAddress, epd_comp.epd_PhoneCountryCode AS Comp_PhoneCountryCode,epd_comp.epd_PhoneAreaCode AS Comp_PhoneAreaCode,
epd_comp.epd_PhoneNumber AS Comp_PhoneNumber,epd_comp.epd_FaxCountryCode AS Comp_FaxCountryCode, epd_comp.epd_FaxAreaCode AS Comp_FaxAreaCode,
epd_comp.epd_FaxNumber AS Comp_FaxNumber,dbo.Person.Pers_PersonId, dbo.Person.Pers_CompanyId, dbo.Person.Pers_PrimaryAddressId, dbo.Person.Pers_PrimaryUserId, dbo.Person.Pers_Salutation,dbo.Person.Pers_FirstName, dbo.Person.Pers_LastName, dbo.Person.Pers_MiddleName, dbo.Person.Pers_Suffix, dbo.Person.Pers_Gender, dbo.Person.Pers_Title,
dbo.Person.Pers_TitleCode, dbo.Person.Pers_Department, dbo.Person.Pers_Status, dbo.Person.Pers_Source, dbo.Person.Pers_Territory, dbo.Person.Pers_WebSite,dbo.Person.Pers_MailRestriction, dbo.Person.Pers_CreatedBy, dbo.Person.Pers_CreatedDate, dbo.Person.Pers_UpdatedBy, dbo.Person.Pers_UpdatedDate,dbo.Person.Pers_TimeStamp, dbo.Person.Pers_Deleted, dbo.Person.Pers_LibraryDir, dbo.Person.Pers_ChannelID, dbo.Person.Pers_UploadDate,dbo.Person.pers_SecTerr, dbo.Person.Pers_WorkflowId, dbo.Person.Pers_AccountId, dbo.Person.pers_intforeignid, dbo.Person.pers_intid,dbo.Person.pers_intlastsyncdate, dbo.Person.pers_promote, dbo.Person.pers_ConflictResDate, dbo.Person.pers_departmentcode, dbo.Person.Pers_OptOut,dbo.Company.Comp_CompanyId, dbo.Company.Comp_PrimaryPersonId,dbo.Company.Comp_PrimaryAddressId, dbo.Company.Comp_PrimaryUserId, dbo.Company.Comp_Name, dbo.Company.Comp_Type, dbo.Company.Comp_Status,dbo.Company.Comp_Source, dbo.Company.Comp_Territory, dbo.Company.Comp_Revenue, dbo.Company.Comp_Employees, dbo.Company.Comp_Sector,dbo.Company.Comp_IndCode, dbo.Company.Comp_WebSite, dbo.Company.Comp_MailRestriction, dbo.Company.Comp_CreatedBy, dbo.Company.Comp_CreatedDate,dbo.Company.Comp_UpdatedBy, dbo.Company.Comp_UpdatedDate, dbo.Company.Comp_TimeStamp, dbo.Company.Comp_Deleted, dbo.Company.Comp_LibraryDir,dbo.Company.Comp_ChannelID, dbo.Company.Comp_SecTerr, dbo.Company.Comp_WorkflowId, dbo.Company.Comp_UploadDate, dbo.Company.comp_SLAId,dbo.Company.Comp_PrimaryAccountId, dbo.Company.comp_intforeignid, dbo.Company.comp_intid, dbo.Company.comp_intlastsyncdate,dbo.Company.comp_promote, dbo.Company.Comp_OptOut, dbo.Address.Addr_Address1,dbo.Address.Addr_Address2, dbo.Address.Addr_Address3, dbo.Address.Addr_Address4, dbo.Address.Addr_Address5, dbo.Address.Addr_City, dbo.Address.Addr_State,dbo.Address.Addr_Country, dbo.Address.Addr_PostCode, dbo.Address_Link.AdLi_Type FROM dbo.Person INNER JOIN dbo.Address_Link ON dbo.Person.Pers_PersonId = dbo.Address_Link.AdLi_PersonID INNER JOIN dbo.Address ON dbo.Address_Link.AdLi_AddressId = dbo.Address.Addr_AddressId LEFT OUTER JOIN dbo.CRMEmailPhoneData AS epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = dbo.Person.Pers_PersonId LEFT OUTER JOIN dbo.Company ON dbo.Person.Pers_CompanyId = dbo.Company.Comp_CompanyId AND dbo.Company.Comp_Deleted IS NULL LEFT OUTER JOIN dbo.CRMEmailPhoneData AS epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = dbo.Company.Comp_CompanyId WHERE (dbo.Person.Pers_Deleted IS NULL)
Once the new view exists the same technique can be used that has been described in the article "Creating a Codeless Search Screen on an External Database using RunBlock".