Get items when target absent

[SITech1265]

An array holds unique countries extracted from M365 contacts. I'm trying to check that each country in the array is present in a Microsoft List. There are two unexpected results.

1) Côte d'Ivoire - the only spelling option for that country in 365 contacts. The Get Items filter fails to match it and the flow follows the "has failed" parallel branch in the flow and can be dealt with there. 

2) Missing countries - if the target country is missing from the list, the flow goes to the success branch! However, once in the success branch the flow is dead. No action in that branch works. The returned value from Get Items for a missing country matches the filter value, just as when the country exists - so a comparison of the returned value with the filter is futile.

Get Items filter is: Title eq '@{variables('varCountry')}'

Any help appreciated, especially for the missing countries.

[ManishSolanki · Accepted solution]

Hi @SITech1265 

We need to replace single quote with two single quotes in value when using odata query. To fix the first issue, pls use the below filter query:

Title eq '@{replace(variables('varCountry'),'''','''''')}'

For second issue, I would suggest you to add another "Get items" action and write filter query to get items whose Title field is blank and add further actions.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[SITech1265]

Thank you @ManishSolanki 

First issue resolved as you said.

I don't get the logic for the missing targets. Suppose I'm searching the List for Canada. The challenge is not that the Canada item has a blank title, but that no item exists in the list for Canada. 

[ManishSolanki]

Hi @SITech1265 

So you mean, for each country there is a sperate list like separate list for Canada, USA etc. I was assuming the list is common for all the countries and we need to look for country value in title field to check if entries exist or not. But If there is a common list, then how will we identify if entries for a country do not exist. Pls give some more details with example.

Thanks

[SITech1265]

@ManishSolanki 

Entity Codes is an MS List that is essentially a look up table for various kinds of information. Currently it has 4 columns: the type of entity - in this example and in our problem, the entity type is Country. The second column is the Title, the 3rd the ISO 2 letter internet code, and the last a reference to a regional contact.

One use is the creation of dynamic email addresses such as office.code@...,eg, office.ph@

Before proceeding with a task we want to check that all target countries are in Entity Codes so the flow will be able to look up the needed data. A flow retrieves the list of target countries from MS365 contacts and reduces them to an array of unique countries. We want the flow to check that each of those unique target countries is in the Entity Codes list.

The problem is when Get Items looks for a country that does not exist in Entity Codes. Sometimes we get an error message "Unexpected error. Request to Azure Resource Manager failed with error: '{"error":{"code":"ActionRepetitionNotFound","message":"The specified repetition '000020-000000' of workflow action 'Condition' is not found."}}'." Other times there is no message.

I had expected that when an item could not be found, the flow would follow the fail branch, not the success branch. To follow success, but then fail effectively preventing any further steps in the "Apply to each" is a surprise that I don't know how to resolve.

"Absence of evidence is not evidence of absence"

Regards

[SITech1265 · Accepted solution]

Hi @ManishSolanki 

The solution is in How to Check if an Item Exists in a SharePoint List with Power Automate - Power Tech Tips and is different from most similar posts that presume the use of item ID. How can you know what ID to look for if the item has never existed?

Modify the condition expression to suit your Get Items : 

length(outputs('Get_items_from_Entity_Codes')?['body/value'])

is greater than or equal to

1

If yes, the item exists; if no it doesn't exist. Take following steps accordingly.

A fuller treatment of the solution is on the Power Tech Tips link above.

Thanks for your help