Question

Photo of Dan Abbuhl

0

Data view for people with no phone number in Rock ? How to filter?

3

I need to make a data view for people in the system that has no phone number in the system.

No Mobil, No Home, No Work.

I have started it but can't see how to filter for phone number.

I have the data view:

Applies to Person

Filter Type: Person Fields

At this point, I can't filter for any of the phone numbers.


Let me know what I'm doing wrong.

  • Photo of Dan Abbuhl

    0

    So here is how I was able to get what I needed.

    I created a data view that included all people in a group.  This produced a long list with only Name & Email.

    I then wanted to add the phone do the data view but couldn't figure out how to add this info.

    I then went to Tools > Reports and made a report that used the data view that I had made.

    From the report, I was able have the report add fields, Family name, name, address, email, phone number mobile, phone number home, phone number work.

    then when I produced the report, it gave me everything I needed.

    • Michael Garrison

      Michael Garrison

      Just to close the loop on this Dan (for others in the future wanting to do the same thing), this didn't output JUST a list of people with no numbers, right? Did you visually scan for people with no numbers or export to Excel to filter for rows where those boxes were all empty?

      • Dan Abbuhl

        Dan Abbuhl

        The data view was the foundation that enabled me to create the report. When I made the report, I used the data view and then had the report give me all the info. If you can't get it, call me at 704-635-9381 and I can walk you thru it or do a facetime and let you see what I'm doing. The report gave me all the people in the data view with the information and the numbers and where thee wasn't a number, the field is blank.

        • Michael Garrison

          Michael Garrison

          Dan, I get it. I'm only trying to document what you did for the "next" person to have this question. Your original question was how to get a report of ONLY people with NO phone numbers. The report you ended up with doesn't do that, but since it shows you all their phone numbers, it allows YOU to identify the people who don't have phone numbers by looking for people with blank cells where their phone numbers would otherwise be.


          Is that correct?

          • Dan Abbuhl

            Dan Abbuhl

            That's right. It created a report with all people and each persons phone number. It wasn't exactly what was asked for but it will do for what they needed.

          • Photo of Thomas Stephens

            0

            This is not going to be as straight forward as you had hoped. Phone numbers are stored in the [PhoneNumber] table. The [PersonId] field in the phone number table holds the record key for the person who the number belongs. So, you would be interested in a PersonId that is NOT in the [PhoneNumber] table. Without playing around with it, I am not sure how it would be done. One idea is maybe a data view of all people (a person DV) and a dataview of phone numbers (phone number DV). Then you are interested in people that are not in the phone number dataview.

          • Photo of Michael Garrison

            0

            In the future, and if you're open to using a DynamicData block, the following SQL will accomplish what you originally requested:

            SELECT
    p.[Id]
    ,p.[NickName] + ' ' + p.[LastName] "Name"
FROM
    [Person] p
    LEFT JOIN [PhoneNumber] pn ON pn.[PersonId] = p.[Id]
WHERE
    pn.[PersonId] IS NULL
                OR pn.[Number] = ''