My Projects View |
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.
For this experiment, I used the Project Management Template.
- 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
- 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. - 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. - 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. - 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 |
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.
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.
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
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.