NOTE: before reading this article you should review Portal Identity Filters. You will need to know what @identity is, and how it relates to User Roles.

As stated in the Portal Identity Filter article, @identity is a global portal variable that is the GUID of the record currently logged into the portal. 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. 

What is this custom SQL doing?

In the above SQL, we are matching the GUID of the user logged into the Athletics portal using the @identity variable with the GUID of a role user who has a role that starts with 'Coach - '. We are also joining to lookup prompt on the [sports] table,  so we can then check to see if the sports prompt matches on the second part of the role name after the dash in 'Coach - '. This means when building out your Coach roles they must match the following naming convention:

Coach - <>

For example, if you have a sport of "Basketball", the role would be "Coach - Basketball."

Coach Roles by Gender

There may be times when your coach roles are divided by gender. It's good to think ahead while you are building out your sports prompts if this will be the case. Once again, your Coach roles will need to exactly match the sports prompts you've created. If your sport prompts will look like "Women's Basketball" then the role will be "Coach - Women's Basketball".

NOTE: If you deviate away from this convention, the Service Desk may not be able to assist you with troubleshooting. For customization of this filter, you will need to reach out to a Preferred Partner or the Community Forums.