Advanced Techniques for Views
Explore advanced techniques for managing and customizing views in MinistryPlatform, including the use of custom SQL, tokens, and specific SQL functionalities to enhance user interaction and data handling.
When you need to add custom SQL using the Page Views page, it is often helpful to begin on the page where the view exists. Then save the view, and go to to edit the view.
Tokens
- dp_UserID: Represents the ID for the user currently logged in to the Platform.
- dp_ParentID: Represents the ID for the parent record of any sub-page record.
- dp_DomainTime: Represents the current time according to the time zone set in the domain record.
For example, you could create a filtered page called "My Opportunities" where users with access to that page would only see the opportunities for which they are the contact person. The filter clause for such a page is:
Contact_Person_Table.User_Account = dp_UserIDSort Order
The view automatically sorts by the first column unless you specify a sort order. To specify a sort order, list the field names in a comma delimited list. If you are using a subquery, use the alias assigned in the field list.
Filter Only Fields
In SQL, you can use fields for filters without them displaying in the results. To do this, omit the field from the SELECT clause. In the following example, notice the date of birth is in the WHERE clause but not in the SELECT clause:
SELECT Contact_ID, Display_Name
FROM Contacts
WHERE Date_of_Birth IS NOT NULL
In the Advanced Search Tool, delete the field from the Field List field in the SQL Layout tab:
As a result, the field's checkbox is deselected with a comparison in the Form Layout tab:
Default Field List (Filter Only View)
To use the same field list as the page (All Record view), you can clear the Field List field in the SQL Layout tab: