Multiple phone numbers and reports

  • Hi

    Our Sage CRM 7.1.c System has been customised to suit our specific requirements with regards to logging information about learners.

    Since the update to 7.1.c i have noticed that phone numbers are stored in a separate entity which means people can have more than one type of phone number.

    We have created a contact report which shows learner information as well as a contact telephone number. some learners have more that one number are getting duplicates on the report.

    Other reports run from the same view, but don't include a contact number on the report, but its showing the particular people twice.

    Is there any way to not have duplicate information on the report an only display one of the phone numbers.

    Hope that makes sense

    Thanks

    Adam

  • Adam

    Have a look at the core views vPersonPE and vCompanyPE.  You will see that they return the company and person details with the main phone/email information.  They behave like the 'old' vPerson and vCompany views when those tables contains the denormalised phone/email data.

    You should be able to reference these views within your own views in order to return only the rows you need.  


  • Hi Jeff.

    Ive included the view you mentioned, it kind of works however it only seems to display a phone number if it is recorded as the persons busines number.

    We log learners mobile numbers as mobiles and there is a hell of a lot to change if needed. I dont suppose its possible to chnage the view to display mobile numbers, or if its possible to include an statement that just selects any number recorded for a person (if multiple numbers are recorded) and just display the one.

    Thanks

    Adam

  • Bump:

    This is the kind if thing I am looking for too

  • Hi There,

    Here is what you can use to return mobile, home and business phone number for person.

    select p.pers_firstname,p.pers_lastname, phm.phon_Number as Mobile, phh.phon_Number as Home,phb.phon_Number as Business

    from Person p

    LEFT JOIN PhoneLink plm

    on p.Pers_PersonId = plm.PLink_RecordId AND plm.Plink_Type = 'Mobile' AND plm.PLink_EntityId = '13' AND plm.Plink_deleted IS NULL

    LEFT JOIN Phone phm ON plm.PLink_PhoneId = phm.Phon_PhoneId AND phm.phon_deleted IS NULL

    LEFT JOIN PhoneLink plh

    on p.Pers_PersonId = plh.PLink_RecordId AND plh.Plink_Type = 'Home' AND plh.PLink_EntityId = '13' AND plh.Plink_deleted IS NULL

    LEFT JOIN Phone phh ON plh.PLink_PhoneId = phh.Phon_PhoneId AND phh.phon_deleted IS NULL

    LEFT JOIN PhoneLink plb

    on p.Pers_PersonId = plb.PLink_RecordId AND plb.Plink_Type = 'Business' AND plb.PLink_EntityId = '13' AND plb.Plink_deleted IS NULL

    LEFT JOIN Phone phb ON plb.PLink_PhoneId = phb.Phon_PhoneId AND phb.phon_deleted IS NULL

    thanks,

    Parth