cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
baseline9
Level: Powered On

Linking to System User Table

Hi All

 

Ok I might be approaching this wrong so I’m hoping somebody can help! 

 

I'm trying to build a simple projects application in power apps that allows people to submit resource and project managers to find resource. 

 

We have created a brand new user / domain and new test environment in CDS. 

Then I create a database and a set of standard tables were available to me. 

 

I would like to be able to utilise the 'systemuser' table in the Common Data Service so when somebody starts or leaves the business, we don’t need to worry about updating our app and the app knows who the person is on login. 

 

I want to create a many to one relationship in this table so we can look up fields from custom tables however I'm unable to get any data across. 

 

I think I need to use the Azure Active Directory Object ID  or userpuid as my primary key but I’m unable to select as neither are searchable. 

 

I am right in assuming once a lookup is set on the primary key then all fields in the related table should be available to select when connecting to the primary table in power apps or is it we need to create a relationship for every field? 

 

The app is simple to start with so we have done a 'beta version' connected to excel and using the lookup functions in the front end but this app is going to grow in the future so we are trying to make is scalable and fast by having the common data service do all of the lookups and calculations rather than in the front end, as I understand this is what the CDS is for as it will help with performance (Along with security benefits etc..)

 

In summary - for testing purposes I’m just trying to lookup one field into the system user table, my predicament is the primary key option.

 

Sorry if I have completely misunderstood how this works, been a bit of a head scratcher!

 

Hope somebody can help 

Many thanks

3 REPLIES 3
Community Support Team
Community Support Team

Re: Linking to System User Table

Hi @baseline9,

Could you please share a bit more about your scenario?

Do you want to Look up User Table (SystemUser) based on current login user?

I agree with your thought almost, we could not use Azure AD Object ID  or User ID as my primary key. The Azure AD Object ID or User ID value is generated within User Entity automatically, we could not get its value directly within an app.

As an alternative solution, I think you could use PRIMARY EMAIL column as the Primary Key.

In addition, I think it is not necessary to build relationship within this Entity. If you want to kook up related records within Entity, I think the Filter function within PowerApps could achieve your needs.

You could use Filter functions to find related records based on PRIMARY KEY column. More details about Filter functions supported within PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
baseline9
Level: Powered On

Re: Linking to System User Table

Many thanks for your reply @v-xida-msft 

 

Mmmm it is a head scratcher this one. 

 

Correct and we have achieved this in our beta version connected to Excel by looking up to the office 365 users connection by email however when I moved the beta app to the common data service it all went very wrong. I got loads of errors, delegation issues and errors submitting data to the CDS.

 

We put it down to trying to 'lift and shift' which from experience is never a great idea. So I have been testing a new basic app with the CDS to re gain our confidence that it's not that buggy. I think we have overcome this now. 

 

As you can imagine I’m right a the very beginning and decisions made now will be critical for the future, I don’t want to have to unravel everything later on. Smiley Happy 

 

Parts of the app have quite complex filtering so my plan is to put some of these logical calculations in the CDS and filter on flags, E.g. capacity > x and available between dates etc.. 

 

I have thought about using email and our 'Beta version' uses email as the primary key but Ideally I don’t want to use email as I don’t believe this to be a good field, what happens if a new starter joins and whoever sets them up put in a mixed case name or mis spells something or we have people with the same name, we can put controls in and it would happen very rarely. I myself used to share the name of somebody else in a company and I used to get their emails all of the time. 

 

I could set up the flags and use looks ups in the front end to find records in a related entity but I don’t think the azure id or user id is available to search there either

 

I wanted to use lookups so you could just see the related fields when connected to the one entity, like in Power BI where you can set up a relationship and then just write RELATED(....

 

Mmmm maybe we can create our own primary ID somehow? 

baseline9
Level: Powered On

Re: Linking to System User Table

OK think I’m closer to a solution! 

 

So I found this article which talks about 'entity hoping'

 

I have also learnt it is better to use the existing entities rather than make my own so I’m now trying to lookup a field using the system users table from the teams table

 

I have created a field called 'Capacity Hrs Per Week' on the Teams entity as a decimal (I want to use it in calcs later on) 

 

I have created a simple app and on the edit screen I have created a custom card and entered the below formula 

 

First(Users).Teams.'Capacity Hours Per Week'

 

However I'm getting 'The property expects text values but this rule produces incompatible table values'

 

Can anyone help ? Smiley Happy 

 

https://powerapps.microsoft.com/en-us/blog/one-to-many-relationships-for-canvas-apps/