Synching problem

[JamesWB]

My app needs to collect data offline as well as online, since it has to work in some factory locations where there is no signal on either SIM or wifi.

In a table, I have a Counter which now works pretty well, which increments when a new record is added.

Problems arise when more than one user is working offline in a factory using the app. The increment takes place separately on each device. When they upload to the cloud sheet, there is confusion.

Is there a way to solve this?

thanks

James

[SkrOYC]

I could explain it to you but I think @Joseph_Seddik would do a better job at it.

In summary, you will need another intermediate table so that the users add their data there and then a bot does the other part.

Anyway, part of your problem is based on the fact that they are offline and any system will have it's problems with it.

[JamesWB]

Yes, it was an attraction of using AppSheet that it accumulates data on the device offline, but it appears not to have elegant ways of synching several offline devices without building some kind of elaborate database routines?

[Joseph_Seddik]

This is not about AppSheet, rather about data modelling and data handling processes. 

When you have a database row susceptible to change based on concurrent user inputs or machine events, you should never allow this row to be updated directly by those users or events. 

Instead, those users or machines events should submit change requests in a separate table, as @SkrOYC has rightfully said, then you process these change requests and update that row accordingly. 

Also, you should think about why do you need a counter column in the first place! You are wasting memory space unnecessarily.

• Are you trying to display a count? UX views already displays dynamic counts. 
• Do you need the count in other calculations? Use COUNT() where needed.
• Are you trying to do some kind of ordering? Use timestamp.

If you remove the count column, then you wouldn't need anything, because you'll have removed the concurrent event/user input row dependency from you table.

Do you still need a count column? In that case would you please tell us why? Thanks.

[JamesWB]

Thanks Joseph, very interesting.

I'm trying to display a record number in the form and inline views and I need to use that record number in a PDF template as well.

The records need to show a count number based on the sequence in which they were added and to not increment when edited and to reorder when one is deleted, reshuffling them so that for example is the original sequence was 1,2,3,4,5 and then 3 is removed, they would become 1,2,3,4 and not 1,2,4,5.

I didn't realise dynamic counts were available in the UX, I'll look into that, sounds good.