Within an app, I can have many individual projects, identified by a ProjectNo column. Within each project, I would like to have a sequential unique identifier (e.g., first record created on a new project should be ProjectNo-001).
A complication is that multiple persons may be adding records to the same ProjectNo simultaneously. AppSheet does not check the backend for the most recent record unless a sync is performed. Therefore, if two or more users are adding records, it is inevitable that duplicate records will be created.
One solution has been to calculate these unique identifiers in the backend with a Google Sheets formula. I am now looking to migrate some apps to a Postgres server and will need to look into server triggers to mimic this effect, however before I do so…
Has anyone encountered a similar scenario, and found a different solution? I have been thinking that it may be possible to trigger a workflow rule upon record creation that would in turn calculate the unique identifiers. I am not clear if this will solve the above mentioned problem with multiple users, however. From what dataset are the workflow rules triggering?
Any thoughts would be appreciated!!