Advanced Techniques for Views
Whenever you need to add custom SQL using the Page Views page, it is often helpful to begin the View on the page where the View exists. Then save the View and go to to edit the View. You should be aware that often once you enter custom SQL into the setup page, that SQL is not editable in the "Form Layout" mode.
Tokens
- dp_UserID: Represents the ID for the User currently authenticated in 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, a filtered page called "My Opportunities" could be created such that 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 will automatically sort 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, fields may be used for filters without appearing in the results. This is accomplished by omitting the field in the SELECT clause. Notice 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, this is accomplished by deleting the field from the Field List in the SQL Layout tab:
The result in the Advanced Search Tool is a grayed-out field with a comparison.
Default Field List (Filter Only View)
To use the same field list as the page (All Record view), you can clear the Field List in the SQL Layout: