Linking to System User Table

[baseline9]

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

[v-xida-msft]

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,

[baseline9]

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. 🙂 

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]

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 ? 🙂 

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

[Boneckrh19]

*******Skip to the next row of stars if you want the answer specific to your picture*********

I don't think you want to be using the First() function here. That will give you the first person in the user table. If what you want is the current users information, you want to do something like 

LookUp(Users, 'User Name' = User().Email).Teams.'Capacity Hours Per Week'

Assuming that "Teams" is the name of the lookup field in the Users entity that connects to the Teams entity.

As for entity hopping, I always call it "walking it back" through the entities that are connected. Probably because my company data setup can mean I've walked back through three entities in one line of code.

About the system users entity; there is a built in function called User() that you might find helpful, and that I used in the formula I listed for you above. This is basically a shortcut to the Users table so that you don't have to do a lookup, and indeed helps with doing a lookup if you need more than just the three fields it offers. (As I did above).

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

User().Email - the current users email. However, please note that this is NOT the 'Primary Email' (internalemailaddress) field from the System entity. It is actually the 'User Name' (domainname) field. This caused some mixups in my company when we realized people had two different emails in those fields.

User().FullName - the current users name. This is the 'Full Name' (fullname) field from the Users entity

User().Image - the current users image. (The image that is changeable here: https://portal.office.com/account/#personalinfo)

Basically, these two formulas find the exact same information from the exact same table

User().Email
LookUp(Users, fullname = User().FullName).'User Name'

Which looks confusing because the display name for that field doesn't allude to it being an email address, but there it is.

Basically, you can use User().Email or User().FullName in a LookUp function so that you can find any information related to the current user. Examples would be:

LookUp(Users, 'User Name' = User().Email).Title
LookUp(Users, 'User Name' = User().Email).'Primary Email'
LookUp(Users, 'User Name' = User().Email).Address
LookUp(Users, 'User Name' = User().Email).'Main Phone'
LookUp(Users, 'User Name' = User().Email).'Mobile Phone'

And, you can add fields to the system Users entity. My company added a field called "Hire Date", and that helps me filter out any records that are not current employees. So if I wanted a list of every user instead of just the current one, I could made a Gallery like this.

Datasource: Users
Items: Filter(Users, !IsBlank('Hire Date'))   //If all your records in this table are valid, you can just put Users

If you want to then pick an employee from this list to edit the information of, add an edit Form with this

Datasource: Users
Item: Gallery.Selected

*******************************Start here*********************************

If you don't want to have a list of all employees and you only want the current user to be able to edit their own info, then skip the gallery, and change the edit Form to this

Datasource: Users
Item: LookUp(Users, 'User Name' = User().Email)

Instead of the Gallery you can have a display Form with the same properties as this edit Form. 

Based on the picture you shared, what it looks like is you want to edit a field from a different entity than the one your form is looking at. You can absolutely do this, but only if you change a few things and also utilize the Patch() function. You should add a custom card to the form. Stick a label and a Text Input control in there. On the Default property of the Text input, put this

ThisItem.Teams.'Capacity Hours Per Week'

On the save button, after the SubmitForm() function, (but before any kind of ResetForm() function if that's where you have it), add this 

Patch(Teams, LookUp(Teams, [TeamsUniqueIdentifier] = Gallery.Selected.Teams.[TeamsUniqueIdentifier],
    {'Capacity Hours Per Week': Value(TextInput1.Text)}
)

And now when you save the User entity changes, you also save your one Teams entity change.

This was lengthy but I hope it helped you understand some of your options!

[Anonymous]

Finally had to use 

LookUp(Users, User=ThisItem.'Created By'.User).'Primary Email'

Just don't know why many fields such as 'First Name', 'Last Name' are blank if I just used ThisItem.'Created By'. Seems like only 'Full Name' has the correct name.

This seems something so basic.

Thanks to everyone for their help. 

Hoping to get a better solution.