Answered

Suggestion for Dataverse tables, relationship and architecture s a Role / Access Project

(0) Share

Report

Posted on by ezFlow

100

We are going to create a Canvas App to display Job Roles and Access from a Dataverse tables (Not Unique Role Name as it may exist in defert department as assigned different access). See Below

Role Name	Department	Office
Accounts Administrator	Retail	NY
Accounts Administrator	Property	LA
Administrative Assistant	Retail	TOR
Administrative Assistant	Property	MTL
Coordinator	HR	CHI
System Admin	IT	NY

The Other table will contain the Access for a Role (see Below)

Role Name	Access Category	Access Title	Default Value
Accounts Administrator	Hardware	Type of Computer	MAC
Accounts Administrator	Hardware	Monitors	1 Monitor
Accounts Administrator	Hardware	VOIP	yes
Accounts Administrator	Software	VPN	Yes
Coordinator	Hardware	Type of Computer	Lenovo
Coordinator	Hardware	Monitors	2 Monitors
Coordinator	Hardware	VOIP	No
Coordinator	Software	VPN	No

I'm looking for suggestion on how to Load the data (from Excel) create the tables and relationships.
Canvas App: User can filter by Department and Office to get a unique Role name, and need to display the related access for the Role.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (1)

ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report

Hi @ezFlow,

You can use Dataflows to import the Excel data and it generate the new tables for you.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report
Hi @ezFlow

Looking at your table structure, first of all you might want to think about adding some more table here.

Roles - This would be the unique list of Roles available (Accounts Administrator, Coordinator, System Admin). The Primary Name field here would be unique to each role

Department - Table with all the distinct departments that exist

Office - This would be the distinct office locations where you can define specific fields about that office

Role Stratification - This would be a table that will be used to create the intersect between the role, department, and office.

I would have the Name field be set as the combination of the Role - Department - Location, you could set this in a real time workflow on Create and any change to the Department, Office, or Role columns on the table.

This table would contain the 3 lookups for the Role, Department, Office

Role Access - This table would be the same as it is now and the Role would be a lookup to the Role table. This provides that connection / relationship between the tables

From a loading of the data perspective... I would do the following.

First create all the tables and relationships in Dataverse

Go to https://admin.powerplatform.com and choose "Environments" from navigation on left

Select your environment from the list

Select "Settings" in top navigation for the Environment

Select "Templates" and "Data Import Templates"

You will then be able to download your templates as needed that you can import through Excel

Once you have them ready, you will be able to load using the "Data Management" section on the same page as the Templates and choose "Imports".

Now, based on the defined model above I would do the following:

Load the Roles through import

Load the Departments through import

Load the Offices through import

Load the Role Stratification through Import and set the lookup fields in this import to the values for the Role, Department, and Office. Each of these will have unique values so it will find the record and match appropriately. You use the Primary Name field value to fill in those columns in the import

Same thing for the Role Access table...

That should do it. Hopefully this was helpful in some way.

Was this reply helpful? Yes No
ezFlow 100 on at

Like (0)

Report
Thank you, for
I may concat the first 4 char of each field:
Name field be set as the combination of the Role - Department - Location, you could set this in a real time workflow on Create and any change to the Department, Office, or Role columns on the table.
I may concat the first 4 char of each field

Was this reply helpful? Yes No