When you create a new query in a portal, you may notice there’s always one custom SQL filter included by default, called Portal Identity.

When you select this filter, you’ll find that it includes the following SQL statement:

(a.[id] = @identity)

This statement filters the query results depending on the identity of the record logged in to the portal.

There are situations where you may need to update this filter. This usually happens when the scope of the portal changes or doesn’t match your query.

You can use the @identity variable with Configurable Joins queries to replace the default SQL filter in a portal. We show you how to do that in this article.

The @identity variable

@identity represents the GUID of the record currently logged into the portal.

For example:

• If your portal employs the Application security setting, @identity is the GUID of the applicant record.

• If the portal employs the User security setting, @identity is the GUID of the user.

When the portal’s security setting and the query base do not match, as when you create a query on the test scores base in an Application security portal, this @identity variable won’t correspond to the right GUID, and the filter must be modified to return the right results.

When you don’t need a portal identity filter

When you select a pop-up in a portal, a parameter is passed into the URL, typically the GUID of the selected record.

Since the information needed to populate merge fields in the pop-up is related to the selected record, rather than the user logged into the portal, the @identity variable is not necessary. Instead, custom SQL referencing the parameter is used.

You also don’t need @identity if you just need to reference parameters passed in from a portal: see Filter-based Searching in Portals.

Editing the portal identity filter

How you approach this depends on whether the security setting of your portal matches your query base:

Security type matches the query base

1. Go to the portal query.

2. Select Filter.

3. Select the GUID filter that corresponds to your query base and security type.

4. In the GUID field, enter @identity.

   

5. Select Save.

Security type doesn’t match the query base

If your query uses a different query base than your portal’s security setting, you must use joins to reach the correct table corresponding to the portal security type. To do this, you must determine the join path to the target base.

In an example of a query on the test score base in an Application security portal, the test scores belong to the person record that is associated with the application. So, you’d first create a join to Person, then another join from Person to Application.

📖 Further reading: Joins

To create a new query on the Test Scores base:

1. In a portal, select New Query.

2. Configure the following settings:

   • Type: Configurable Joins

   • Category: Related

   • Base: Test

3. Select Save.

4. In the query, select Join.

5. Select Person.

6. Select Save.

7. Select Subquery Filter.

8. Select Join.

9. Select Applications.

10. Select Save.

11. Still in the subquery, select Filter.

12. Under Applications, select GUID.

13. In the GUID field, enter @identity.
    

14. Select Save.

@identity based on user population permissions

When administratively logged in to Slate, “by Population” queries limit results to only records within populations that the user has access to.

However, these queries do not function within the portal tool. Instead, the query must manually filter for specific populations based on the @identity variable. This can be done using a subquery filter.

The approach described here only works for populations that are assigned via role, not for populations assigned to individual permissions. This follows our best practice of using population-aware roles to assign populations to users.

We can build our subquery filter through the following join path: to the record’s populations, looking up that population, joining to all of the roles with that population, and then to all users with those roles.

We then use the User GUID export to compare to the @identity variable to make sure the logged-in user has a role assigned to one of the record’s populations.

1. Go to the portal query.

2. Select Subquery Filter.

3. From the Aggregate list, select Formula.

4. In the subquery filter, add the following joins, in order:

   • From the base to Populations

   • From Populations to Lookup Population

   • From Lookup Population to Role User

   • From Role User to User

5. Select Export.

6. Under User, select GUID.

7. Select Save.

8. In the Formula field, enter @User-GUID = @identity.
   

9. Select Save.