Matching values evaluating as false

[cmertz]

I have a flow that is meant to check two lists against each other and compare whether a key value is present in the other list. For some reason some of these values are not being considered equivalent despite being identical to each other. I do not know why this is happening.

 The two Sharepoint records that are evaluating as unique values despite the ADO IDs column (which is the one being checked).

The loop breaks down as follows:

Tick Deletion Indicator (TDI): Loops through every record in the first pictured Sharepoint list

Apply to Each 2 (ATE): Loops through every records in the second pictured Sharepoint list

Condition 2: Compares TDI ADO IDs (cast as string) to ATE ADO IDs and ATO Parent IDs (cast as string). If they match, the deletion indicator is swapped to "No".

Condition: Compares TDI ADO IDs (cast as string) to ATE ADO IDs (cast as string). If they match, the Found in Smartsheet option is set to "Yes".

These conditions should evaluate as True for the above example, yet both of these records are marked incorrectly. I am unsure why this is happening and guidance would be greatly appreciated.

[rzaneti]

Hi @cmertz ,

It is possible that you have a hidden blank space in one of the strings, which results in a difference between the values. To see exactly how Power Automate is reading the values, you can access the raw outputs from each 'Get items' action (if you prefer, just to make it easy to isolate the desired List record, you can also add the current looped item from the 'Apply to each' into a 'Compose' action and get the outputs). If you don't know how to access the raw outputs, follow the steps below:

First, go to the 'flow details' page and click in one of the previous runs:

Once you do it, you will be in the 'flow run page', which may look like this:

Go to the 'Get items' actions and click in it to expand the details. Then click in "Show raw outputs" in the OUTPUT section. In some cases, this button appears as 'Click to download':

It will display a sidebar on the right side of your screen containing this JSON notation (if you had the 'click to download' option, it will open the outputs in a new tab):

Find the "body" property. It is always after the "headers" property. The "body" property may have a "value" property, which has one entry per each record from your SP List. We need just a small sample of the values that you are trying to compare. You can use the 'CTRL+F' to search for the property value. 

Make sure to remove any sensitive content from the image before sharing. You also can find more details about how to access the outputs in Power Automate in this article that I wrote recently: http://digitalmill.net/2023/06/29/how-to-access-the-raw-outputs-in-power-automate/

[cmertz]

Hi @rzaneti!

Looking at the raw outputs from the lists, these values still appear identical. No whitespaces or anything that may not show up from looking at the records. I've included screenshots of the values below.

[rzaneti]

Hi @cmertz ,

You are right: the values are identical and should be captured by Power Automate. Could you please share the text of the string() expressions that you are using in your Conditions?

Also, I recommend you to include two composes into your 'Apply to each 2' action, assigning each of the ADOIDs column dynamic column to each of them. It will allow us to visualize how does these values look like in the loop iteration where they should be matching. 

[cmertz]

Hi @rzaneti,

Below I will list the string functions in order of top down, left to right. I was casting each item as a string to try and rule out the possibility of a type issue.

string(items('Tick_deletion_indicator')?['ADOIDs'])

string(items('apply_to_each_2')?['ADOIDs'])

string(items('Tick_deletion_indicator')?['ADOIDs'])

string(items('apply_to_each_2')?['parentids'])

string(items('Tick_deletion_indicator')?['ADOIDs'])

string(items('apply_to_each_2')?['ADOIDs'])

I will attach screenshots of the compose functions later, I removed parallelism in the flow as it was running into some runtime errors so it may take some time to get these values.

[cmertz]

I ultimately had to restructure the flow to avoid runtime issues (8+ hours without resolving). However, upon further investigation it appears that the records that aren't being matched up are not being checked at all. The Apply to Each loop that looks at my PDT Data Sharepoint list is looking through 100 records, meanwhile the actual list contains 129. I have the default settings on my Get Items for that list, so it should be polling all records. When I looked at raw outputs however, the values were not there. Why would this be happening?

 

[rzaneti]

Hi @cmertz ,

By default, the 'Get items' action returns only 100 records. You can increase this limit up to 5k records by setting a pagination. First, access your 'Get items' action settings:

Toggle 'Pagination' to on and set the Threshold to a number above your qty of records:

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 

[cmertz · Accepted solution]

Two changes were made to fix the problem:

1) Rather than using nested loops to find matching values, a Filter Array action was used to identify whether a match was found or not. This greatly reduced run-time and seemed to more accurately identify matches.

2) Sharepoint lists have a hidden default of 100 items. This isn't clear to users as the actions says the default is to return all items in the list but this is a Microsoft product so who cares about the user. By going into the action settings, enabling pagination, and setting the max items higher (300 in this example) the list returned all values I was looking for.