Hi,
So I change the view below to link to PhoneLink and Phone table to get the person mobile number. Then I put the [MobileNumber] into Person Search Screen.
But it disappear from the person search screen some time, metadata refresh bring it back though.
May I know where went wrong?
CREATE VIEW vSearchListPerson 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_FaxCountryCode as Comp_FaxCountryCode,
epd_comp.epd_FaxAreaCode as Comp_FaxAreaCode, epd_comp.epd_FaxNumber as Comp_FaxNumber, Person.*, Company.*,
Address.* ,phone.Phon_Number as [MobileNumber]
FROM Person
LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID
LEFT JOIN Company ON Pers_CompanyId = Comp_CompanyId AND Comp_Deleted IS NULL
LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID
LEFT JOIN Address ON Pers_PrimaryAddressId = Addr_AddressId AND Addr_Deleted IS NULL
LEFT JOIN PhoneLink on PLink_EntityID=13 AND PLink_RecordID=Pers_PersonId AND PLink_Type='mobile' AND PLink_Deleted IS NULL
LEFT JOIN Phone on PLink_PhoneId=phone.Phon_PhoneId AND Phon_Deleted IS NULL
WHERE Pers_Deleted IS NULL