Calculated Column vs. Workflow

Question

This is my second question in as many days. I was successful with my first question. So here goes: I set up a ACR/Project Number Assignment log which auto-generates a customized, sequential number.

I have 2 calculated columns, the first creates the unique number upon creating a new log entry. The second is the "Closed Date" column which adds the current date or N/A dependent on the status of the entry (Open, Closed or Canceled). Here's the rub, all of the assigned numbers reflect "000" where a sequential number should appear. Only, when I go into list settings, open the corresponding list and refresh the page does the seq. number display.

Obviously , a delay, glitch or lag in the formula. So, I think a workflow is the better way to go. It will also allow for an email to go to the requestor.

1st cal. col. formula:

=IF(Category="Project File","PF-IS-"&Location&TEXT(Created,"-yy")
&CONCATENATE  ("-",REPT("0",MAX(0,3-LEN(ID))),ID),IF(Category="Change 
Control","CC-IS-"&Location&TEXT(Created,"-yy")&CONCATENATE("-",REPT("0",MAX
(0,3-LEN(ID))),ID)))

2nd cal. col. formula:

=IF([Project Status]="Closed",(Created),IF([Project Status]="Canceled", 
(Created),IF([Project Status]="Open","N/A")))

Help. Please and thank you!

Answer 1

In your formula, the issue would be in the ID field that will not be calculated until the formula is changed again in calculated column field settings.

Check the full list of supported and unsupported fields in the SharePoint Calculated Column Formula.

---

In your scenario, the available workaround is using

• the workflow instead of the calculated column,
• If you have a good knowledge of development, try to use Event Receiver instead.
• Build your formula using JSOM

Check the details steps and other workarounds at

• Use SharePoint ID Field in Calculated Column.
• Auto Serial Number in SharePoint New Form using JSOM.
• Using Lookup Field in Calculated Column SharePoint