Selecting elements of a filter array

[Seuadr]

Hi all,

you folks have been very helpful over the last few days in helping me build this flow - i appreciate it greatly!

So i have a flow that reads an email, pulls some data out of the email, and then i want it to either create or update an item in a sharepoint list based on if the item already exists. I've been advised to use a filter array to find if an item exists, and then i need to use the ID from the filtered array to use in the update item action for the sharepoint list. 

the first time i did this, it worked fine, but, now i am getting an error:

The 'inputs.parameters' of workflow operation 'Update_item_3' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'id' is required to be of type 'Integer/int64'. The source type/format 'Array' of the value '[{"@odata.etag":"\"1\"","ItemInternalId":"2579","ID":2579,"Title":"ANT HVAC4A.DISCH AIR is in Alarm at PRI3.","Timestamp":"2021-04-13T14:15:34Z","Modified":"2021-04-13T14:15:38Z","Created":"2021-04-13T14:15:38Z","Author":

i was using the following expression to get the ID:
first(body('Filter_array')?['ID'])

here is a short example of an output from the filtered array:

{
    "body": [
        {
            "@odata.etag": "\"1\"",
            "ItemInternalId": "2579",
            "ID": 2579,
            "Title": "ANT HVAC4A.DISCH AIR is in Alarm at PRI3.",
            "Timestamp": "2021-04-13T14:15:34Z",
            "Modified": "2021-04-13T14:15:38Z",
            "Created": "2021-04-13T14:15:38Z",
            "Author": {
                "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                "Claims": "i:0#.f|membership|,
                "DisplayName": 
                "Email": 
                "Picture": 
                "Department": 
                "JobTitle": null
            },
            "Author#Claims": "i:0#.f|membership|,
            "Editor": {
                "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                "Claims": "i:0#.f|membership|,
                "DisplayName": ,
                "Email": "
                "Picture": "
                "Department": 
                "JobTitle": null
            },
            "Editor#Claims": "i:0#.f|membership|
            "{Identifier}": "Lists%252fANT%2bAlarms%252f2579_.000",
            "{IsFolder}": false,
            "{Thumbnail}": {
                "Large": null,
                "Medium": null,
                "Small": null
            },
            "{Link}": 
            "{Name}": "ANT HVAC4A.DISCH AIR is in Alarm at PRI3.",
            "{FilenameWithExtension}": "ANT HVAC4A.DISCH AIR is in Alarm at PRI3.",
            "{Path}": "Lists/ANT Alarms/",
            "{FullPath}": "Lists/ANT Alarms/2579_.000",
            "{HasAttachments}": false,
            "{VersionNumber}": "1.0"
        }
    ]
}

"ID" or "ItemInternalId" would be fine, i just need to figure out how to select them as part of the elements in the array?

[Pstork1]

I think your closing parentheses is in the wrong place. Try this instead:

first(body('Filter_array'))?['ID']

[Seuadr]

@Pstork1 that gives me an error (which disappeared last time by deleting and readding the compose - but does not appear to be working the same this time):

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Compose_5' at line '1 and column '6227' cannot reference action 'Filter_array'. Action 'Filter_array' must either be in 'runAfter' path or within a scope action on the 'runAfter' path of action 'Compose_5', or be a Trigger.'.'

i don't know how i can have the output of the filter array in the run path after the compose?

[Pstork1]

The runAfter path has nothing to do with the formula inside the Compose.  You are evidently setting the Run after settings for some of your actions and the error says essentially that the Compose and filter array aren't in the same path through the flow.  Can you provide a screenshot of that section of the flow and check what the run after settings are set to?

[Seuadr]

fair enough - here is the flow, compose 5 comes directly after filter array 2:

here are their "run after" settings:

i didn't actually change these, but clicked in to it and cancelled and those were whatever the default selection is?

[Pstork1]

Not sure where that error message is coming from then.  What do you get in Compose 5 if you just set it for

body('Filter_array')

It might be that your filter is returning no results so there isn't a First()

[Seuadr]

@Pstork1 same error. i can delete the compose all together and then add a new one, same error. selecting the body from dynamic content works though?

[Pstork1]

Yes, but what does the output of the body look like in a successful run?  

[Seuadr]

[
  {
    "@odata.etag": "\"1\"",
    "ItemInternalId": "2579",
    "ID": 2579,
    "Title": "ANT HVAC4A.DISCH AIR is in Alarm at PRI3.",
    "Timestamp": "2021-04-13T14:15:34Z",
    "Modified": "2021-04-13T14:15:38Z",
    "Created": "2021-04-13T14:15:38Z",
  }
]

[Pstork1 · Accepted solution]

If that is what the output from the Filter Array looks like then this should give you the ID value of the first record.  Replace ID with any other field name, like Title, to get those values.

body('Filter_array')[0]?['ID']

[Seuadr · Accepted solution]

@Pstork1 thanks for the assistance. i guess i'll have to figure out the run after error before i can test it, as that pops up every time. I may try rebuilding it from scratch.

[Seuadr]

so i can't explain the weird runpath error thing - but when i applied the solution to a brand new flow without that baggage, it worked as intended. I'll propagate the change out to the multi-message branch of the flow and make sure that it also works, but i suspect if it does not i should start a new thread - cause that is a whole other can of worms 😛

[Seuadr]

so - i've been testing this with a brand new flow - it seems if you use a compose action with the expression, it gets mad about it and has the issue with the runafter stuff... if you put the expression in to the update item, it is fine - UNLESS you then do the compose with the expression, then you gotta delete it all and start again for it to "clear" no idea what is up with that or why, but... good to know?

[Anonymous]

Hello, may I kindly ask what does each terms in this expression mean? 

Kind regards,

O.J.

[Pstork1]

body('Filter_array') is the output of the Filter Array action, which is an array
[0] is the first record in the array
?['ID'] is the ID field in that first record.

[ak121]

emil id for the CC, I used "body ('filter_array')[0][email)". However, this query only returns the first match. Is there a way to get all possible matches (email id ) instead of just the first?

[Pstork1]

The [0] is why you get the first match. You can get successive matches using [1] or [2] etc. But there is no way to get the array of matches, which is I think what you want.  For that you need to use an Apply to Each and and Append to Array.

[ak121]

I am relatively new to the field of power automation and this is my first task. Flow sends an email to each individual, but I must include their manager's name in the cc field. There are multiple managers for each individual. Where do I need to apply each function?

[ak121]

I am relatively new to the field of power automation and this is my first task. Flow sends an email to each individual, but I must include their manager's name in the cc field. There are multiple managers for each individual. Where do I need to apply each function

[Pstork1]

If the Apply to each loop is running on each person then you would create the array of managers in a loop just before you send the email.