HI, Does anyone know if there is a way SGLT will connect to attendance and send the SGLeader a report when a member has missed three times in a row?
KimBland, are you wanting to have the system send emails to the group leader, or just provide them access to this type of information through your SGLT? Also, are you wanting them to see a list of all the people in their class that might meet that criteria, or are you thinking of notifying them every time someone misses that 3rd meeting?
The top goal would be to have the system email the group leader. I was thinking of having a list once a month emailed to the leader showing all the people in the class that meet that criteria for that month.
That is getting very fancy. To do that, you would basically need to look at having someone build you a custom Arena Agent that would do the automated emailing for you. If you can settle for the reporting piece, you could expose a public page in your public portal that displays a report grid from query built to pull all those people in. I'm not sure if Shelby's Group Leader Toolbox allows you to add pages to it, and keep the securities tied to the group leader. I know ours does.
In short, the only "free" idea I can offer is to craft a SQL query to pull the people you want, and what you want about those people, put it in a Report Grid From Query module somehow linked to your Arena GLT (the key is making sure the group id is in the URL of the page you put it on, so you can make the "report" group specific). You would then have to figure out the best way to provide permission to that module on that page to only let group leaders of that group see it.Outside of that, you could get closer to what you are actually wanting by looking into getting help from a custom developer. I hear Kingdom First Solutions is the best.
Thank you. You understood my train of thought exactly. Thank you for your great ideas! Now I will have to see how it can be done. :)
We accomplish this as a tab in the group page for the Toolbox. That tab pulls the group id from the URL and shows anyone who has missed three weeks in a row. For security, someone already has to have rights to see that specific group page in order to see the tab on the page that has RGFQ module. The query for the stored procedure is below. This also links to Google maps for directions to their home. For us, this is tied into Sunday School specifically, the occurrences it's looking at for attendance are for SS only. You would be better off removing that part that has the occurrence type 25 in it.
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
CREATE PROC [dbo].[cust_gbc_Missed_Last_Three_Group_Meetings](@group INT)ASSELECT P.person_id,P.person_name,'<a href="tel:' + HOME.phone_number + '">' + HOME.phone_number + '</a>' AS [Phone],'<a href="tel:' + CELL.phone_number + '">' + CELL.phone_number + '</a>' AS [Cell],(CASE CELL.phone_numberWHEN '' THEN ''ELSE '<a href="sms:' + CELL.phone_number + '"><img src="./images/textbutton.png"></a>'END) AS [Text],'<a href="mailto:' + dbo.core_funct_primary_email(P.person_id) + '">' + dbo.core_funct_primary_email(P.person_id) + '</a>' AS [Email],'<a href="maps.google.com/maps + A.[street_address_1] + ',' + A.[city] + ',' + A.[state] + '&key=AIzaSyChhYcJ8fzS7PSaUjDGUPOxr8NXis5QDH0&saddr=%21Current%20Location%21" target="_blank">' + A.[street_address_1] + ', ' + A.[city] + '</a>' AS [Address]FROM core_v_merge_fields PLEFT OUTER JOIN smgp_member MEM on MEM.person_id=P.person_id LEFT OUTER JOIN core_person_phone HOME ON HOME.[person_id] = P.[person_id] and HOME.[phone_luid] = 276 LEFT OUTER JOIN core_person_phone CELL ON CELL.[person_id] = P.[person_id] and CELL.[phone_luid] = 282 LEFT OUTER JOIN core_person_address PA ON PA.[person_id] = P.[person_id] and PA.[primary_address] = 1 LEFT OUTER JOIN core_address A ON A.[address_id] = PA.[address_id] and PA.[primary_address] = 1 WHERE MEM.role_luid IN (25, 9977, 24) AND MEM.group_id=@groupAND MEM.active=1AND((SELECT COUNT(ATT.person_id) FROM core_occurrence_attendance ATT LEFT OUTER JOIN core_occurrence OCC ON OCC.occurrence_id = ATT.occurrence_id AND OCC.occurrence_type = 25 LEFT OUTER JOIN smgp_group_occurrence GRPOCC ON GRPOCC.occurrence_id = OCC.occurrence_ID WHERE MEM.person_id=ATT.person_id AND MEM.group_id=@group AND ATT.attended = 1 AND GRPOCC.group_id=@group AND (OCC.occurrence_start_time >= GETDATE()-20 AND OCC.occurrence_start_time <= GETDATE()) ) = 0AND(SELECT COUNT(ATT.person_id) FROM core_occurrence_attendance ATT LEFT OUTER JOIN core_occurrence OCC ON OCC.occurrence_id = ATT.occurrence_id AND OCC.occurrence_type = 25 LEFT OUTER JOIN smgp_group_occurrence GRPOCC ON GRPOCC.occurrence_id = OCC.occurrence_ID WHERE MEM.person_id=ATT.person_id AND MEM.group_id=@group AND ATT.attended = 1 AND GRPOCC.group_id=@group AND (OCC.occurrence_start_time >= GETDATE()-27 AND OCC.occurrence_start_time <= GETDATE()-21) )=1)ORDER BY P.person_name