Skip to main content

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

Access Web App My Projects View
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.
Access Web App Templates
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.
On Start Macro
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().
On Start Macro Details
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.
Data macro
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 UI Macro
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
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
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.
Labels:

Popular posts from this blog

SharePoint | Set default value of date field to last day of the month

One of the things, that I needed to do recently is find a way to set the default value of a date field to the last day of the month. Though some content were really helpful in getting me on the way to solving the problem, I thought I'd post the formula required to make it easier for others looking to do the same in the future. What I wanted to do  The steps below describe what I wanted to achieve: User uploads a file into a document library A date column will automatically be populated with the date representing the end of the month (calculated based on the date the file was uploaded) How do we do this? Open your column settings for your date field Under the Additional Column Settings, set the default value option to Calculated Value Enter the the formula below: =(DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY("1-Apr-2008"))) - (1) Click save and that's all you need to do What does the formula above do? The formula above is quite simple.
Read more

Only show a control to an employee's manager in PowerApps (using SharePoint as a data source)

The Problem In my previous blog post , I wrote a basic tutorial on configuring PowerApps to conditionally show/hide and make mandatory/optional a control based on the answer provided in a previous control. In this blog post we will expand on that scenario to create a form that only shows a control called approval status to a user if that logged in user is the manager of the user specified in the employee control. This means when the manager is using the application, they can change the status to approved. Meanwhile, a user that is not a manager of the person specified in the employee cannot change the status to approved. The Solution Aside from containing a variety of native functions, PowerApps also allow us to add connections to add functionalities into our app. In this case we will add the Office 365 Users connection so that we can get information on: The user that is logged on The manager of the user specified in the employee control Note: Whilst this soluti
Read more

Uploading a file from a SharePoint document library into an Azure Blob Storage with Microsoft Flow (or Logic App)

The Problem One of the things that I've been experimenting with lately is Microsoft Flow . The service allows you to build process automation to facilitate transfer of information from one system to another easily. One scenario I wanted to try out is to be able to copy/move a file from SharePoint to an Azure Blob Storage. There are a number of reasons that you may want to do this: Archive files that are no longer needed Upload a copy of a file (usually an image) into a Blob Storage so that images can be hosted in a CDN to optimize page load performance In the past doing something like this would have required some form of custom development e.g. a remote event receiver. However the introduction of Microsoft Flow and Logic Apps has created another alternative that is worth exploring.... Update 30/09/16:  If you are looking to put your assets into a CDN to make it load faster, then it's worth checking out the new Preview Release  Office 365 Public Content Delivery
Read more