Populate property based on tags in file name

[drmrbrewer]

I have created a "Choice" column called "_Type" with a number of predefined choices, like "_OA1", "_PD3", and "_GY2".

Now for each new file added to SharePoint I'd like to create a flow which populates the _Type column based on the content of the file name.  For example a file called "Your agenda for today's _OA1 meeting" should automatically add "_OA1" to the _Type column when the file is added.

I have a very basic flow which will look for "OA1" as a condition and update the relevant property:

 

But I don't want to be creating a new condition for each possible choice, not least because the choices might change later.

Ideally I'd like to be able to recognise a particular "tags" pattern in the file name and apply all the recognised tags to the _Type column.

e.g. a file name like "Agenda for meeting { tags: _OA1, _GY2 }.docx" would automatically apply _OA1 and _GY2 to _Type.

I know what I'd need to do in e.g. VBA or JavaScript to parse the file name and extract what I want, but don't have enough knowledge yet of Power Automate to know how to achieve this here.  Basically I'd be wanting to parse the filename to look for a "{ tags: ... }" pattern, and extract the comma-delimited list as an array. 

[wskinnermctc]

Can you run the flow and show an example of the outputs of the file name? 

And/Or provide examples of the full file names with extension? Like what are some possible names?

I'm trying to get an idea of what to use for extracting information.

[drmrbrewer]

@wskinnermctc  thanks for the reply.  An example file name was given in my post:

"Agenda for meeting { tags: _OA1, _GY2 }.docx"

This should be parsed to extract "_OA1" and "_GY2" and apply these both to the _Type column (which accepts multiple choices).

But really any suitable format could be used in the file name to identify where the tags are, e.g. "Some random email which is tagged [ _OA1, _GY2] or whatever.msg".

Or something with a part that looks a bit more like JSON: "blah blah { tags: [ _OA1, _GY2 ] } blah.xlsx".

EDIT: sorry, I forgot the colon ":" is not a valid char in a filename, so this rules out some of the above.  So let's say "Some random email with [[ _OA1, _GY2]] tag.msg" ... i.e. parse the file name to extract a string within "[[ ... ]]" and convert that to an array of tags to add to the property.

I just have no idea where to start on this sort of parsing... once pointed in the right direction I should be away!

[wskinnermctc]

Also, do not name your columns "_Product" or "_ColumnName" starting with an underscore, number, or special character. 

The system will add "Odata_" to the beginning of the name, and any expression will have to use "Odata__Product" or "Odata__ColumnName" to identify the field.

See more about it here in a post about column EntityPropertyName for sharepoint.

[drmrbrewer]

@wskinnermctc OK thanks for the tip.  I wasn't really intending to use underscores, I was just adding this to distinguish those that I'm using for testing purposes from those that I'm not, so I know which ones can be deleted in future.

[wskinnermctc]

So it is a little difficult to be precise with this since file names might be different. But if you are making all of the file names or know the consistent format, then it would be easier.

Basically, you need to be using some combination of expressions using String Functions to identify what you want.

Let use the text string FileName example:

"Agenda for meeting [[tags_OA1, _GY2]].docx"

The split() function will separate the text string by a character you identify. So if I used split with the '_' underscore the result will be:

split('Agenda for meeting [[tags_OA1, _GY2]].docx','_')

[
  "Agenda for meeting [[tags",
  "OA1, ",
  "GY2]].docx"
]

So that is an array of 3 items.

If you know the file name will always have double starting and ending brackets then you can use the substring() and indexOf() and lastIndexOf() to get positions of characters and extract what you want. I also had to use the add(), subtract sub(), and integer int() functions in order to get the correct positions of characters.

substring(variables('FileName'),
add(int(1),indexOf(variables('FileName'),'[')),
sub(lastIndexOf(variables('FileName'),']'),add(int(1),indexOf(variables('FileName'),'['))))

This will result in an output of:

[tags_OA1, _GY2]

See the power automate example below:

substring example

So can you look at those examples and let me know if you can work on it some way, otherwise I would need to know exactly how FileNames will be coming in, even if they don't have tags, to try and generate some expression logic.

[drmrbrewer]

@wskinnermctc thanks, that's really helpful, and I got it working to the point where the string is in the form "[_OA1, _GY2]".  How do you then treat this as an array of strings, and assign each string to my "_Type" column (which accepts multiple choices) as per my OP?  Perhaps easier to get into the form "_OA1, _GY2" and split on the comma to create an array?

As a side note, I'm far more used to just writing code in a simple text file, where I can see how it all fits together and edit functions directly in the file, like automating stuff via VBA.  This Power Automate feature is potentially very useful, but I am finding it tricky to do stuff via a graphical flow editor, where the overall flow is visible in the UI but the detail of each step is buried several clicks down... I'd rather just dig in and write text code directly.  Never mind, will get used to it!

[wskinnermctc]

Will it ALWAYS be double brackets [[_OA1, _GY2]]

Or will there be times when it is just single brackets? [_OA1, _GY2]

Will there be times when there are no brackets but the tags are still in the file name? "Name_OA1_GY2"

[wskinnermctc]

If the file name with tags ALWAYS has double brackets [[ _OA1, _GY2]]

This expression will get the string down to _OA1, _GY2

replace(replace(
substring(variables('FileName'),
add(int(1),indexOf(variables('FileName'),'[')),
sub(lastIndexOf(variables('FileName'),']'),add(int(1),indexOf(variables('FileName'),
'[')))),'[',''),']','')

It is basically the first big expression wrapped by two replace() function to remove '[' and ']'

We can check if the file name has tags as a condition, but I'm not including that just yet. I'm just working on getting the string to the correct format. Once the string expression is completed, we can figure out how to deal with a condition of file names without tags.

[drmrbrewer]

> Will it ALWAYS be double brackets [[_OA1, _GY2]]

Undecided yet, but the format of this is completely within my control... what's most important is that the format is consistent and predictable, which it will be.  So if I can get something working for "[[_OA1, _GY2]]" then I can later modify it easily to work for "[_OA1, _GY2]" or "{ _OA1 _GY2 }" or whatever.