Programming a button to both write data to a Collection or update the record if it already exists

[dsocohen]

I'm struggling to get my formula to do this. It seems that Patch and Defaults might be the way to do this, but I can't get it to work.

I'm building an app that tracks competency attainment. For any single competency, a user should be able to check a box stating whether or not they've attained that competency and should be able to take notes on how they did so. After doing that, I'm having the user hit a save button to write that to a Collection. If they ever revisit a competency's page, they should be able to update the Check Box and their notes. At first, I used the Create function to save the data, but then quickly realized that every button click appends a new row to the Collection.

I've resorted to trying to build an If Statement that searches for text within the Collection. If the text is not found, then a Create is executed. If the text is found, then a Patch is executed. Everything I've read says that Patch will serve both functions. I'm not sure what I'm doing wrong, but here's where I'm at:

If(UserData(Competency)<>BannerLabel_1_PMT.Text, Collect(UserData, {Competency:BannerLabel_1_PMT.Text, Attained:Checkbox_1_PMT.Value, User_Notes:UserNotes_1_PMT.Text}), Patch(UserData,{Attained:Checkbox_1_PMT.Value, User_Notes:UserNotes_1_PMT.Text}))

Where UserData is the name of my Collection. I'm invoking and unsupported function at the beginning of the function. Any help would be appreciated.

[Anonymous]

@dsocohen sounds like UserData holds only the competencies for 1 user, is this correct? Maybe you could share a little more about your app.

If the above is correct you may be able to re-write you formula like this (pseudocode):

If( BannerLabel_1_PMT.Text in UserData.Competency , Patch() , Collect() )

The 'in' operator checks if the user has that competency already in the column 'Competency' for their data. If so Patch the data, if not Collect the data.

You could also look at Update() & UpdateIf() https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-update-updateif

[v-yutliu-msft · Accepted solution]

Hi @dsocohen ,

The right syntax of Patch should be :

1)Patch(table, Defaults(table),{....})    //create new records

2)Patch(table,the item that you want to edit,{....})                    //edit an existing record

So please try this formula:

If(IsEmpty(Filter(UserData,Competency=BannerLabel_1_PMT.Text)),
   Collect(UserData, {Competency:BannerLabel_1_PMT.Text, Attained:Checkbox_1_PMT.Value, User_Notes:UserNotes_1_PMT.Text}),
   Patch(UserData,LookUp(UserData,Competency=BannerLabel_1_PMT.Text),
{Attained:Checkbox_1_PMT.Value, User_Notes:UserNotes_1_PMT.Text}))

//usually we use IsEmpty to justify whether this record exists

Here's a doc about Patch function for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

Best regards,

[dsocohen]

@Anonymous Thanks for responding! So here's my intent with this app:

I'd like for employees to use this app to track their progress against assigned competencies. My understanding is that PowerApps doesn't instance apps like you might expect with a smartphone. It simply behaves as a front end interface to manipulate data. From that standpoint, whenever a user opens up the app, I believe that they're all opening the same instance of the app.

User progress against their competencies only needs to be stored locally as this is just a companion app to a larger business solution that acts as the System of Record for competency data. This app is really just designed to allow employees to have access to the details of their competencies when they're not in front of their computers.

There appears to be a local cache for storing said data in PowerApps. Ideally, I'd like the user to commit their competency progress to that local cache and then when they re-enter the app, reload their progress from that local cache. I think this might make the app behave and feel like an instanced app where everyone is bookkeeping their own personal data, and can update it as they see fit. The only way I can see that working is everyone writes their status to a Collection so that that Collection can then be saved locally. What I don't know is if someone were to reload the Collection upon re-entry, would that action re-populate all of my Check Boxes and User Notes boxes.

Here are some screenshots for reference:

[dsocohen]

@v-yutliu-msft Thank you so much!! That did the trick. I'm successfully writing data and updating data to my local Collection.

Now, I'm trying to use the SaveData function...

SaveData (UserData,"UserData")

...to store that Collection in a local cache. I'm attempting to recall that data with LoadData (UserData, "UserData"); however, it's not updating the app's state to reflect last recorded progress. I have a feeling that reloading the data simply rebuilds the Collection, but that that Collection is not linked to the status of the visual controls in the app.

Is this not the right mechanism for checkpointing a user's progess/data/app's last state?

[Anonymous]

@dsocohen is the LoadData / SaveData not working on a phone or in your PowerApps dev environment? These functions only work on an iOS or Android mobile device, currently, so any work you do in the dev environment would have to be tested on a phone / tablet.

This is a handy video explaining how to setup offline functionality in PA

https://www.youtube.com/watch?v=ZAzZCh6hfbw

[dsocohen]

@Anonymous I actually just got this working. I had to go back and set the default state for each of the items to go back and read the reloaded Collection. Once I set this, it's now working like a charm.

Thanks!!

[Anonymous]

@dsocohen nice work!! I've yet to build an offline app, only research too date, but may need to in the not too distant future? I may have to hit you up for some advice when that happens 🙂