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

[moelhag]

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?

[mdevaney · Accepted solution]

@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]

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

[moelhag]

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?

[mdevaney]

@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."

[moelhag]

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? 

[mdevaney]

@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."