I’m here to seek your thoughts. I have the following tables in my app: Orders, Products, Customers, and Shipping. I’m using custom multi-step form for all add and edit actions. I have about 3-5 users who are a support team using my app, and data in those tables must be shared by all the users.
However, I realized that once a User A starts add or edit action, all other users would see that action in progress in real-time on their end i.e., User A and User B cannot add a new row to the same table at the same time with direct access to the tables.
One recommended solution I read from the community and documentation is to use Row Owners and User Specific Columns, but they’re merely a security feature, and like I said I want all stored data to be accessed by any user at anytime.
I am thinking of creating another four different tables (each associated with the main tables) to be used as a staging tables. These new tables would have similar columns as the main tables. I would use either Row Owner or User Specific Column on the staging tables, which would help any user to add new rows independent of each other at the same time. Only when a user confirms their action, I finally write it to the main tables, which can now be accessed by anyone, and clear the row in the staging tables. Same for edit action. If a user starts edit action, I would copy the Row ID of the row to be edited to the staging area and let user edit. Technically two users can edit the same row at the same time, independent of each other, but whoever submits last gets stored ultimately. Last to finish for the win!
You could implement a “task locking” mechanism, where a row (or row ID) becomes “locked” when a user starts editing or adding a new row, preventing others from working on the same row or table until it’s submitted or canceled. You could do this using a custom column for status or even a flag column. Once a user finishes their action, the row gets unlocked.
Always use user specific columns with custom forms. Row owners should not be necessary as each user is only working with one row tied to the custom form and values unique to them are stored in the user specific columns.
I get the traditional DB of lock mechanism, but ‘how’ would you differentiate ‘In Progress’ status of a new row in spreadsheets like Glide tables? Check if Row ID exists, but certain fields are empty? How about for ‘edit’ action, when technically all columns are not empty? Would love to hear your thoughts
Yes, that’s what I do. I have a separate table with a single row and a bunch of user specific columns. I use it for custom forms and I’ve used it for both adding new rows to another table and editing existing rows.
Adding rows with a custom form is pretty straightforward and only requires an add row action. I usually clear the user specific columns on the way into the form rather than on the way out. You don’t need to add a row first and then edit it. Just let the row add to the table using the Add Row action.
The editing part is a little more setup because you need a single value → whole row column in your regular table linking to the row of your staging table, A set column action that loads all of the user specific columns in the staging table from the regular table on the way into the form, a single relation linking the staging table row to the row being edited in the regular table. Followed by a set column action that updates the regular table row with values from the staging table.
I usually add a column to my staging table that contains the mode (ie ‘Add’, ‘Edit’). This becomes useful for your submit button action, which will be a custom action that either adds a row if the mode is ‘Add’ or updates through a set column action using the relation if the mode is ‘Edit’.
Always open the form using ‘Show Detail Screen’ so you only have to design it once for both Add and Edit modes.
With this setup, users will never see anything 'in progress". It will work just like a native form and nothing will be committed to the regular table until they click on submit.
I have been using this a lot recently, also extends it to include a set of add & edit for each screen that would use that table (say 2 rows for my Tab A, 2 rows for my Tab B). Can dynamically point to those rows.