Reply
Highlighted
Level 8
Posts: 157
Registered: ‎07-13-2017
Accepted Solution

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

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.


Accepted Solutions
Level 8
Posts: 157
Registered: ‎07-13-2017

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

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.

 

View solution in original post


All Replies
Level 10
Posts: 271
Registered: ‎05-17-2017

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

[ Edited ]

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

Level 8
Posts: 157
Registered: ‎07-13-2017

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

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?

Level 10
Posts: 271
Registered: ‎05-17-2017

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

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.

Level 8
Posts: 157
Registered: ‎07-13-2017

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

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

 

 

 

Level 10
Posts: 271
Registered: ‎05-17-2017

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

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.

 

 

 

 

 

 

 

 

 

 

Level 8
Posts: 157
Registered: ‎07-13-2017

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

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.

 

Level 10
Posts: 271
Registered: ‎05-17-2017

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

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 Smiley Happy

 

Very nice work!