cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WaMCHCH
Frequent Visitor

Lookup a record and add new record

Hello, I just can't get my head around the lookup formula and add new record if the record doesn't exist. Please help!

 

Currently in PowerApps I have a screen(screen1) with a button, on button select it navigate to another screen(screen2) and load up a record of that current PowerApps user.

 

I have a variable called: currentUser (which is the employeeID)

 

I have a CDS entity called: Response {employeeID,col1,col2,col3}

 

What I want to do is when the user select the button, it will look up the Response entity and the employeeID record. if the emplyeeID exist navigate to the next screen, if the employeeID doesn't exist is Response entity then add employeeID to the entity before navigating to the next screen.

1 ACCEPTED SOLUTION

Accepted Solutions
RezaDorrani
Dual Super User II
Dual Super User II

Hi @WaMCHCH 

 

To check if record exists

If(IsBlank(Lookup(Response, employeeID = currentUser).employeeID), code to add new entity record and navigate, code to navigate)

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

7 REPLIES 7
RezaDorrani
Dual Super User II
Dual Super User II

Hi @WaMCHCH 

 

To check if record exists

If(IsBlank(Lookup(Response, employeeID = currentUser).employeeID), code to add new entity record and navigate, code to navigate)

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

Hi, thank for your help, I tried to follow your instruction but I still get error when trying to add EmployeeID

Capture.PNG
It say that (in Patch) EmployeeID doesn't exist.... I can't figure out why the EmployeeID didn't appear and all other field in the Response entity appeared.

RezaDorrani
Dual Super User II
Dual Super User II

Hi @WaMCHCH 

 

Your patch function syntax is not correct

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Hi @RezaDorrani ,
I have looked at many examples of Patch and tried different things but I still can't figure out why I can't update record still,


patch.PNG
I keep getting Invalid argument type, in patch it doesn't recognize "EmployeeID" field....
My datasource is a CDS name ESM_L1EmployeeResponses and in there the following columns: {EmployeeID, Q01, Q02, Q03, Q04...} with EmployeeID being the primary key.

 

All I wanted to do is check the records to see if the employeeID is already existed, if not add the employeeID

RezaDorrani
Dual Super User II
Dual Super User II

Hi @WaMCHCH 

 

1) What is the data type of column EmployeeID?

2) Set(currentUser, ...) and if(isblank( ?  Why an AND

First Set(currentUser, ...); f(isblank(....    -- This will first set the currentUser and then check if your employeeid exists or not

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

thank @RezaDorrani 

I thought I'll share this solution. 
Because I wanted to create a varible that contain the current form user first.
the use of ; and AND made a big different and the code is now working.

Set(currentUser, Office365Users.MyProfile().MailNickname) ;

If(IsBlank(LookUp(ESM_L1EmployeeResponses, EmployeeID = currentUser).EmployeeID), Patch(ESM_L1EmployeeResponses, Defaults(ESM_L1EmployeeResponses) ,{EmployeeID: currentUser }); Navigate(Screen_Level2) , Navigate(Screen_Level1))

 

RezaDorrani
Dual Super User II
Dual Super User II

@WaMCHCH 

 

Great !!!

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,617)