0 Action List (call, contact, etc) Shared by Thomas Stephens, Northview Church 3 years ago 8.0 Communications, Prayer Advanced We had the need to create a call list. Note, Rock came out with a solution but it wasn't out yet and it didn't fit our need exactly. That tool is the calling campaign plugin. I would encourage you to investigate that as well. You should still read over this as it may provide ideas or things that may help you with other problems you have to solve. This tool uses SQL to pull a list of families. You will have to work with your leaders to determine what families should be pulled in. You will not be able to just copy and past my SQL. How it works One web page is used to display the family to call. A URL parameter is used to narrow it down to a campus. I am randomly displaying one family from the SQL query to call. That means several people can be working the list at the same time. The odds are, the same family won't be displayed to two people at the same time until the list gets very small. I created a few entity attributes to save the data. All for the group entity, specifically, the family group type. I have three buttons that live on the page as well. The are 'Connected - Conversation', 'Connected - Voicemail', and 'Did Not Connect'. These buttons navigate to another page where a workflow block is placed. With URL parameters, I am setting workflow attributes. That workflow sets the group (family) attributes and then redirects them back to the page where a new random family is displayed. See a screen shot below for an example of what the page looks like. Items needed to be created Entity Attributes: System Settings -> Entity Attributes. See a screen shot below for a screen shot of my group attribute. I circled the key items that create this attribute on the family group. Clicking 'Show In Grid' will display it on a person's profile in the group area (the area where the address is displayed). How many of these you create is up to you. I would say at least one is mandatory to keep track of who had been called. SQL Query: We are using two queries. One for the entire campus call list and then another for an engaged list. To us, that means on a team, in a group or an eRA. All campus call list. Notice the campus Id is set from a lava variable. I will show you how we get that later. We are pulling active people with a certain connection status. {% sql return:'rows' %} SELECT TOP 1 p.PrimaryFamilyId as Id FROM Person p JOIN GroupMember gm ON p.Id=gm.PersonId WHERE p.ConnectionStatusValueId IN (981,65,66) AND p.RecordTypeValueId = 1 AND gm.GroupRoleId = 3 AND p.PrimaryCampusId = {{campusId}} AND p.Id NOT IN ( SELECT p.Id FROM Person p JOIN AttributeValue av ON p.PrimaryFamilyId=av.EntityId AND av.AttributeId = 39835 AND av.ValueAsBoolean = 1 ) AND (p.Id IN ( SELECT DISTINCT(p.Id) FROM Person p JOIN GroupMember gm ON gm.PersonId=p.Id JOIN [Group] g ON g.Id=gm.GroupId WHERE g.IsActive = 1 AND g.IsArchived = 0 AND gm.GroupMemberStatus = 1 AND g.GroupTypeId IN (124,215,214,72,80,81,82,83,84,85,86,87,157,159,162,169,168,170,175,176) ) OR p.Id IN ( SELECT p.Id FROM Person p JOIN AttributeValue av ON p.PrimaryFamilyId=av.EntityId AND av.AttributeId = 1783 AND av.ValueAsBoolean = 1 ) ) ORDER BY NEWID() {% endsql %} The following query is the more broad one that doesn't factor in on a team, in a group, or eRA. Just that they haven't been called and are a certain connection status. {% sql return:'total' %} SELECT COUNT(DISTINCT(p.PrimaryFamilyId)) as Sum FROM Person p JOIN GroupMember gm ON p.Id=gm.PersonId WHERE p.ConnectionStatusValueId IN (981,65,66) AND p.RecordTypeValueId = 1 AND gm.GroupRoleId = 3 AND p.PrimaryCampusId = {{campusId}} AND p.Id NOT IN ( SELECT p.Id FROM Person p JOIN AttributeValue av ON p.PrimaryFamilyId=av.EntityId AND av.AttributeId = 39835 AND av.ValueAsBoolean = 1 ) ORDER BY NEWID() {% endsql %} SQL Result Display: Here is my HTML for taking the results, which are group Ids and presenting the data nicely. <h3>{{campusText}} List</h3> {% for row in total %} <b>{{ row.Sum }} families not contacted</b> {% endfor %} <br> <table class="table table-bordered table-striped table-hover table-sort"> <tr> <th>Name</th> <th>Mobile</th> <th>Home</th> </tr> {% for row in rows %} {% assign group = row.Id | GroupById | Property:'Guid' %} {% groupmember where:'GroupId == {{row.Id}} && GroupRoleId == 3' %} {% for gm in groupmemberItems %} {% assign p = gm.PersonId | PersonById %} <tr> <td> <a href="https://rock.northviewchurch.us/person/{{p.Id}}" target="_blank">{{ p.FirstName }} {{ p.LastName }}</a> </td> <td> {{p | PhoneNumber:'Mobile'}} </td> <td> {{p | PhoneNumber:'Home'}} </td> </tr> {% endfor %} {% endgroupmember %} {% endfor %} </table> Now to add the questions to the bottom, like notes or greatest area of prayer. Note the ids here are important <form> <label for="note">Notes:</label><br> <input type="text" id="note" name="note" size="100"><br><br> <label for="note">Area of greatest need for prayer:</label><br> <input type="checkbox" id="job" name="job"> <label for="job">Job</label><br> <input type="checkbox" id="finances" name="finances"> <label for="finances">Finances</label><br> <input type="checkbox" id="health" name="health"> <label for="health">Health</label> </form> Next for the buttons <a id="connected" title="Connected" href="#" onclick="Conntect();return false;" class="btn btn-primary btn-size">Connected - Conversation</a> <a id="connected" title="Connected" href="#" onclick="Voice();return false;" class="btn btn-primary btn-size">Connected - Voicemail</a><br><br> <a id="connected" title="Connected" href="#" onclick="NoConnect();return false;" class="btn btn-primary btn-size">Did Not Connect</a> Notice the buttons all have an onclick event that calls a function. The function will create the needed URL to navigate to (where that workflow lives). These functions also get the HTML input values and gets them ready to pass as URL parameters. The goal here was whatever I pass in the URL won't have to be touched in the workflow. I can directly set the group attribute without having to modify or format the value I pass in. Those look like this: <script language="javascript" type="text/javascript"> function Conntect() { var need = ''; var note = document.getElementById("note").value; var temp = document.getElementById("job").checked; if (temp) { need = "1,"; } var temp = document.getElementById("finances").checked; if (temp) { var str2 = "2,"; need = need.concat(str2); } var temp = document.getElementById("health").checked; if (temp) { var str2 = "3"; need = need.concat(str2); } window.location.href = "https://northviewchurch.us/page/2505?Person={{CurrentPerson | Property:'PrimaryAlias.Guid'}}&Group={{group}}&Success=True&Campus={{campusText}}¬es="+note+"&need="+need+"&type=1"; } function NoConnect() { var note = document.getElementById("note").value; window.location.href = "https://northviewchurch.us/page/2505?Person={{CurrentPerson | Property:'PrimaryAlias.Guid'}}&Group={{group}}&Success=False&Campus={{campusText}}¬es="+note; } function Voice() { var need = ''; var note = document.getElementById("note").value; var temp = document.getElementById("job").checked; if (temp) { need = "1,"; } var temp = document.getElementById("finances").checked; if (temp) { var str2 = "2,"; need = need.concat(str2); } var temp = document.getElementById("health").checked; if (temp) { var str2 = "3"; need = need.concat(str2); } window.location.href = "https://northviewchurch.us/page/2505?Person={{CurrentPerson | Property:'PrimaryAlias.Guid'}}&Group={{group}}&Success=True&Campus={{campusText}}¬es="+note+"&need="+need+"&type=2"; } </script> Finally you will need to workflow that sets the group (family) attributes. I have attached that as a file. There are a two important things to note here. 1) That workflow will need to be on a page that your buttons navigate to. You will need to edit the code above to go to that page vs the page I have them going to. 2) The keys to the attributes are very important. If you notice, my URL parameters are named exactly the same. This allows the URL to set the workflow attributes with no work needed from myself. Finally I talked about the URL parameter that sets the campus id. I am using '?campus=carmel' for example. That will allow me to use one page to serve all campuses. {% assign campus = 'Global' | PageParameter:'campus' %} {% case campus %} {% when 'carmel' %} {% assign campusText = 'Carmel' %} {% assign campusId = 1 %} {% when 'fishers' %} {% assign campusText = 'Fishers' %} {% assign campusId = 2 %} {% when 'anderson' %} {% assign campusText = 'Anderson' %} {% assign campusId = 3 %} {% when 'gl' %} {% assign campusText = 'Greatest Lafayette' %} {% assign campusId = 4 %} {% when 'binford' %} {% assign campusText = 'Binford' %} {% assign campusId = 5 %} {% when 'kokomo' %} {% assign campusText = 'Kokomo' %} {% assign campusId = 6 %} {% when 'westfield' %} {% assign campusText = 'Westfield' %} {% assign campusId = 7 %} {% when 'flora' %} {% assign campusText = 'Flora' %} {% assign campusId = 26 %} {% when 'peru' %} {% assign campusText = 'Peru' %} {% assign campusId = 27 %} {% endcase %} Here is my entire HTML block (with SQL enabled) {% assign campus = 'Global' | PageParameter:'campus' %} {% case campus %} {% when 'carmel' %} {% assign campusText = 'Carmel' %} {% assign campusId = 1 %} {% when 'fishers' %} {% assign campusText = 'Fishers' %} {% assign campusId = 2 %} {% when 'anderson' %} {% assign campusText = 'Anderson' %} {% assign campusId = 3 %} {% when 'gl' %} {% assign campusText = 'Greatest Lafayette' %} {% assign campusId = 4 %} {% when 'binford' %} {% assign campusText = 'Binford' %} {% assign campusId = 5 %} {% when 'kokomo' %} {% assign campusText = 'Kokomo' %} {% assign campusId = 6 %} {% when 'westfield' %} {% assign campusText = 'Westfield' %} {% assign campusId = 7 %} {% when 'flora' %} {% assign campusText = 'Flora' %} {% assign campusId = 26 %} {% when 'peru' %} {% assign campusText = 'Peru' %} {% assign campusId = 27 %} {% endcase %} {% sql return:'rows' %} SELECT TOP 1 p.PrimaryFamilyId as Id FROM Person p JOIN GroupMember gm ON p.Id=gm.PersonId WHERE p.ConnectionStatusValueId IN (981,65,66) AND p.RecordTypeValueId = 1 AND gm.GroupRoleId = 3 AND p.PrimaryCampusId = {{campusId}} AND p.Id NOT IN ( SELECT p.Id FROM Person p JOIN AttributeValue av ON p.PrimaryFamilyId=av.EntityId AND av.AttributeId = 39835 AND av.ValueAsBoolean = 1 ) AND (p.Id IN ( SELECT DISTINCT(p.Id) FROM Person p JOIN GroupMember gm ON gm.PersonId=p.Id JOIN [Group] g ON g.Id=gm.GroupId WHERE g.IsActive = 1 AND g.IsArchived = 0 AND gm.GroupMemberStatus = 1 AND g.GroupTypeId IN (124,215,214,72,80,81,82,83,84,85,86,87,157,159,162,169,168,170,175,176) ) OR p.Id IN ( SELECT p.Id FROM Person p JOIN AttributeValue av ON p.PrimaryFamilyId=av.EntityId AND av.AttributeId = 1783 AND av.ValueAsBoolean = 1 ) ) ORDER BY NEWID() {% endsql %} {% sql return:'total' %} SELECT COUNT(DISTINCT(p.PrimaryFamilyId)) as Sum FROM Person p JOIN GroupMember gm ON p.Id=gm.PersonId WHERE p.ConnectionStatusValueId IN (981,65,66) AND p.RecordTypeValueId = 1 AND gm.GroupRoleId = 3 AND p.PrimaryCampusId = {{campusId}} AND p.Id NOT IN ( SELECT p.Id FROM Person p JOIN AttributeValue av ON p.PrimaryFamilyId=av.EntityId AND av.AttributeId = 39835 AND av.ValueAsBoolean = 1 ) ORDER BY NEWID() {% endsql %} <h3>{{campusText}} List</h3> {% for row in total %} <b>{{ row.Sum }} families not contacted</b> {% endfor %} <br> <table class="table table-bordered table-striped table-hover table-sort"> <tr> <th>Name</th> <th>Mobile</th> <th>Home</th> </tr> {% for row in rows %} {% assign group = row.Id | GroupById | Property:'Guid' %} <!-- Name --> {% groupmember where:'GroupId == {{row.Id}} && GroupRoleId == 3' %} {% for gm in groupmemberItems %} {% assign p = gm.PersonId | PersonById %} <tr> <td> <a href="https://rock.northviewchurch.us/person/{{p.Id}}" target="_blank">{{ p.FirstName }} {{ p.LastName }}</a> </td> <td> {{p | PhoneNumber:'Mobile'}} </td> <td> {{p | PhoneNumber:'Home'}} </td> </tr> {% endfor %} {% endgroupmember %} {% endfor %} </table> <br> <form> <label for="note">Notes:</label><br> <input type="text" id="note" name="note" size="100"><br><br> <label for="note">Area of greatest need for prayer:</label><br> <input type="checkbox" id="job" name="job"> <label for="job">Job</label><br> <input type="checkbox" id="finances" name="finances"> <label for="finances">Finances</label><br> <input type="checkbox" id="health" name="health"> <label for="health">Health</label> </form> <br> <br> <a id="connected" title="Connected" href="#" onclick="Conntect();return false;" class="btn btn-primary btn-size">Connected - Conversation</a> <a id="connected" title="Connected" href="#" onclick="Voice();return false;" class="btn btn-primary btn-size">Connected - Voicemail</a><br><br> <a id="connected" title="Connected" href="#" onclick="NoConnect();return false;" class="btn btn-primary btn-size">Did Not Connect</a> <script language="javascript" type="text/javascript"> function Conntect() { var need = ''; var note = document.getElementById("note").value; var temp = document.getElementById("job").checked; if (temp) { need = "1,"; } var temp = document.getElementById("finances").checked; if (temp) { var str2 = "2,"; need = need.concat(str2); } var temp = document.getElementById("health").checked; if (temp) { var str2 = "3"; need = need.concat(str2); } window.location.href = "https://northviewchurch.us/page/2505?Person={{CurrentPerson | Property:'PrimaryAlias.Guid'}}&Group={{group}}&Success=True&Campus={{campusText}}¬es="+note+"&need="+need+"&type=1"; } function NoConnect() { var note = document.getElementById("note").value; window.location.href = "https://northviewchurch.us/page/2505?Person={{CurrentPerson | Property:'PrimaryAlias.Guid'}}&Group={{group}}&Success=False&Campus={{campusText}}¬es="+note; } function Voice() { var need = ''; var note = document.getElementById("note").value; var temp = document.getElementById("job").checked; if (temp) { need = "1,"; } var temp = document.getElementById("finances").checked; if (temp) { var str2 = "2,"; need = need.concat(str2); } var temp = document.getElementById("health").checked; if (temp) { var str2 = "3"; need = need.concat(str2); } window.location.href = "https://northviewchurch.us/page/2505?Person={{CurrentPerson | Property:'PrimaryAlias.Guid'}}&Group={{group}}&Success=True&Campus={{campusText}}¬es="+note+"&need="+need+"&type=2"; } </script> Download File