Can we keep initial value when having data validity to prevent duplication?

[vivian]

I have a column of sequence number List[Task] which is a unique order number combined with a virtual column with year & month as a prefix, i.e. YYYY-MM-000, below is what I put on the initial value to create the number when the users add a new row.

CONCATENATE([Prefix],
right(“000”&(NUMBER(
INDEX(
SPLIT(
LOOKUP(MAX(List[_RowNumber]),“List”,“_RowNumber”,“Task”)
,[Prefix])
,2)
)+1),3)
)

But the limitation of having this initial value is that if more than 1 user is going to add the record at the same time, the app will create the same order number.

As I need it as a unique number (no worries, I have another column with “UNIQUEID()”), I am going to have data validity to prevent duplicate input.

I went through all the content here, but seem that all the example did not work on my app. I am thinking will it create a conflict here if we use both initial value & data validity on the same column?

Or should I use another trick to use “arrayformula” on the google sheet? but seem that it wouldn’t work coz my order number is depended on the prefix, year & month.

[Steve]

vivian:

But the limitation of having this initial value is that if more than 1 user is going to add the record at the same time, the app will create the same order number.

There is no way to avoid this problem, unless each user is given a different base starting number.

vivian:

As I need it as a unique number (no worries, I have another column with “UNIQUEID()”), I am going to have data validity to prevent duplicate input.

Not gonna work as you hope.

[WillowMobileSys]

Steve:

Not gonna work as you hope.

As Steve said, but to elaborate…

Each user on their respective device will have a complete COPY of the app from which to work with. So if two users open the app at the same time, with all things being equal, they both will have the same exact copy of the app. Neither copy knows anything about the other.

In this type of system, the only way to prevent the type of duplication you are trying to avoid is to have an external resource hand the app the core sequential number you are looking for. But this takes time and effort to build and usually not worth it.

Using AppSheet’s UNIQUEID() function is your best insurance to prevent duplicates. I believe they have built some “smarts” into that function to ensure that duplicates are never generated for a particular column.

[vivian]

Thanks for your elaboration John!

[vivian]

Thanks Steve!