Managing null array

[bug289]

Okay, so I've been battling this for a few weeks and searched everywhere I can think of. There are some solution I've tried but I am still struggling to get it to work.

Background:

I have a SharePoint Document library that I pull in and filter down to those added in the last 7 days.

I have a SharePoint list, which holds information on what people want to be notified about, which may contain blank information. Basically, there are several 'theme' columns and they can choose one or many items in each column. They can also leave it blank (see red in image):

I want to go through and build a table of items for each person and email it out.

I have built the majority of the flow, which works fine if the fields in the notification log are not blank. When they are blank however, if fails because the 'array' of notification items is blank. It fails at a select because it can't be blank.

The problem with there being 7 columns that could potentially be blank, I really don't want to manage it with conditions, the flow if already monumental with multiple parallel flows going on.

So, is there a way to check in the 'From' of the Select, and make it a blank array if it is null? Or maybe, when I 'Get items', if that one is null, I could set the array(s) then before I enter the loop? Or something more sophisticated?

For context, here is the section of the flow I'm dealing with:

[DamoBird365]

Hi @bug289 

You could try coalesce.  Something like follows:

coalesce(outputs('Get_item')?['body']?['doesnotexist'],createArray(''))

Coalesce returns the first non null response.  Either the path that might not exist, OR an empty array.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts. Thanks

[bug289]

Hi DamoBird365,

Thanks for the suggestion, it is a new formula for me, but I'm not sure how to make it work here. With the Select I am selecting a specific field from the table returned by the 'Get Items'. I need to be able to build it into a tidy Array, with all the multiple choice items in little arrays that I can then query to select the right items for the output table. If that makes sense?

[DamoBird365]

Hi @bug289 

I'm not sure I do follow at the moment.  I've assumed that you have a Choice Type field in your list and allowing multiple selection (and presumably multiple fields).  

You are then looping through all items in an apply to each and using a select on each column, you are running item()['value'], maybe item() ? - i am not sure as I cannot see from your screenshot?  What are you trying to output via the select?

I have done this based on the following List and I get no errors.

I note that you are then adding these arrays into an object in compose16, what are you trying to achieve?

Your question was "So, is there a way to check in the 'From' of the Select, and make it a blank array if it is null? Or maybe, when I 'Get items', if that one is null, I could set the array(s) then before I enter the loop? Or something more sophisticated?". 

Coalesce will return the first non null value from a list.  If your select 17 is based on HSW Topic, you can take that expression and insert it into the coalesce and if it is null or empty, you can return another array. 

Albeit I think it would be best to try and understand why or what you are trying to achieve, what data is going in and what data do you expect out?  Some screenshots or maybe sample array.  There may be an easier way.

Cheers,

Damien

[bug289 · Accepted solution]

Hi Damien,

You inferred everything correctly, I just wasn't sure how to convert to just the HSW Topics column in the formula, instead of 'Get items body' which I presume finds the first blank overall.

As it turns out, after some further investigation, it was me being an idiot. I hadn't set the columns to be multi-choice, so it wasn't drawing it through as an array but as an Object, which errored in the Select, so I must have changed it to 'value' at some point, which meant it was coming through as a string. I think I have now resolved it but I need to go through and check the follow-on of the flow.

Thank you so much for the response, it has taught me something else I didn't know existed. You may have figured out I have a lot to learn ;).

Danielle

[v-xiaochen-msft]

Hi @bug289 ，

If everything works fine afterwards, please consider marking the correct reply as a solution.

Best Regards,

Wearsky

[odl001]

Hi @DamoBird365 
I'm having almost similar issue where I am trying to pull data from a multi-select field in Sharepoint (taken as an array in Power Automate) and join them as string. It's working well if there is a value but throwing an error when it is empty.

I used item()?['value'] in the Select PPIM step and joining the output with a comma (; ) as a separator.