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
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (1,922)