We experienced a case recently where the BP was having trouble having a custom field show up in their Company Search Screen and Company Grid.
We could not tell from the Logs why it was not showing up. It all seemed to have been setup correctly. The Transation existed correctly in SQL we could see the field was there too. as well as in the vCustom_Edits, Custom_Captions and Custom_Edits tables.
What had originally happened was the BP was not aware that it was a custom field until later during case research and they had done upgrades since which would have possibly broken this custom field. Further more we managed to find out it was setup as a derived field view.
We tried out creating it from scratch in a vanilla instance with these steps.
but the field was still not available from the field list. Doube checked that we used the Derived Field Wizard correctly and made sure the translation was in fact there.
Tried again on newer instances of CRM just to make sure and had no luck.
updated the vSummaryCompany in Admin / Customisation / Company / Views
CREATE VIEW vSummaryCompany AS
SELECT
RTRIM(
ISNULL(Pers_FirstName, '')
) + ' ' + RTRIM(
ISNULL(Pers_LastName, '')
) AS Pers_FullName,
RTRIM(
ISNULL(User_FirstName, '')
) + ' ' + RTRIM(
ISNULL(User_LastName, '')
) AS User_Name,
vAddress.*,
vPersonPE.*,
vCompanyPE.*,
(
select
top 1 convert(datetime, comm_datetime)
from
comm_link
inner join communication on cmli_comm_communicationid = comm_communicationid
where
cmli_comm_companyid = comp_companyid
order by
comm_datetime desc
) as Comp_LastCommunication,
Users.*,
Account.*
FROM
vCompanyPE
LEFT JOIN vAddress ON Comp_PrimaryAddressId = Addr_AddressId
LEFT JOIN vPersonPE ON Comp_PrimaryPersonId = Pers_PersonId
AND Pers_Deleted IS NULL
LEFT JOIN Users ON Comp_PrimaryUserId = User_UserId
LEFT JOIN Account ON Comp_PrimaryAccountId = Acc_AccountId
WHERE
Comp_Deleted IS NULL
I've then ran the Derived Field wizard/component and created the field.
https://community.sagecrm.com/partner_community/m/example_components__developer_resources/28771.aspx
With details as followed
This creates the field in CRM and metadata as confirmed by:
select * from custom_edits
where colp_colname in ('case_color', 'comp_lastcommunication')
select * from custom_captions
where capt_family = 'colnames'
and capt_code in ('case_color', 'comp_lastcommunication')
However, after metadata refresh and restarting machines, I still cant see that new field in Company / Lists / Company Grid or Company / Screens / CompanySearchBox.
Turns out the issue was in the Custom_Edit table.
The CreatedBy and CreatedData were NULL. Seems that the grid edit needed those two fields set before it would display it in the drop downlist for choice.
Very odd for those two fields to drop off from the db. Issue fixed though.
The BP ended up updating the entire entry based on an existing DateOnly data field:
update Custom_Edits
set ColP_DataType = 2,
ColP_DataSize = 20,
Colp_TiedFields = ',',
Colp_ssViewField =',',
ColP_CreatedBy = 1,
ColP_CreatedDate = '2024-05-10 10:22:17.000',
ColP_System = NULL
where ColP_ColName = 'Comp_LastCommunication'