I have the following columns in SharePoint: Subject, In/Out, Date of Transmission, Unique Id. Subject column is a text field, In/Out is a choice column with the two dropdown options 1. Incoming, 2. Outcoming, Date of Transmission is a date and time column, and Unique ID is a calculated column. I attempted to create a formula for the Unique ID calculated column that gives each new record a unique based on Date of Transmission and the In/Out columns. With In/Out column counted separately meaning the first entry that has "Incoming" in In/Out column is counted as 1 and the first entry that has "Outgoing" in the In/Out column is counted as 1 and each column would increment by 1 as new records are added and the increment would be based on the In/Out column value. The format for the calculated column would be YEAR, "I" for Incoming or "O" for Outgoing, and the record increment count. Example for the 30th incoming record that has a date of transmission of 8/18/2020 would be 2020-I-030. And example for the 30th outgoing record in the same list with same date as given in the previous example would be 2020-O-30. The formula I am using is as follows:
=YEAR([Date of Transmission])&"-"&IF([In/Out]="Incoming","I","O")&TEXT(COUNTIFS([In/Out], [In/Out], [Date of Transmission], "<="&[Date of Transmission]),"000")
But am getting the following error: "The formula contains a syntax error or is not supported."
I've tried creating a new column and new list using the formula but end up with the same result.
The previous formula I was using did not differentiate between In/Out records and aggregated the count, so if the first outcoming message entered was the fifth record entry in the list it was counted as 5 and not one though it would identify it as O in the identifier. (2020-O-005 vs 2020-O-001; latter is the objective) That formula is:
=YEAR([Date of Transmission])&"-"&IF([In/Out]="Outgoing","O","I")&"-"&TEXT(ID,"000")
Any assistance / advice would be greatly appreciated.
