1

I have an SP Library that has a 'Name' column. The names of the documents are formatted as follows:

 2021  CT0000  Driver             Charlie  Tuna  1_1_2021.jpg
 2021  CT0000  Driver Capatain    Charlie  Tuna  1_1_2021.pdf
 2021  CT0000  Driver Instructor  Charlie  Tuna  1_1_2021.bmp

I'd like to create a calculated column in the same Library that reads the content of the 'Name' column and automatically enters an abbreviation in the calculated column based on a 'portion' of the content in the 'Name' column.

Example: The content of the calculated column would be CT000D, CT0000DC, CT0000DI.

Is there a way to write an 'IF' statement for this purpose? Something like the below, that reads the string and if it finds the specific combination then...?

 =IF("Driver Captain" in Title,"CT0000DC, IF("Driver Instructor" in Title,"CT0000DI))
Improve this question

2 Answers 2

Reset to default
0

We cannot use file name in calculated column formula. It's a known limitation of SharePoint.

There is a SharePoint UserVoice for this. You can vote here: Use filename in calculated field

Workaround:

  1. Create a new text field & populate its value using workflow/Power automate flow on item creation/update. Hide this field from property forms.

  2. Use workflow to update the Title field based on the filename. Then use Title field in the calculated column formula.

Improve this answer
0

Solution1

Create a Calculated column: When the focument Title is Driver Captain, the value is CT0000DC; when the document Title is Driver Instructor, the value is CT0000DI.

=IF(Title="Driver Capatain","CT0000DC",IF(Title="Driver Instructor","CT0000DI",""))

enter image description here

enter image description here

Solution2:

Create a MS Flow: When the document Title contains Driver Captain, the value is CT0000DC; when the document Title contains Driver Instructor, the value is CT0000DI.

enter image description here

enter image description here

========================= Updated Answer ========================

Have you tried with Switch Case feature?

Flow supports nested conditionals, which make it possible to have very advanced branching logic. Switch case makes it very easy to have parallel branches of conditional logic based on a single value.

enter image description here

For information, please view this article.

Improve this answer
5
  • Yes, I like the flow option; and I've tried it, I just couldn't get it right. To be clear there are 20 different titles to which this would need to be accomplished. Flow won't let you write that many different condition (as youi show it). Maybe a variable string that includes each 'IF' in it might work. Your thoughts?
    – Curtis E Troutt
    Apr 30, 2021 at 14:54
  • Solution 1 would also work, and I tried that, also. The Title does ONLY include the name of the tilte (i.e. 'Driver'), each Title it formatted as 'Year UnqID Title FrtName LastName'; so, I need a 'contains' or 'contained in' type of calculatioin/condition. Example: 'If Title contains...then' as apposed to 'IF Title =...'.
    – Curtis E Troutt
    Apr 30, 2021 at 15:01
  • Hi, Curtis E Troutt. I have updated answer under initial My Answer. Hope this can help you to solve issue. Have a nice day!
    – EchoDu_MSFT
    May 3, 2021 at 7:28
  • Thank you for the update. I am still unclear as to whether or not the 'Switch Case' you reference in your updated answer will work for me. The examples you provide has specific 'text' that is being queried. Example: If 'Driver Captain' then 'CT0000DC'. This I understand. However, my 'Titles' are long and complex; Example: 2021 CT0000 Driver Captain Charlie Tuna 1_10_2021. All I want to know is if the Title CONTAINS (not if it equals) 'Driver Captain'; if yes then the Flow should concatanate the MemberUniqueID with the abbreviation to make the CertQualUniqueID 'CT0000DC'.
    – Curtis E Troutt
    May 3, 2021 at 14:57
  • Hi, Curtis E Troutt. “Switch Case” only provides "equals" conditions, that is, when your "Title" is equal to "Driver Captain", the "CT0000DC" will be filled in. At present, there is really no better solution to realize your design. If there is any solution in the future, I will share with you as soon as possible. Have a nice day!
    – EchoDu_MSFT
    May 4, 2021 at 7:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.