Question

Photo of Austin Barnett

0

Dataview Question SMS Number Report

2

Hi, I'm working on a Dataview that will show all mobile numbers that are not active for SMS.  Problem is I need to additionally filter it on Active Records.  Here is what I have, but what can I do to make this Dataview (which relates to Phone Number) filter also on Active Recrods.  I'm not scared if someone says it has to be SQL.

Screen Shot 2015-11-04 at 2.21.36 PM.png

  • Photo of Arran France

    0

    As David suggested there's no possible way of doing this through data views at the moment.

    I think this SQL does what you want:

    SELECT p.FirstName, p.LastName, p.Id
    FROM [Person] as p
    JOIN [PersonAlias] as pa on pa.Id = p.Id
    JOIN [PhoneNumber] as pn on pn.PersonId = pa.Id

    WHERE pn.Number != '' AND p.RecordStatusValueId = 3 AND pn.IsMessagingEnabled = 'false'    AND pn.NumberTypeValueId = 12

  • Photo of David Leigh

    0

    If you are referring to the Active status on the Person record, this isn't currently possible so far as I know.
    One way to achieve this might be to add a new Data Filter "Phone Number/Person Data View" that filters for only those phone numbers associated with People from a specified Person Data View. Would that solve your problem?