Answered

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

(0) Share

Report

Posted on by happyccy

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”?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Gerard Simpson 2,987 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
shaikha92 43 on at

Like
a
(1)

Report
Copy link

Link copied!

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"

Was this reply helpful? Yes No
happyccy 71 on at

Like
a
(0)

Report
Copy link

Link copied!

Many Thanks

Was this reply helpful? Yes No
happyccy 71 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

Gerard Simpson 2,987 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
happyccy 71 on at

Like
a
(1)

Report
Copy link

Link copied!

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.).

Was this reply helpful? Yes No
Gerard Simpson 2,987 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

That's great - glad that's working.

Good luck with the rest of your build! ☘

Was this reply helpful? Yes No