Get Item vs Get Items - both not too helpful :/

[Holly_CMS]

Hi All! Happy New Year 🙂

This is a simple flow yet the Get Item(s) actions have been a bitty challenging for me. 

Get Item action would pass successfully but it loops. Not sure how to stop that....However, I am not able to filter to a specific field - ErrorTypes - that are null in that action...

So that takes me to Get Items action....

Get Items action....I had to use the custom field for ErrorTypes that is in the SP list but would NOT populate in the list of Dynamic Value. Yet that keeps failing as does not recognize - "The field 'ErrorTypes' of type 'Note' cannot be used in the query filter expression'.  WHY????!!!!! That field having NO data is the point of the flow being created. 

How do I get this going?? 

[creativeopinion]

@Holly_CMS The Get Item action will return a single item. The Get Items action returns all items (unless you use the Filter Query—to specify which items you want returned) from a SP LIst.

What are you trying to accomplish?

The Get Item action requires an ID to retrieve a specific item from a specific list. In this case, the list would have to be the same list that you've selected in the flow trigger.

For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

----

Take a look a this YT Tutorial I recently uploaded: 3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow

In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:

1️⃣ Looping through a Single Item

2️⃣ Creating Unnecessary Nested Loops

3️⃣ Looping through an Unfiltered Array

At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.

I'll also cover:

✅ How to avoid the Apply to Each action with a single item array

✅ How to use the item() function to access dynamic content in an array

✅ How to prevent unnecessary nested Apply to Each action loops

✅ How to use the Select action

✅ How to convert an array to a string with the Select action

✅How to use the Filter Query field

✅ How to count the number of items in an array

✅ How to use a condition control

✅ How to use the concurrency control

✅ How to set a top count

✅ How to use Compose actions for troubleshooting

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! 

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[Pstork1]

The problem with the Get Items action is that you can't query on null fields in a Get items because those fields are not sent to decrease the size of the data package.  You can do a filter array to select rows with a null value in a specific field, but that is only after you have retrieved all the items using Get Items.  I'm also not sure what kind of column you are using for ErrorTypes. SharePoint doesn't have a Notes column type.

[Holly_CMS]

I am aware of the differences of these two actions and that is not what I am asking for. I am asking for assistance to get either one of them to work as I need the flow to work.

The goal is to get the values from other fields within the same SP List to combine into one sentence in the ErrorTypes field. I do not know why Power Automate called the field NOTE in the error message but it is a multiple lines of text field. 

As you can see in the pictures of all the fields below that I want combined and put in the Error Types field. The pink highlights shows the combined and the yellow as NULLS. The reason for the Nulls is because in another flow (and we cannot alter that flow due to the complexity and difficulties to fix it every time I update it. Hence this new short flow) is updating the list AFTER the approval flow, not before it goes out before approval....With that said, it's causing some of the rows have nulls in the field and then the report comes out blank....and I can't build a Power BI without these values yet....

I hope this clarifies for your question of accomplishment.

[creativeopinion]

@Holly_CMS To clarify, you are looking to list the column name(s) in the Error Type column as a string of text if the value of the is true. Is that correct?

[Holly_CMS]

please see my reply above (under CreativeOpinion)....especially regarding the Notes Column type....

see error message on the right....

And I have used fields that have Nulls in the Get Items filters in other flows without a problem....

[Holly_CMS]

No, the values from the multiple fields into one in ErrorTypes Field....

In the ErrorTypesCompose action....

concat(if(equals(outputs('Get_Items')?['body/DuplicateNoteType'],'True'),concat('Duplicate Note Type', ', '),''),

if(equals(outputs('Get_Items')?['body/HighMinutes'],'True'),concat('High Minutes', ', '),''), 

if(equals(outputs('Get_Items')?['body/HighUnits'],'True'),concat('High Units', ', '),''), 

if(equals(outputs('Get_Items')?['body/LongTimeNoUnits'],'True'),concat('Long Time With No Units', ', '),''), 

if(equals(outputs('Get_Items')?['body/Short_Counseling'],'True'),concat('Short Counseling', ', '),''), 

if(equals(outputs('Get_Items')?['body/ZeroUnits'],'True'),concat('Zero Units', ', '),''), 

if(equals(outputs('Get_Items')?['body/NegativeMinutes'],'True'),concat('Negative Minutes', ', '),''), 

if(equals(outputs('Get_Items')?['body/NoContactWith'],'True'),concat('No Contact With', ', '),''), 

if(equals(outputs('Get_Items')?['body/HighCharLowTime'],'True'),concat('Long Content - Low Time', ', '),''), 

if(equals(outputs('Get_Items')?['body/LowCharHighTime'],'True'),concat('Short Content - High Time', ', '),''), 

if(equals(outputs('Get_Items')?['body/MontanaZeroBU'],'True'),concat('Montana Zero BU', ', '),''), 

if(equals(outputs('Get_Items')?['body/LateEntry'],'True'),concat('Late Entry', ', '),''), 

if(equals(outputs('Get_Items')?['body/AZFocusArea'],'True'),'AZ Focus Area', ''))

In the FinalErrorTypesCompose action....

substring(trim(outputs('ErrorTypesCompose')), 0, sub(length(trim(outputs('ErrorTypesCompose'))), 1))

Then in the Update Item action, the output is added to the ErrorTypes Field...

[creativeopinion]

@Holly_CMS Can you confirm the type of column you are using for the Columns you have in your screenshot? Are they a single line text field?

[Holly_CMS]

The ones in the True/False columns - those are the single line of text

the ErrorTypes field is a multiple lines of text. 

[Holly_CMS]

I just changed up the flow a little and will see what it looks like tomorrow morning when the new rows of data comes in....I just know it didn't kick off the warning loop message this time...The When an item is created action.....

[creativeopinion]

@Holly_CMS After reviewing your expression it does look like you are only wanting to return the names of the columns where the value is True (and not if the value is False). In this case I would recommend

Creating an array of your column data as I've shown below. 

The output of this Compose action looks like this.

Then, with a Filter Array action you can filter out any blank columns.

Run a test. The output of the Filter Array action should not display any blank items.

In another Compose action, check the count of items returned from the Filter Array action.

Initialize a String variable.

Add a Condition check to see if the number of items is equal to 0. If so, you can set a value, if not (and there are items) set the variable with the column data.

Sorry, I'm still unclear on your issue. Did you want to output a value if the ErrorTypes is blank?.... I've set the variable with a string of text "None".

If there are items, use the Join function to convert the Array into a String separated by a comma. 

Run a test. The Join action will convert the array to a string and separate each item with a comma (and it won't include a trailing comma that you need to remove).

In the Update Item action, insert the variable into the ErrorTypes field. If you are using the Get Item action, the Update Item action shouldn't be nested inside an Apply to Each loop. 

If you started editing this flow in the New Designer and switched over to the Classic Designer, I'd recommend creating a brand new flow and switching to the Classic Designer BEFORE you make any edits. The New Designer still has a few bugs.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! 

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[Pstork1]

Ok, that clarifies things. 

1) You can't use a multiline text field in an OData query. That's why you are getting the error message you are getting.  As I mentioned you'll need to use a Filter Array instead.  

2) If you are filtering out the blank values because you need something to concatenate for a report then take a look at the Coalesce() function.  Putting each field inside a Coalesce when you concatenate them would let you specify a default value if the field is null.  That way you don't need to do all the filtering.  Power Automate Functions: Coalesce (Handle Null Values from Objects) - FlowJoe.io

[Holly_CMS · Accepted solution]

You are correct on the column names that I need returned - my brain was fried yesterday 😕

So I checked my flow this morning and it failed. That because of the Apply to Each so I took the Update Item action out of it and deleted the Apply to Each and it worked. It started to fill in the ErrorTypes field with values in it 🙂

In the meantime, I am checking out your suggestion and will test that as another alternative for others that may need it and for my future reference if my flow still bombs. I will let you know. Thank you so much for your time in helping me!!