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
thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (3,855)