Question

Photo of Rainelle Ellerbe

0

Automatic Pending Person report

2

Is it possible to create automatic reports or scheduled reports? I would like to have an email or notification on my dashboard that people are pending versus manually checking the report

  • Photo of Michael Garrison

    1

    To e-mail this list to yourself, you can use a Workflow (to build and send the e-mail). To trigger the workflow on a schedule, you'll then use a job to run the workflow.

    We're going to have to create the list using SQL, but fortunately this is a pretty easy query.

    If you want a list of people marked either "Pending" or with connection status of "Web Prospect", you can use this query:

    SELECT
        p.[Id]
        ,p.[NickName] + ' ' + p.[LastName] "Name"
    FROM
        [Person] p
    Where
        [RecordStatusValueId] = 5 -- Pending
        OR [ConnectionStatusValueId] = 67 -- Web Prospect

    (If you want JUST pending people like the stock report, you can omit that last line).


    OK, let's get this e-mailed to you.

    Start by creating the workflow. Call it whatever makes sense to you.

    Create a Workflow Attribute called "list". (if you change the key to anything but "list", be sure to update that name in the email at the end). Use attribute type "Memo" or "Text" (memo would make it easier to troubleshoot if you were building this workflow from scratch).

    This workflow doesn't need to be persisted, so uncheck "Persist Automatically".


    Great, now add your first action. Call it something like "Get list". Set it to type "SQL Run" (which you'll find under Utility)

    This action sets any workflow attribute to a single value returned by a SQL query. So we need to get the above query to return a nicely-formatted list of names and IDs as a single row.


    Here's how we're going to do it:

    Running For XML auto would return all the results as a single row, formatted like

    <p Id="123" Name="Ted Decker"/><p Id="124" Name="Cindy Decker"/>

    But that wouldn't show up in an e-mail. So we're going to find-and-replace certain strings (marked in gray in Ted's record above) with a link that will be useful in your e-mail


    So use this query in your SQL Run action:

    SELECT (
    REPLACE(
        REPLACE(
            REPLACE(
                (
SELECT
    p.[Id]
    ,p.[NickName] + ' ' + p.[LastName] "Name"
FROM
    [Person] p
Where
    [RecordStatusValueId] = 5 -- Pending
    OR [ConnectionStatusValueId] = 67 -- Web Prospect

For XML auto)
                ,'<p Id="','<a href="{{ 'Global' | Attribute:'InternalApplicationRoot' }}PersonDuplicate/'
            )
            ,'" Name="','">'
        )
        ,'"/>','</a><br />'
    )
)

    You can still identify the original query we started with in there; feel free to make edits as you see fit. Just be sure you always return "Id" and "Name" or you'll have to redo the replace lines =)

    This will output a single row similar to this:

    <a href="http://rock.rocksolidchurchdemo.com/PersonDuplicate/123">Ted Decker</a><br /><a href="http://rock.rocksolidchurchdemo.com/PersonDuplicate/124">Cindy Decker</a><br />


    Now that's a string we can use in the email body!

    So tell the action to store the results in your "list" attribute.


    Now add a second action

    This action will be "Send Email" (find it under "Communicate").

    Address it to the person or group you wish to receive it and set the subject line.

    Use this for the email body:

    {{ 'Global' | Attribute:'EmailHeader' }}
    Here is the list of person records in Rock which are pending or marked as "web prospect":
    {{ Workflow | Attribute:'list' }}
    {{ 'Global' | Attribute:'EmailFooter' }}

    Now save the workflow

    There you go; whenever this workflow is run, the people you told it to send the email to will receive a nicely-formatted list of people marked as pending or as web prospects.

    EmailPendingPeople-Workflow.png


    One last question: how to get it to run on a schedule?


    For this we'll use a "Run Workflow" job like I said. So go to "Admin Tools > System Settings > Jobs Administration".

    Scroll to the bottom of the grid and click on the (+) button to create a new job.

    Give the job a name you'll be able to identify later, and make sure it's "Active". Turn notifications off, and select Rock.Jobs.LaunchWorkflow as the Job Type. Now you'll be able to pick your new workflow from the list. Finally, use CronMaker to generate your schedule. For instance, 0 0 20 1/1 * ? * would run every evening at 8pm

    Now save the job. You can run it on demand if you want to make sure it'll work, but otherwise you'll get an email as scheduled every night.


    Hope that helps!

  • Photo of Michael Garrison

    1

    You can also add the original SQL to a Dynamic Data block on your dashboard and use some formatted output like:

    <div class="panel panel-block">
    <div class="panel-heading">
        <h4 class="panel-title">Pending people</h4>
    </div>
    <div class="panel-body">
        <ul class="list-group list-group-panel">{% for row in rows %}
            <li class="list-group-item"><a href="/PersonDuplicate/{{ row.Id }}">{{ row.Name }}</a></li>
        {% endfor %}</ul>
    </div>
</div>

    to get the list to live right on your dashboard so you see it whenever you log in.