Answered

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

(0) Share

Report

Posted on by UB400

356 Microsoft Employee

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (18)

Answers (1)

Sort by

hpkeong 2,944 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
UB400 356 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

hpkeong 2,944 on at

Like (5)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
AlanCampbell 35 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
UB400 356 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Mike8 1,330 on at

Like (2)

Report
Copy link

Link copied!

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
)

Was this reply helpful? Yes No
bdc604 158 on at

Like (3)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
JeremyDIV 68 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
UB400 356 Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

@bdc604 you can now increase the limit beyond 500 records (upto 2000 I believe). I can understand your frustration, but not the sentiment you express. With a little exploration and help from the great community here, it's amazing what one can achieve with PowerApps, there's no shortage of amazing Apps that have been built by members of the communiy here.

If you're just trolling, then I suggest you do it elsewhere. The PowerApps community has by far the best and most helpful members I've come across anywhere.

Was this reply helpful? Yes No