Once upon a time I wrote an article called "But I really, really need to see the actual oppo_opportunityid displayed!"
This article looks at how you could use the column alias idea to allow for records to be searched for by the their unique ids.
Note: Generally I think this is not a good idea. It is a much better idea to use the StoredProc entry type to allow for alternative keys to be used.
But if you must, you must.
You can find out which view you need to use by looking for the definition of the searchbox in the database.
e.g.
select * from Custom_ScreenObjects where CObj_Name = 'companysearchbox';
This tells me that if I want to add searching on the comp_companyid I will have to add an alias column to the view vSearchListCompany. This is a system view.
Within
Administration -> Customization -> Company
Change the view definition from
CREATE VIEW vSearchListCompany AS SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(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_PhoneFullNumber AS Comp_PhoneFullNumber, epd_comp.epd_FaxCountryCode as Comp_FaxCountryCode, epd_comp.epd_FaxAreaCode as Comp_FaxAreaCode, epd_comp.epd_FaxNumber as Comp_FaxNumber, Company.*, Person.*, Account.*, Address.*, (SELECT CASE WHEN CompanyCount = 0 or AccountCount = 0 THEN NULL ELSE 'Y' END FROM vSharedAddress WHERE vSharedAddress.AdLi_AddressId = Addr_AddressId) as Addr_IsSharedAddress FROM Company LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID LEFT JOIN Person ON Comp_PrimaryPersonId = Pers_PersonId LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID LEFT JOIN Address ON Comp_PrimaryAddressId = Addr_AddressId LEFT JOIN Account ON Comp_PrimaryAccountId = Acc_AccountId WHERE Comp_Deleted IS NULL
to
CREATE VIEW vSearchListCompany AS SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, comp_companyid as comp_compid, 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_PhoneFullNumber AS Comp_PhoneFullNumber, epd_comp.epd_FaxCountryCode as Comp_FaxCountryCode, epd_comp.epd_FaxAreaCode as Comp_FaxAreaCode, epd_comp.epd_FaxNumber as Comp_FaxNumber, Company.*, Person.*, Account.*, Address.*, (SELECT CASE WHEN CompanyCount = 0 or AccountCount = 0 THEN NULL ELSE 'Y' END FROM vSharedAddress WHERE vSharedAddress.AdLi_AddressId = Addr_AddressId) as Addr_IsSharedAddress FROM Company LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID LEFT JOIN Person ON Comp_PrimaryPersonId = Pers_PersonId LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID LEFT JOIN Address ON Comp_PrimaryAddressId = Addr_AddressId LEFT JOIN Account ON Comp_PrimaryAccountId = Acc_AccountId WHERE Comp_Deleted IS NULL
Note: I am using Sage CRM v7.1sp1.
Note: This technique never makes the actual unique id of the record editable so no corruption of keys is possible.
This will have added comp_compid as an alias that is then available to be selected as a field in the companysearchbox.
BUT... this will have the new derived field 'comp_compid' behave like a string so the screen will behave 'oddly'. If I searched for a comp_compid of 28 I would get all the companies whose comp_companyid values start with 28 (28, 280, 281 etc)
If I want the field to be treated like an integer then I need to add a definition of the derived field into the custom_edits. See the article "Adding Derived Fields in Views into Meta Data"
