home

MinistryPlatform

RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite
RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite

What would you like to know more about?

Show Contents

thumbnailMinistryPlatform

Record Facts

Advanced Record Facts

Want to take your Record Facts to the next level? Use these advanced tips and tricks to empower your staff to serve your people well, and maybe wow them along the way!

Percentages

Want to make it easy for your users to see registrations and Group percentages at-a-glance? Just set Show Percentage to Yes on the Page View Rules record. This is visually illustrated with a fill and is always compared against 100. So, if the value is 25, the Record Fact fill is 25% full. For this to happen, any comparisons must occur in the View and require advanced SQL. The comparison of Target Size and Current Participants must happen in a View, so the View returns a value of 25.

Note: To avoid rounding to 0, the SQL calculation must be CAST as a decimal and then CAST again to drop decimal points.
CASE WHEN Groups.[Target_Size] = 0 THEN NULL ELSE(CAST((SELECT Count(*) FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_IDAND (GP.[End_Date] IS NULL OR GP.[End_Date] > @DomainTime)) AS decimal(6,2))/Groups.[Target_Size]) / 100)AS int) END AS [Current Participants]Copied
Note: Percentage fills help your team visualize event registrations or Group capacity. But remember, you can't divide by 0. We recommend using Mass Assign to add a value or make the fields blank (null) to make sure there are no zeros in your comparisons.

Age Calculations

Showing a Contact or Participant's age as a Record Fact is a no-brainer. If you want to calculate the age of deceased Contacts based on their Date of Death rather than the current date, use this in your Facts View.

(SELECT CASE WHEN C.Contact_Status_ID = 3 THEN (CONVERT(int,CONVERT(char(8),C.Date_of_Death,112))-CONVERT(char(8),Date_of_Birth,112))/10000  ELSE C.__Age END FROM Contacts C WHERE C.Contact_ID = Contacts.Contact_ID) AS [Age]

Combine Values

An advanced or power user can create a record fact that displays milestones a participant has. When baptized, the fact also turns green and displays an icon. This allows the casual user to glance at a list and get all the information they need. Want to really empower that casual user? Set up a view notification to send them a periodic email reminding them to check the list.

((SELECT CASE WHEN EXISTS(SELECT PM.[Participant_Milestone_ID] FROM Participant_Milestones PM WHERE PM.[Participant_ID] = Participants.[Participant_ID] AND PM.[Milestone_ID] = 2)THEN 'Baptized'ELSE 'Not Baptized'END)+  ' +' + CAST((SELECT COUNT(*) FROM Participant_Milestones PM WHERE PM.[Participant_ID] = Participants.[Participant_ID]) AS varchar(15)) ) AS [Milestones] Copied

Dates Without Times

Want to see the date something happened (for example, a birth date, registration date, or Event date) but not see the time? Add a little bit of code before the date field SQL so the Record Fact just displays the date.

Here's an example of removing the date from an Event Start Date Record Fact:

(SELECT CONVERT(date,Events.[Event_Start_Date])) AS [Event Start]
MinistryPlatform

Last updated: November 26, 2024