Auto-Assigning Project Codes Based on Status

BrendonP12222

Good afternoon,

I have a formula to auto-generate a Project Code based on Approval Status and Department and department.

=IF([Approval Status]@row = "Approved", LEFT(Department@row, 3) + "-" + (YEAR([Project Intake Date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + [Suffix Helper]@row + ""))

Using the screenshot above as an example, I would like to adjust this formula so that Row 5 becomes OTL-2024-103 since it was "approved," and no Project Code was generated for Row 4 because the project was "declined".

To take it a step further, I'd like Row 7 to become OTL-2024-105 since Row 6 hasn't been "approved" yet.

Once Row 6 is approved, I'd like that to become OTL-2024-106.

Once a row has been assigned a project code, I'd like to "lock" it in place so it can't be adjusted.

BrendonP12222

More context:

This is the formula I'm using to generate the Suffix.
=IF([Approval Status]@row <> "Approved", "", MATCH(UniqueID@row, COLLECT(UniqueID:UniqueID, [Parent ID]:[Parent ID], [Parent ID]@row, Department:Department, Department@row), 0))

I have a second helper suffix column to add digits.

=IF(LEN(Suffix@row) = 1, "10" + Suffix@row, IF(LEN(Suffix@row) = 2, "1" + Suffix@row, IF(LEN(Suffix@row) = 3, "" + Suffix@row)))