cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moelhag
Microsoft
Microsoft

Two normalized tables: need to show GUID if current user when there fullname is equal to current users full name, by connecting two tables via a primary key

Hey all,

 

So here is the situation. I have two tables, One called Activity and one call Names. These are connected to a database with thousands of entries. The Activity table contains details about the entry, while the names column contains details about the team members connected to the entry. Here are the  relevant values in both tables: 

 

Activity

Primary Key
Title
 

 

Names

PrimaryKey
GUID
FullName (complex) - only populates when connected to activity table

 

I need to populate a text field with the current users' GUID by connecting both these tables using the primary key, comparing the fullname in the 'Names' Table to the current users' fullname (i wrote code that says "CurrentUser = User().Fullname") and populating the text field with the current users' GUID if the expression matches.  

 

I also need to show all the current users active titles in a gallery using this same logic (titles are sourced in the Activity table, while the names connected to said titles are in the 'Name' table) . 

 

My issue is that i have no idea how to do this. I dont have much experience with SQL. Can someone help me out here?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Super User
Super User

@moelhag 

Assuming User().FullName produces exactly the same name found in your Names table you should use this code.  Put it in the Text property of your label.

 

LookUp(Names, FullName = User().Fullname, GUID)

 

There is currently not enough information to advise about your 2nd question.  How are the Activity and Name tables related?  What common fields do they have?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."  

View solution in original post

6 REPLIES 6
mdevaney
Super User
Super User

@moelhag 

Assuming User().FullName produces exactly the same name found in your Names table you should use this code.  Put it in the Text property of your label.

 

LookUp(Names, FullName = User().Fullname, GUID)

 

There is currently not enough information to advise about your 2nd question.  How are the Activity and Name tables related?  What common fields do they have?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."  

HadynM
Kudo Kingpin
Kudo Kingpin

Hi @moelhag 

Firstly, I would recommend against using a FullName as an identity field.  These are likely to change from marriage, divorce, etc.

My preference is to use the Office365Users connector and MyProfile() function somewhere at the start of the app to get details of the current user:

Set(currentUser, Office365Users.MyProfile());

Then you can reference the user object GUID like:

currentUser.Id

Even if they change their name, the GUID remains the same.

 

Secondly, can you please clarify:

  • Is the GUID in the Names table a reference to the user or the activity?
  • Is the FullName used outside of the app?

 

Cheers

Hadyn

 

Hey  @mdevaney 

 

The Lookup Function worked Great! I truly appreciate it, that was a pain trying to figure out on my own.

 

I can give you that additional info for the second question: 

Activity

Primary Key

ActivityTitle

 

 

Names

Primary Key
GUID
FullName (complex) - only populates when connected to activity column

resourcekey (16 digit key representing individuals attached to each activity)

 
Each Primary Key has a correspond ActivityTitle. I want to show every ActivityTitle for the current user. The resourcekey is a key that represents each individual assigned to an activity. I am thinking the way to display all titles currently assigned to the current user would be to create a text label that looks up the current users resourcekey (label23_2). I wrote this code in the gallery to show all activity titles assigned to the current user:

 

LookUp('Work Activities', resourcekey = Label23_2)

 

& set the label within the gallery equal to:

ThisItem.Activitytitle

 

 

This throws an incompatible type error. Any suggestions?

@moelhag 

I believe the reason for the incompatible type error is you are missing the Text property in your reference to the Label23_2.  Try writing it like this.

 

LookUp('Work Activities', resourcekey = Label23_2.Text)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

LookUp('Activity', resourcekey= Label23_2.Text)

 

this code threw back an "incompatible type error". The resource key is a "control" type so its unsupported for evaluation. Is there any work around for this? 

@moelhag 

You'll need to replace resourcekey with an actual column name from your activity table.  It cannot reference a control.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up." 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,260)