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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,840)