Lookup a record and add new record

[WaMCHCH]

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.

[RezaDorrani · Accepted solution]

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.

[WaMCHCH]

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


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]

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]

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,

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]

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]

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]

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