When creating a new query within a portal, a "Portal Identity" filter is automatically added. This filter is intended to limit the query results based on the record currently logged into the portal.
Sometimes, depending on the query base used and the intent of the query, this filter may need to be adjusted, removed, or replaced with a different custom SQL snippet. Therefore, you should always review the Portal Identity filter whenever you are building your portal queries to make sure you are filtering correctly. As mentioned in the Portal Project Management article, the person on your team maintaining your portals should be comfortable with adding and editing custom SQL.
Within the custom SQL "Portal Identity" filter, a variable of @identity is used.
@identity is a global portal variable that is the GUID of the record currently logged into the portal. If the portal uses Application security, @identity is the GUID of the applicant record. If the portal uses User security, @identity is the GUID of the user.
When the portal security and the query base are a match (ex: an application-based query in an Application security portal), the "Portal Identity" filter will typically not need to be changed, as the query is usually pulling information about the record logged into the portal. However, when the portal security and the query base do not match (ex: a test scores based query in an Application security portal), the "Portal Identity" filter will need to be changed to return the correct results.
When editing the "Portal Identity" filter, it is important to determine who or what the query results belong to. In the case of a Test Scores-based query in an Application security portal, the test scores belong to the person associated with the application.
As you create queries with Configurable Joins, you'll get a better sense of how the tables are connected, which will make adjusting this filter even easier!
If a User security portal needs to pull a list of athletes based on custom roles, a custom SQL snippet with a subquery is needed. In this scenario, we need to pull the list of roles for the user logged into the portal and then compare those roles with sport prompt values.
If a User security portal needs to pull a list of records based on population permissions, a custom SQL snippet with an existence statement is needed. In this scenario, we need to check the population permissions for the user logged into the portal and then compare those populations with the populations the record is in. In this example, the query uses the Applications base and the filter is checking if the application populations match the user's population permissions.
exists(select * from [user.right] ur
inner join [population] pop on (pop.[population] = ur.[population])
where ([user] = @identity) and (pop.[record] = a__JID_.[id]))
If this was a person-based query and we wanted to check for person populations, the custom SQL snippet would look like this.
When opening a pop-up in a portal, a parameter is passed into the URL, typically the GUID of the record clicked. Since the information needed to populate the merge fields in the pop-up is related to the record clicked, rather than the user logged into the portal, the @identity variable is not needed. Instead, a custom SQL snippet referencing the parameter is used.