Building Power App using Dataverse tables (multiple data source)

[Rados]

Hi all,

We want to make Power App in Microsoft Teams using multiple Dataverse tables. We have one Dataverse table "Stores", which contains columns Store and Email, and another Dataverse table "Employees", which contains columns Store, First name, and Last name. These two tables should be paired using column Store.  When the user (in this case store from the Store column) is logged on Office 365, user should see just employees from that store.

For example, if O365 account is logged on with email address store1@example.com (Email column), and that store has 10 employees, in the Power App should be viewed first and last names of those ten employees in that store. If another store is logged on O365, employees from that store should be viewed in the Power App.

Does anybody have experience with that?

[dpoggemann]

Hi @Rados ,

You would be able to accomplish your desire here by utilizing galleries and filters but this is not really "security" but just controlling what the user will view based on your filtered views.  Microsoft Teams has a much more limited security model then can be put in place for a full Dataverse environment (https://docs.microsoft.com/en-us/power-platform/admin/about-teams-environment#role-assignments) and any member of the team (except guests) would be able to see all the store employee records from a security perspective.  

Note, from a model perspective, there is a table that is already used for the User logged in called "User" and you could relate this User table to the Employee table (add the User as a Lookup column) and then it will make it easier to filter etc.  Example video demonstrating the filtering on the current user in a Power App:  https://youtu.be/-VaADTTPYmg 

Note, Even if you upgraded to the Full Dataverse, something to be aware of is the User table has "Read" permission across the business unit and this can not be set to User level in the role based security.  So, in this situation if you had all the stores, employees, and related users in same business unit, you would need to be conscience of that.  You might want to consider a different business unit per store, depending on your needs...

[bipinshan]

Hi @Rados ,

If I understand correctly, you have Employee entity which has Store lookup field. If yes then you can use filter function to show list of employees based on logged in user email -

Filter(Employees, 'Emergency Contact'.Email=LoggedInUserEmail)

App onstart -> Set(LoggedInUserEmail, User().Email);

Replace 'Emergency Contact' with Store.

Regards,

Bipin Kumar
Follow my Blog: https://xrmdynamicscrm.wordpress.com/

Kudos and Accept my answer if helpful!

[Rados]

Thank you both, for trying to help. @bipinshan, yes. Employees table has list of employees and stores, but Email column is in another table Stores. When the store is logged in O365 account, that store needs to look up into Stores table, pair with email address in column Email, pick up store from that same table, and collect all employees from table Employees who belong to that store.

[Rados · Accepted solution]

Just to notice you, we have found a solution. As a workaround we have used Power Automate to pair Store columns in both tables, then take data from column Email in dataverse table Stores, and send that data to the new column Email in table Employees. When the user (store in this case) is logged on, app will show only employees who work in that store.