Unanswered

Auto-generate sequential ID (reference number) in SharePoint List and display on submission form

(1) Share

Report

Posted on by labrat45

I have an OOS Log SharePoint List for tracking OOS events (+ Power App submission form) and each entry needs a unique reference number in the format of "LABOOS-YYYY-##" (e.g. 3rd OOS event this year would be logged as "LABOOS-2024-03").

How do I auto-generate this ID sequentially when entering a new incident on the submission form based on the previous entry already on the SharePoint list? The ID should be displayed (non-modifiable) on the submission form when entering an OOS incident, populated in the list ID column when the incident is submitted, and restart the incident numbering when a new year is reached.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

AllanDeCastro 412 Most Valuable Professional on at

Like (0)

Report
Hello

I think this question is more related to SharePoint columns. You can add a calculated column (In the List Settings or Library Settings page, click on "Create column" then choose "Calculated" as the column type).

In the Formula box, enter your formula. For example:

="LABOOS-" & TEXT(YEAR(Created), "yyyy") & "-" & TEXT(ID, "00")

YEAR(Created) retrieves the year part from the creation date of the item.
ID is the default ID column of SharePoint, which increments sequentially for each new item.
TEXT(..., "00") ensures that the sequential number (ID) is formatted with leading zeros.

Once the calculated column is created, SharePoint will automatically calculate its value based on your formula for each item in the list or library.

Was this reply helpful? Yes No
labrat45 4 on at

Like (1)

Report

Thank you, however there seems to be an issue with the formula. Creating a new item on the list produces an ID of LABOOS-1905-00 for each new item. The year should be 2024 and the sequential number should follow whatever the most recent entry value is. (e.g. Previous entered event ID is LABOOS-2024-16, therefore new entry ID should be LABOOS-2024-17)

Was this reply helpful? Yes No
AllanDeCastro 412 Most Valuable Professional on at

Like (1)

Report

Hello

This is unusual since the reference number is based on the creation date and the item ID, which should be sequential for all items. Maybe check the data in both of these column on the created item to understand what happened.

Was this reply helpful? Yes No