Summary:
A customer may have a requirement to add a date/time column to the Company Find grid depicting the last Note record was created against a company. This can be done by creating a new view, and amending an existing view.
More information:
This customisation can be carried out by doing the following.
1: Create a new view called vLastNote on the Notes entity, in Administration -> Customisation -> Notes -> Views.
CREATE VIEW vLastNote
AS
SELECT dbo.Notes.Node_NoteId, dbo.Notes.Note_UpdatedDate,
dbo.Notes.Note_Note, dbo.Notes.Note_ForeignId
FROM dbo.Company CROSS JOIN
dbo.Notes
WHERE (dbo.Notes.Note_NoteId =
(SELECT MAX(Note_NoteId) AS Expr1
FROM dbo.Notes AS Notes_1
WHERE (Note_ForeignId = dbo.Company.Comp_CompanyId))) AND (dbo.Notes.Note_Deleted IS NULL)
Once this is done, go to Administration -> Customisation -> Company -> Views and edit the vSearchListCompany view.
Then update the vSearchListCompany view as per below:
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,
Note_NoteId, Note_UpdatedDate, Note_Note
FROM Company
LEFT OUTER JOIN vLastNote ON Comp_CompanyId = Note_ForeignId
LEFT OUTER 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 and Acc_Deleted IS NULL
The next step is to go to Administration -> Customisation -> Company -> Lists and add the last updated date (Note_UpdatedDate) and note text (Note_Note) to the Company Grid.
The end result is that when you view a company list, the last note recorded against a company, and the date it was added are displayed.
The main caveat to keep in mind is that this process involves editing a system view (vSearchListCompany). As such, customisations made to this view may be overwritten following an upgrade.