Checking to see if a Record exists, if it does then Patch an update, if not then create a new record

[UB400]

I'm building a "Survey" App that collects User responses to Questions, each User's response is in a separate record in the Responses table. The User should be able to save their response prior to Submitting their response, in order to come back to it to make any changes prior to Submitting their response.

How can I check to see if a Record exists for this particular response, if not then create a new record.

I was thinking of testing by using the lookup function, to see if a record existed that had the Question ID and User().Email, as the combination of these would be unique for each record.

Could someone kindly guide me on this and if possible give me an example of the syntax to use?

Thanks.

[hpkeong]

Hi

You may use:

- SaveData and LoadData

- This is saved in local storage area and retrievable when needed.

Please refer to:

https://powerapps.microsoft.com/en-us/tutorials/function-savedata-loaddata/

Have a nice day.

[UB400]

@hpkeong Thanks for the response, that will be really usefull, but not really what I was looking for.

What I'm trying to understand is as follows:

In this case my DataSource is a Sharepoint list, to identify a unique record I would use two Values, the QuestionID and the UserID.

So I need to lookup the SharepointList and see if any record meets QuestionID && UserID, if true it means the record exists and then simply update the values using Patch. If it's false, then the record does not exist and I need to create a new record with Patch (Defaults).

I can't figure out the syntax to do a lookup where two values need to match and also the syntax to do the branching if the condition is true or false.

Thanks.

[hpkeong · Accepted solution]

Hi

I try using Excel with Sample Data and use:

- "Check Mark":

If(LookUp(TableSurvey, DataCardValue7.Text in QuestionID || DataCardValue8.Text in UserID, "OK") <> "OK", SubmitForm(EditForm1), UpdateContext({Alert: true}))

- OnSelect.TextBox = UpdateContext({Alert: false})

- Visible.TextBox = Alert (for "Repeat" alert)

I hope my screenshot may help you and get what you want.

Sample DataQuestionID: Taufik BB; UserID: T01 BBQuestion ID & UserID Repeat: Alert pop-up

Hav a nice day.

[AlanCampbell]

G'day @UB400, did you ever figure this out, I'm currently trying to acheive the same thing, the solution below I can believe will check and then submit the new form if one doesn't exist, but I don't see the else part of the if statement to pass the row id and then update the exisiting record.

Any insight would be great.

Cheers

Alan

[UB400]

@AlanCampbell apologies for the delay in my response, too much going on on my side.

I don't use Forms, as more often than not,I run into limitations with them. I prefer to use the "Patch" command.

On the "If" condition, I found that you can nest them as well, so you could have an If statement as the "DataSource" for another "If" condition.

At the time I had raised this question, I had not realised that a Gallery "Selected" value is a "Global" value i.e. you can use it anywhere in the App, so that makes things much easier, and saves you from having to make an "expensive" call to the DataSource to retrieve values.

Let me know if you want to try Patch, I as well as others here can jump in, and help you along the way.

[Anonymous]

I have an alternative solution that works with SharePoint Lists:

Formula:

If(CountIf(ListOne,fieldOne=lblUser.Text,fieldTwo=GalleryOne.Selected.fieldname=0,Navigate(Next Screen,ScreenTransition.None),Navigate(RedirectScreen,ScreenTransition.None))

 ListOne is the SharePoint List;

 lblUser.Text=User().Email;

Basically it counts the records in ListOne where they match the user's email address and on one other field (identified by GalleryOne.Selected.fieldname).  If the answer is '0' then the user is allowed to progress to the first question of the survey; if the answer is not '0' then they are redirected to a screen advising they have already completed this survey.

[Mike8]

Hello UB400,

Does this work for you?

If(
IsBlank(LookUp(Responses,QuestionID=[the ID you want to check]&&UserEmail=User().Email)),  <-- returns true or false
Collect(Responses,{...}), <--- Adds a record (if the expression above is true.)
Patch(Responses,LookUp(Responses,QuestionID=[the ID you want to check]&&UserEmail=User().Email),{....}) <--Updates if false
)

[bdc604]

But, and this is a BIG but, won’t PowerApps ONLY query the first 500 records of a datasource such as a SharePoint list? Not 500 filtered results, but 500 records total. If you have a SharePoint list with 1000 items, PowerApps will only check the first 500 items against your criteria. As stated in articles, this heavy lifting task should really be delegated to SharePoint, but of course they haven’t figured out how to do this yet. No worries tho, its on the 20 year roadmap. In the meantime, feel free to use SP/PA to make cute apps like shopping lists for grandma, or even a dvd collection organizer!

[JeremyDIV]

@bdc604 Sounds like you have something against Swearpoint and PowerGaps...