cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UB400
Level 10

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
hpkeong
Level 10

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

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.

 

survey.pngSample DataScreenshot (124).pngQuestionID: Taufik BB; UserID: T01 BBScreenshot (125).pngQuestion ID & UserID Repeat: Alert pop-upScreenshot (127).png

 

 

Hav a nice day.

hpkeong

View solution in original post

14 REPLIES 14
hpkeong
Level 10

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

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.

hpkeong
UB400
Level 10

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

@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
Level 10

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

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.

 

survey.pngSample DataScreenshot (124).pngQuestionID: Taufik BB; UserID: T01 BBScreenshot (125).pngQuestion ID & UserID Repeat: Alert pop-upScreenshot (127).png

 

 

Hav a nice day.

hpkeong

View solution in original post

AlanCampbell
Level: Powered On

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

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
Level 10

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

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

jindarling
Level: Powered On

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

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.

 

Highlighted
Mike8
Level 10

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

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
Level 8

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

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
Level: Powered On

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

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

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 191 members 4,978 guests
Please welcome our newest community members: