Patch On Fail / On Success - Data Loss if signal drops out mid transmission

[Delid4ve]

Hey guys just want a bit of advice,

If we use forms within our apps then we have OnSuccess / OnFail to handle any errors.

Within my app everything is stored in collections after the first screen and saved/loaded in order for offline use (it will be being used in alot of basements).  Once all data has been collected/entered/changed and we are back with signal i have multiple patch functions to save all the data back to sharepoint and on premesis SQL.

If the button is pressed to save the data back and internet is not connected thats fine, im using If(Connection.Connected.

However, if signal is lost during data transmission, its lost.  Ive tested numerous times by putting in aeroplane mode mid transmission.

What is the best way to handle this? Is there any way?

I cant afford to have data loss under any circumstances.

[JRaasumaa]

You may have to patch the data to a temp table in your on-premise SQL.

Then before patching that table to live data, run a comparison of the collection to the temp table and flip a true/false flag before triggering a procedure to move the data to live.

That way you can wait for a specific confirmation that the collection and a local sql table match 100% before moving it into live data.

 edit: spelling

[Delid4ve]

Hmmm...

I was looking at using the On error function.  Basically once the staff have entered all the required info across 5 screens they return to the home screen and press a button - Upload data.

I have just added after the process a nested if/onerror for each data source before the data is cleared from the collections and savedata, if it evalulates to true then the upload data button stays visible and a label show to resubmit the data again. but now im thinking about it thats ok for existing records that update, but any new records that were partially added wont work as they will have new IDs and wont get picked up on the if exists patch's.

So yes, thats a grand idea you have given me.

Now..

How am i to handle the comparing the data... does a simple if(table = table work) work? Or a little more complex?

[JRaasumaa]

It depends a lot on how your data is organized but since SQL cannot see into PowerApps collections you would need to do the comparison right in PowerApps. If the temp table was a static named table then you could have PowerApps compare the saved collection (maybe off a primary key field) to the temp table, using a ForAll, and change a column to false if any of your fields do not match.

I used a primary key ("Primary Id") field as a new column entry for a fake 1 to many relationship in PowerApps for a similar situation with image storage and it works quite well, you just have to write the primary key to a column every single time something is saved manually but that would give you a comparison field to look at if you did similar with a temp table.

It's going to be a lot of trial and error I think but with some planning I think you can save yourself from ever missing data or writing incorrect data.

[Delid4ve]

Hi JRaasumaa

I have created a temporary list within sharepoint matching the table within SQL, but with an additional column 'Upload' (Report Table)

I have created a flow - When an item is created or modified in temporarylist with a condition 'Upload' set to Yes, get items (filtered by three columns that will make the record unique, if exists in SQL update, If not add new.

Now im back to powerapps, i have changed the patch to temporary table.  But im a little stuck on the data comparison..

Could you give me an example formula for the suggestion above please.

Thanks in advance

[JRaasumaa]

It would be difficult to write an exact solution for you but I would write something using the ForAll to go through the collection with a lookup to the SQL table.

Then use an IF to check the value of the collection against the current ForAll line and a SQL lookup and if it doesn't match run a Patch to update the Upload column to a no.

[Delid4ve · Accepted solution]

I think i found a solution, the only stumbling block i had to mitigate was delegation.  So:

1: Patch Report_Collection to Temporary_Report_List in sharepoint with Tempoarary_Report_List having an additional column 'Upload' with default set as false

2: ClearCollect Temporary_Report_List back into powerapps as Temporary_Report_Collection (in operator not delegated)

3: Run the comparison, this is how i done it:

If(IsEmpty(Filter(Temporary_Report_Collection,Not(ID in Report_Collection.ID And Or(Field1<>LookUp(Report_Collection,ID=ID,Field1),Field2<>LookUp(Report_Collection,ID=ID,Field2),etc,etc,etc,etc),ForAll(Temporary_Report_Collection,Patch(Temporary_Report_List,{ID:ID},{Upload:true}),Set(UploadFailed,"Upload Failed, Please Resubmit")

4:  Use Flow so if item is modified or created in Temporary_Upload_List And Upload=true then it patches the row to my main table.

There is still room for error if signal drops out during the setting upload to true but i dont think im going to get rid of it completely and it should be a very quick execution for that small amount of change.  Ive probably got to add in some if error clauses to the clearcollect aswell to rerun it if signal drops out whilst loading the temp table.

[JRaasumaa]

That's a great workaround for a problem that shouldn't even exist if PowerApps had a better way to deal with patching data collections from offline to online 🙂

Very nice work!

[tejaspatel1982]

This is not perfect solution yet!

we are storing everything into collection. and 

when user press submit button

1) we save data into collection at local

2) then we check if Internet is there or not. if its there then we transfer the data to database and remove the local collection data. 

3) if internet is not there then data will stay there in collection. and when internet come it will write data to database.

this will work even internet is down while transferring data, because we store local data at all the time and remove the same once we transfer data to database.

now I only have one concern, if intenet goes down in between the app gets freezed and we dont have any option to handel the error. It would be really appreciated if you help us to deal with this scenario. I am sure we dont have anything like event which raises if internet goes down.