Generate unique id in specific format in app sheet

[urvashigarg]

I want to create unique id in specific format like TASK00001.

whenever new task is created, system automatically create id TASK00002 and so on.

please help

[baba_sawane · Accepted solution]

Hi , 

The most straight forward way would be this formula : 

IF(
[_THISROW].[_RowNumber] - 1 < 10,
CONCATENATE("TASK", "00000", ([_THISROW].[_RowNumber] - 1)),
IF(
[_THISROW].[_RowNumber] - 1 < 100,
CONCATENATE("TASK", "0000", ([_THISROW].[_RowNumber] - 1)),
IF(
[_THISROW].[_RowNumber] - 1 < 1000,
CONCATENATE("TASK", "000", ([_THISROW].[_RowNumber] - 1)),
IF(
[_THISROW].[_RowNumber] - 1 < 10000,
CONCATENATE("TASK", "00", ([_THISROW].[_RowNumber] - 1)),
CONCATENATE("TASK", "0", ([_THISROW].[_RowNumber] - 1))
) ) ) )

It's a shameful formula 😅

[baba_sawane · Accepted solution]

Hi , 

The most straight forward way would be this formula : 

IF(
[_THISROW].[_RowNumber] - 1 < 10,
CONCATENATE("TASK", "00000", ([_THISROW].[_RowNumber] - 1)),
IF(
[_THISROW].[_RowNumber] - 1 < 100,
CONCATENATE("TASK", "0000", ([_THISROW].[_RowNumber] - 1)),
IF(
[_THISROW].[_RowNumber] - 1 < 1000,
CONCATENATE("TASK", "000", ([_THISROW].[_RowNumber] - 1)),
IF(
[_THISROW].[_RowNumber] - 1 < 10000,
CONCATENATE("TASK", "00", ([_THISROW].[_RowNumber] - 1)),
CONCATENATE("TASK", "0", ([_THISROW].[_RowNumber] - 1))
) ) ) )

It's a shameful formula 😅

[urvashigarg]

Thanks for your reply.

I am new to app sheet..Could you please tell where I need to apply this form.

when I apply on column level, I am getting this error.

"Key column 'Task ID' in Schema 'All Task_Schema' cannot use an app formula. The app formula will be removed."

[baba_sawane · Accepted solution]

You need to add a new column (not a virtual one), and you can use this formula inside your Initial value field and use it as a key column.

If you don't know how to add a new column : In your database, create a new column for the ID (in my screen it's TASKID) : 

Then go back to your app and regenerate the table using the refresh button: 

[urvashigarg]

thank you so much..its working

[mckinley]

can you show the formula?

[Harith]

How about i want to start the id starting with 20000?