A query was raised recently about how to add fields, such as Mobile Phone Number, to the My Contacts list block. This query can be answered in 2 parts; firstly how to do retrieve the data we want to display and secondly from where, or from what view, does the List block retrieve it's data.

We already know from previous posts that phone number data is stored in the phone table and the number type for contacts is determined by the translation family link_persphon. So to retrieve a mobile phone number we need to use a SQL statement such as

select phon_areacode+' '+phon_number from phone where phon_type = 'Mobile'

and to return a contact's mobile number is we need to use a SQL statement such as (where the record id for the contact is '34')

select phon_areacode+' '+phon_number from phone join person on phon_personid = pers_personid where phon_type = 'Mobile' and pers_personid = 34

The UserContacts List block gets it's data from the vUserContacts view. If we didn't have this information already available to us, we could determine this for ourselves by examining the metadata definitions;

navigate to

click on the Change column for the list of interest, here UserContactList, and you will see vUserContacts is the view or table to select fields from.

If we wanted to verify that we have examined the correct list, simply change the caption for one of the columns - by putting a script such as the following

Caption='my sample column';

into the CreateScript for one of the fields , saving your changes and then viewing the list to see if the column heading has changed

Putting this all together, to add the contact's mobile to the User Contact list we need to modify the vUserContacts view to also retrieve the mobile number from the phone table.

So, we amend the view (navigate to Admininstration->Customisation->Secondary Entities->User Contacts->Views; select vUserContacts and then select Change) to read as follows (paste this in and select Save)::

CREATE VIEW vUserContacts AS SELECT RTRIM(ISNULL(Pers_PhoneCountryCode, '')) + ' ' +RTRIM(ISNULL(Pers_PhoneAreaCode, '')) + ' ' +RTRIM(ISNULL(Pers_PhoneNumber, '')) AS Pers_PhoneFullNumber,RTRIM(ISNULL(Pers_FaxCountryCode, '')) + ' ' +RTRIM(ISNULL(Pers_FaxAreaCode, '')) + ' ' + RTRIM(ISNULL(Pers_FaxNumber,'')) AS Pers_FaxFullNumber, UserContacts.*, Person.*, Users.*,Company.*, Person_Link.*, Address.*, RTRIM(ISNULL(Phon_AreaCode,''))+' '+RTRIM(ISNULL(Phon_Number, '')) AS MobileNumber FROM Person INNER JOIN UserContacts ON UCnt_PersonID =Pers_PersonID AND UCnt_Deleted IS NULL INNER JOIN Users ON UCnt_UserID =User_UserID AND User_Deleted IS NULL LEFT OUTER JOIN Person_Link ONPeLi_PersonID = UCnt_PersonID AND PeLi_Type IS NULL LEFT OUTER JOINCompany ON PeLi_CompanyID = Comp_CompanyID LEFT OUTER JOIN Address ONPers_PrimaryAddressId = Addr_AddressID LEFT OUTER JOIN Phone ON pers_PersonId = phon_PersonId AND phon_type = 'Mobile' AND phon_DeletedIS Null WHERE Pers_Deleted IS NULL


Now, if you navigate to the Lists tab in User Contacts and select the UserContactList block, you should see an entry ViewField:MobileNumber at the bottom of the drop-down; add this field and when you view the My Contacts list you will see the mobile numbers displayed.

Notice that the column heading displays MobileNumber, because in the view definition above when I combined the Phone AreaCode and Number and named the result MobileNumber. If you want to display different text in the column heading, then add in a Translation

US Translation: Contact Mobile