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
  • Please Check the below answer, Please don't forget to Mark and Upvote the correct answer in case it helped you! take a tour at sharepoint.stackexchange.com/tour – Mohamed El-Qassas MVP Aug 30 '17 at 9:11
5

Yes, In your scenario, the available workaround is using the workflow instead of the calculated column, and if you have a good knowledge in development, try to use Event Receiver instead.

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.

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.