cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
happyccy
Helper I
Helper I

***Seek Help*** >> Patch ‘EmployeeID’ to SharePoint Lookup Column

I have 2 table – “Employee” and “Application”. 

 

In the application screen, it pre-loaded employee’s personal particulars by lookup their Principal Email (User().Email) and routed to the relative record in “Employee” List.

 

However, how can I Patch the “EmployeeID” to “Application List”?

 

happyccy_0-1638079419958.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
AmDev
Super User
Super User

Hi @happyccy 

 

As you have the EmployeeID from the Employee list lookup, you should be able to use it to set the default of your Application List EmployeeID Lookup field. Try the following record as the default in the Application List EmployeeID Lookup datacardvalue:

 

{Id: Lookup('Employee List', Datacardvalue??.Text = 'Employee ID').Id

Value: Datacardvalue??.Text}

 

(i.e. Datacardvalue??.Text = E01 in your pic above)

 

Hope this helps.

 

Just another question - Not sure about your full usecase, but if you are using Power Apps, do you need the SPO level lookup? - If all interactions happen in the app, you could simplify by just using single line text for the Application list Employee ID and let Power Apps do the work of looking up - though not sure if your users are working exclusively in Power Apps?

View solution in original post

7 REPLIES 7
AmDev
Super User
Super User

Hi @happyccy 

 

Am I right in saying you want this patch to happen when the current user is submitting an application? - so it will always be the current user's ID that is patched with the corresponding application?

 

If this is the case and your application form is connected to the Application list, it should just be the case of showing the Employee ID Datacard in your application screen then adding your formula to the default property of the TextInput control (DatacardvalueX). Then in the 'Update' property of the Employee ID Datacard, add 'DatacardvalueX.Text'. If you want you can then turn visibility to false to hide this control and you're formula will still work away in the background.

 

Hope this helps - Good Luck!

shaikha92
Helper I
Helper I

I think the formula Lookup('Employee List',EmployeeEmail = User().Email).EmployeeID) should be like this: Lookup('Employee List',Lower(EmployeeEmail) = Lower(User().Email)).EmployeeID)

 

Using Lower() function to convert both strings to lowercase so you can compare them both. since comparison is case sensitive. E.g. "hello@abc.com" is not the same as "hello@ABC.com"

Many Thanks

Many Thanks.  @AmDev  Yes, I want to Patch a new record to 'Application list' after clicking Submit button.

 

Of the Application Screen, it pre-loaded employee's personal particulars by using formula (Lookup('Employee List', EmployeeEmail = User().Email)) and let it automically loaded the personal data w/o inputting on the Form (not editable) and its data cards.

 

However, what I want to Patch to a column in 'Application List' is a Lookup column - 'Employee ID' from 'Employee List", I cannot use EmployeeID: DataCardValue1 this queue directly.

 

happyccy_0-1638096390675.png

 

AmDev
Super User
Super User

Hi @happyccy 

 

As you have the EmployeeID from the Employee list lookup, you should be able to use it to set the default of your Application List EmployeeID Lookup field. Try the following record as the default in the Application List EmployeeID Lookup datacardvalue:

 

{Id: Lookup('Employee List', Datacardvalue??.Text = 'Employee ID').Id

Value: Datacardvalue??.Text}

 

(i.e. Datacardvalue??.Text = E01 in your pic above)

 

Hope this helps.

 

Just another question - Not sure about your full usecase, but if you are using Power Apps, do you need the SPO level lookup? - If all interactions happen in the app, you could simplify by just using single line text for the Application list Employee ID and let Power Apps do the work of looking up - though not sure if your users are working exclusively in Power Apps?

Thank you so much @AmDev 

I use it and works

UserID: (Alternative for returning UserID by calling "DataCardValue1.Text"
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: LookUp('03USER', DataCardValue1.Text = UserID).ID,
Value: DataCardValue1.Text
}

 

Because there are different types of users in this app: General User, Officer, Manager, & Admin (not System Admin.).

AmDev
Super User
Super User

That's great - glad that's working.

 

Good luck with the rest of your build! ☘

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (3,023)