Generate Unique Identification Numbers

thor

I need to create a field in my database that generates unique identification numbers for each row. The desired format is f-####-####-####, where the "f" remains constant. These numbers should be generated for both existing records in the database and any future records that will be added. Can anyone help? I can easily do this in Airtable using formulas, but I'm stilling learning how Xano works. And seeing the process will help me better understand Xano. Thanks in advance.

Louis Machado - CSA

Hi everyone,

I've recorded this video showing how you can create this custom unique ID type and store it in the database.
https://www.loom.com/share/1a2d8924c400408fa674aa2b85a6410a?sid=2a42ae01-4159-4cef-93d1-0851c2aca2a9

You are welcome to download and use the snippet of my work featured in the video, plus the functions to edit existing records.
https://www.xano.com/snippet/zExXELo4

thor

Thank you, Louis. Much appreciated. I'm super close, but have three follow-up questions.

1. My unique index to prevent duplicate values is not working. I'm getting the following error: SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index "mvpw2_3_btree_unique_xdo_fin_asc"
   DETAIL: Key ((xdo ->> 'fin'::text))=() is duplicated. (SQL: CREATE UNIQUE INDEX "mvpw2_3_btree_unique_xdo_fin_asc" on "mvpw2_3" USING btree(((xdo->>'fin')::text) asc))
2. Is there a way to generate the numbers when you add a new record in the database view? Or do you have to go to the function and run it every time to add a new record?
3. Is there a way to generate the numbers for records already in the database?

The methods to trigger a function is where I'm still fuzzy. Thanks.

Louis Machado - CSA

Hi @thor

Thanks for the questions, they are very good ones.

1 - If you attempt to create a unique index on a table that currently contains empty values, although these values are empty, they exist, resulting in duplicate records within the column. To resolve this issue, populate the column with non-duplicate values, and your problem will be resolved.

2 - To be able to generate a column with a predefined formula is something we have in our roadmap and will be added to the product in the upcoming months.

3- The workflow shown in the tutorial is what you will use inside your function stack to create to either add new records in the table or update the existing ones. While the tutorial primarily focuses on adding new records, to edit existing ones your function stack will include this below.

Also, I've already revised the snippet to include both the adding and editing workflows.