cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WaMCHCH
Level: Powered On

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
Dual Super User
Dual Super User

Re: Lookup a record and add new record

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
Dual Super User
Dual Super User

Re: Lookup a record and add new record

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

WaMCHCH
Level: Powered On

Re: Lookup a record and add new record

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.

Dual Super User
Dual Super User

Re: Lookup a record and add new record

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.

WaMCHCH
Level: Powered On

Re: Lookup a record and add new record

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

Dual Super User
Dual Super User

Re: Lookup a record and add new record

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.

 

WaMCHCH
Level: Powered On

Re: Lookup a record and add new record

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

 

Dual Super User
Dual Super User

Re: Lookup a record and add new record

@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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (3,748)