Access Web App | Filter a view by current user login (deprecated)

My Projects View

Update (April 1 2017): Microsoft has released an update that willl stop the creation of new apps starting June 2017, and shut down any remaining apps by April 2018. See more here

The problem

One of the things I needed to do lately was see if a view within Access Web Apps can be configured to filter data that is related to the logged in user. e.g. as a user I want to be able to view a list of projects where I had been assigned as the project owner.

In SharePoint, this would have been quite easy to achieve as we can create a view and set the filter to {column name} is equal to [me].

Unfortunately Access Web Apps does not quite work this way:

• No people picker control in Access Web Apps - this means we need to store and manage our own list of user data within a table in your Access Web App Database (or find a way to link a table with SharePoint's user profile or hidden user information list).
• Access Web Apps is a relational database - this means the approach to filtering a table view is different from SharePoint. Being relatively new to Access Web Apps, I did some googling.

The most relevant content I have found around filtering the table view by user login has come from the Access Web Apps LinkedIn Group. For those who are familiar with how Access works, I think the information provided in that discussion is sufficient for you and would highly recommend checking the discussion out instead of continuing on with the post below. 

However if like me you are relatively new to Access and Access Web Apps, and would like more in depth content on filtering a table view based on logged in user than what is provided in the LinkedIn discussion thread I posted above, please join me as I share my observations below.

The Steps

Before we start, make sure that you have an Access Web App Project that is already set up with all the tables and columns you need for your application.

• Table you want to create a filtered view - ensure it has a lookup column to your user table. Create a table view (do not use view type of summary) e.g. Assigned to me.
• Table that contains your user list - ensure it has display name and email address

For this experiment, I used the Project Management Template.

Project management template was used for this experiment

High level steps

1. Create an on start macro to get the display name and email address of the currently logged on user.
   Note in this example I used display name, however in a larger user base, you will probably want to use email address to reduce likelihood of the scenario where you have two users with the same name.
2. Create a data macro to query the list containing your users (e.g. Employees List) for the ID of the logged on user.
   One of the key difference between data macros and normal macros is that data macros are used to perform operations directly on your database tables, and allows you to abstract logic away from the user interface. e.g. query or update a table. 
3. Create a UI macro initiate the filtering of the table view you want to filter.
   Note this step is not necessary for this to work as you can combine step 3 and 4 by putting the macros from this step into the on load event. The advantage of this approach is to extract the logic or code from individual table views and make it easier for your to reuse the functionality across multiple views in your table.
4. Modify the on load event of your table view to call the UI Macro you created. 

Walkthrough

1. Create an on start macro

Create a new on start macro which will be responsible for getting the logged on user's display name and email address when your application loads on the browser, and stores it in a variable which can be used by macros in your application.

Create a new On Start Macro

In the on start macro configuration page, we are going to set two variables to an access function called UserDisplayName() and userEmailAddress().

Set variables to store the logged in user's display name and email address.

2. Create a new data macro

Create a new data macro which will be used to query the user table for the ID of the logged in user display name you retrieved from Step 1.

Createa new data macro

Configure your data macro add a parameter for which we can pass the display name of the user. Then add a query action to query your user table.

In my case I queried my employees table, and set a where clause to get the record that matches the display name. I then stored the ID of the record into the return variable so that any UI macro that calls this data macro will get back the ID.

Configure data macro to query your user list

3. Create a UI macro

Create a new macro which will be used to run the data macro to get the ID of the logged in user, before filtering your table view.

Create a UI Macro

Configure your UI macro to start the data macro you created in Step 2. In the input parameter, enter the variable you used to store the logged in user's display name in Step 1 and store the output parameter into a local variable. 

On the next line, add the action to RequeryRecords and configure the where clause. The statement you use in this where clause will depend on whether you used the database table as your table view source or a database query as your table view source:

• Database table as a view source - {Database Table}.{Database Table Column}={Local Variable}
• Database query as a view source -  {Database Query}.{Database Query Column}={Local Variable

Configure UI Macro

4. Modify the on load event of your table view

The last thing on this walkthrough is to set the on load event of your table view to run the UI Macro you have created in step 3.

Configure On Load Event

Next Steps

If you would like to learn more about Access Web Apps, I would certainly recommend checking out Microsoft Access 2013 Inside Out (affiliate link).

Have you tried creating table views that are filtered by the logged in user? Have you found alternative approaches to fulfilling this business requirement? If so please share your experience in the comments below.