3

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!

  • So what's your question bro? do you need to know the preferred one – Mohamed El-Qassas MVP Aug 5 '17 at 0:06
  • Will the workflow provide the instant sequential number? – K. Boone Aug 5 '17 at 0:08
  • Or can I modify the formula to provide the correct sequential number? – K. Boone Aug 5 '17 at 0:09
5

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.