Split error "The provided value is of type 'Null'

[ppleos_]

Hi All

I am hoping this has been asked before, but unfortunately like the flow my search is not working.

I have created two flows, using the same functions and steps. One flow run successful, and the other will report an error at the split function.

1.

2.

Here is the FX: first(skip(split(triggerOutputs()?['body/{Path}'],'/'),4))

Please help me understand why the same function reports an error in the second environment? How can solve it? @eliotcole @Expiscornovus 

Thanks in advance!

[Expiscornovus]

Hi @ppleos_,

It looks like the Folder Path field you are referencing in your expression is empty/has no value, that is why it cannot split.

You might want to check that in the output of your When a file is created or modified (properties only) trigger action of your failed flow instance run history. Can you see if the {Path} property is listed with a value?

Do you have any other specific configuration in your trigger action, can you share a screenshot of that?

[eliotcole]

Ah, @Expiscornovus ... check that expression, if the path is:

/folderone/foldertwo/

Then picking [4] from the split will be empty or null.

Getting Paths and Parents:

OK, if you're coming here from what you saw in an email update, @ppleos_ , this looks different as I realised that When a file is created or modified (properties only) provides much more information.

So ... From the When a file is created or modified (properties only) trigger ( 😅 ), perform the following expressions to get the following information:

Path no final /:	slice( triggerOutputs()?['body/{path}'], 0, -1 )
Parent: may fail on root	last( split( slice( triggerOutputs()?['body/{path}'], 0, -1 ), '/' ) )
Parent: more accurate	if( or( equals(last(split(slice(triggerOutputs()?['body/{Path}'], 0, -1), '/')), null), empty(last(split(slice(triggerOutputs()?['body/{Path}'], 0, -1), '/'))) ), '/', last( split( slice( triggerOutputs()?['body/{Path}'], 0, -1 ), '/' ) ) )

That last one checks to see if the response of getting the parent returns either a null or an empty value, if it does, then the file is in the root of the library, '/' ... if not, then the expression to get the parent can run.

If you want those expressions you can copy the text in the below spoiler and paste it directly into a compose action where it should lay them out for you.

Spoiler

EXPRESSIONS
1 - Path no final /:
@{slice(
	triggerOutputs()?['body/{Path}'], 
	0, 
	-1
)}
slice(
	triggerOutputs()?['body/{Path}'], 
	0, 
	-1
)

2 - Parent (could return null in root folder):
@{last(
	split(
		slice(
			triggerOutputs()?['body/{Path}'], 
			0, 
			-1
		), 
		'/'
	)
)}
last(
	split(
		EXPRESSION_1, 
		'/'
	)
)

3 - Parent w/ no null response:
@{if(
    or(
        equals(last(split(slice(triggerOutputs()?['body/{Path}'], 0, -1), '/')), null),
        empty(last(split(slice(triggerOutputs()?['body/{Path}'], 0, -1), '/')))
    ),
    '/',
    last(
        split(
            slice(
                triggerOutputs()?['body/{Path}'], 
                0, 
                -1
            ), 
            '/'
        )
    )
)}
if(
    or(
        equals(EXPRESSION_2, null),
        empty(EXPRESSION_2)
    ),
    '/',
    EXPRESSION_2
)

If Purely on a When a file is created Trigger

I had originally answered the question forgetting that the outputs of a created trigger give barely any information, but created/modified gives more data.

However, I've kept some of my original answer for those that come here and have a When a file is created trigger! 😅

Spoiler

Here it is easier to do a Get file metadata and a Get file properties, and you'll get all the information that you need on the file. Picking a split from potentially deeper folders is a messy affair, ppleos, because SharePoint libraries move a lot of things around in terms of name.

I'll edit with examples shortly, but that will get more accurate data here.

In terms of getting the parent folder, I'm not sure if that is in either of those actions, but I know that it is in an Send an HTTPS call to SharePoint for a file's details.

If you want some data straight away look in the headers:

File Name:	triggerOutputs()?['headers/x-ms-file-name']
File Path (including File Name):	triggerOutputs()?['headers/x-ms-file-path']

However I far advise just running both (or either) of the two file information actions, @ppleos_.

If you wish to get more indirect information from that using expressions, then I've listed the the path, the path no last slash, and parent folder in the spoiler below:

Spoiler

Those expressions:

Path no File Name:	replace( triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], '' )
Path no Name or final /:	slice( replace( triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], '' ), 0, -1 )
Parent:	if( or( equals( slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1), null ), equals( slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1), '' ), empty( slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1) ) ), '/', last( split( slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1), '/' ) ) )

As you may be able to tell, the second expression is used 4 times in the parent expression, to check against 3 types of emptiness, and to use if it isn't empty. This means that if the file is on the root of the checked library, it will show just a slash, and if it's anything else, the name of the parent folder only.

To get the parent name, it already doesn't have the file name or final slash, then you split it on slash '/', and take the last value. Boom.

If you don't care about that slash, then you can easily make it simpler.

If you only care about the parent name, then you're golden.

In the below spoler is some text that if you copy it all, and paste it directly into the input of a Compose  action, then it should give you these expressions with very brief notations on the structure underneath them.

Spoiler

DIRECT
1 - Name
@{triggerOutputs()?['headers/x-ms-file-name']}

2 - Path
@{triggerOutputs()?['headers/x-ms-file-path']}

EXPRESSIONS
1 - Path no name:
@{replace(
    triggerOutputs()?['headers/x-ms-file-path'], 
    triggerOutputs()?['headers/x-ms-file-name'], 
    ''
)}
replace(
    triggerOutputs()?['headers/x-ms-file-path'], 
    triggerOutputs()?['headers/x-ms-file-name'], 
    ''
)

2 - Path no name no last slash:
@{slice(
    replace(
        triggerOutputs()?['headers/x-ms-file-path'], 
        triggerOutputs()?['headers/x-ms-file-name'], 
        ''
    ), 
    0, 
    -1
)}
slice(EXPRESSION_1, 0, -1)

3 - Parent folder:
@{if(
    or(
        equals(
            slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1),
            null
        ),
        equals(
            slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1),
            ''
        ),
        empty(
            slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1)
        )
    ),
    '/',
    last(
        split(
            slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1), 
            '/'
        )
    )
)}
if(
    or(
        equals(
            EXPRESSION_2,
            null
        ),
        equals(
            EXPRESSION_2,
            ''
        ),
        empty(
            EXPRESSION_2
        )
    ),
    '/',
    last(
        split(
            slice(replace(triggerOutputs()?['headers/x-ms-file-path'], triggerOutputs()?['headers/x-ms-file-name'], ''), 0, -1), 
            '/'
        )
    )
)

That's all she wrote!

[Expiscornovus]

Hi @eliotcole,

Yes, I was planning on sharing an expression to check if {Path} was empty, but you already did that 😁 Nice examples! 😀

[eliotcole]

Yep ... it wasn't so much that it needed to be checked that it was empty, more that it was too *specific* a check.

Checking the 4th array item when there might only be 2, or when you actually wanted the 3rd is possibly the result of using a sample size of one when working it out. That's why the key thing I put in was to remove the last slash, that meant that the last() array item would always be the parent.

SharePoint always presents paths with an absolute leading slash (root) then any folder structure, then a final slash.

That said, if one wanted to be truly data agnostic and get a parent one would:

1. Check if the path was a slash and length 1 (root)
2. If not check the first and last characters, and if they were a slash, remove them
   1. Then split the remainder (which may be one word) by the slash
   2. Take the last value

That might even be a shorter path to it, to be fair.

EDIT - LOL ... I was a bit wrong with my SP assertations, but none of the logic is flawed ... excerpt of created/modified trigger 😅

{
    "{Name}": "evidence of Book aa",
    "{FilenameWithExtension}": "evidence of Book aa.jpg",
    "{Path}": "fileLibraryOne/",
    "{FullPath}": "fileLibraryOne/evidence of Book aa.jpg",
}

[ppleos_]

Hi @eliotcole ,

Thanks for the detailed answer. After checking, I find that when working with "Apply to each", the path cannot be obtained.

The process flow of the whole event is like this:

Created flow in Automate:

I'm new to Power Automate, please let me know how to solve this issue. Appreciate on your effort. Thanks both @Expiscornovus @eliotcole 

[eliotcole]

You should no longer need that Apply to each, @ppleos_, the logic is such that you only have one file, here.

That looks like leftover logic from the before times. 😉

Also, put the book condition in the trigger as a Trigger Condition, and it will only ever fire if the name contains Book. This will save you many repeat triggers of the flow. 🙂

Also Also ( 😅 ) ... you can't move/copy this file to the same Library without firing the trigger again ... *whatever* you do, here. So you will need to add a (hidden) Choice Column to the library in question, called 'flowmoved' where the choices are 'Yes' and 'No', and the default answer is 'No'. Then, when you move the file, ensure that this is changed to 'Yes'. This then necessitates an additional trigger condition, that ensures that '@equals(triggerOutputs()?['body/flowmoved/Value'], 'NO')' to be added to raise the amount of triggers to 2.

Ignore the 'strikeout' text above, I should have an example for you, shortly in a new post.

[eliotcole]

Why do you need this value, @ppleos_ ?

Ah, I see, you're checking the parent folder, to see if the file has already been moved. OK, that's doable, we can fit that into the trigger, too. 🙂

[Expiscornovus]

Hi @eliotcole,

I had another look at the get parent folder question.

I have taken a different approach by using the REST API. There seems to be a ParentUniqueId value in the fieldValueAsText collection property. You can use that GUID to retrieve the FileLeafRef of the parent folder.

I see in the meantime you guys already carried on and I see you are close to a solution with a trigger condition.

So, I am just sharing this but obviously ignore this if it is not relevant for the approach you guys are taking 😁

[eliotcole]

I was going to do this, but found that it's still inefficient, since it would still run on everything.

I'm shortly to post a solution which won't fire unless specific conditions are met.

[eliotcole · Accepted solution]

Right, @ppleos_, this is a Two Action flow solution for you!

An important thing to note before I get into this is that any flow with this type of trigger does not run when a file is moved, so that effectively eliminates the worry about checking moved files.

It doesn't eliminate the need to check that a modified file is one that is already in the desired place, though, so the path is still a required calculation.

I have made a subfolder called 'booksFolder' where I'm moving the files.

This can all be done, though within the trigger.

Trigger On 'Book' And Not In Specific Path

Here's the two step flow:

I will include a copy of that Compose action at the end there for you at the end of this.

For the flow to work, I need to ensure:

1. That the file name contains 'Books'
   
   
2. That the file being checked is not inside 'booksFolder'

So I will need two Trigger Conditions on my When a file is created or modified (properties only) trigger to ensure this.

The Two Trigger Conditions

Check name contains 'Books'	@contains(triggerOutputs()?['body/{Name}'], 'Book')
Ensure folder is not 'booksFolder'	@not(equals(last(take(split(triggerOutputs()?['body/{Path}'], '/'), sub(length(split(triggerOutputs()?['body/{Path}'], '/')), 1))), 'booksFolder'))

Those expressions are fanned out in the below spoiler:

Spoiler

Fanned out expressions

Check name contains 'Books'	@contains( triggerOutputs()?['body/{Name}'], 'Book' )
Ensure folder is not 'booksFolder'	not( equals( last( take( split( triggerOutputs()?['body/{Path}'], '/' ), sub( length( split( triggerOutputs()?['body/{Path}'], '/' ) ), 1 ) ) ), 'booksFolder' ) )

As you can see, the 'booksFolder' one looks complicated, but when you fan it out, it's actually quite simple:

1. Inside the take() it takes the amount of items once split() minus 1.
2. Then it only looks at the last item in the resultant array.
3. If that is equals() to 'booksFolder' it will return true, so we need to reverse that otherwise the flow will ONLY run on files inside booksFolder.
4. The not() reverses it.

I can further restrict this trigger by using the Folder field in the trigger. Whatever value that is chosen there will ensure that this flow only runs on items placed in that folder.

If you include a Folder field value, this could mean that you don't need to worry about the second trigger condition. This is because the flow will only run on files that are created or modified in the specific folder chosen here.

To add the trigger conditions I've placed the steps in the below spoiler in case you already know how to do this:

Spoiler

1	Go into the Trigger's menu, and select Settings.
2	Tap 'Add' and paste the expression in, ensuring it has an @ at the start.

If you need any help with Trigger Conditions there is a lot around, just remember that they're just normal expressions with an @ and they are always on triggerOutput values. So you can test them in your flow anywhere, and for really basic ones, like the 'contains Book' one, build it using the Filter action, then copy it from the advanced bit there.

Obviously each separate Trigger Condition means that you have an extra condition that needs to be true, so you are in effect making a HUGE and() expression here. 😉

The move action is the same as yours:

In the below spoiler is the text for that Condition if you just want to play with stuff, paste it into a condition for testing.

Spoiler

ONLY RUN ON FILES THAT HAVE A NAME FIELD CONTAINING:Book
@contains(triggerOutputs()?['body/{Name}'], 'Book')

ONLY RUN ON BRAND NEW ITEMS
@equals(triggerOutputs()?['body/{VersionNumber}'], '1.0')

ONLY RUN ON FILES NOT IN THE FOLDER:booksFolder
@not(equals(last(take(split(triggerOutputs()?['body/{Path}'], '/'), sub(length(split(triggerOutputs()?['body/{Path}'], '/')), 1))), 'booksFolder'))

============
If you need a separate flow or a user needs to make a change before this flow runs, then the 'BRAND NEW ITEMS' trigger is ...
IF YOUR LIBRARY HAS MAJOR VERSIONS
@equals(triggerOutputs()?['body/{VersionNumber}'], '2.0')

IF YOUR LIBRARY HAS MINOR VERSIONS
@equals(xpath(xml(concat('<r><n>', triggerOutputs()?['body/{VersionNumber}'], '</n></r>')), 'floor(/r/n)'), 1)

If you want to immediately check whether those conditions work, simply triple click the line, copy it, then paste it onto a new line and it should paste in as an expression!