Sharepoint get item/update item with conditional check - makes an entry for each item in the list?

[Seuadr]

Hi all, 

I'm working on a flow that takes email alarm messages.

here is an example "single message" email:

Thursday, 04/08/2021 at 14:14:24. The Point ANT HVAC4B.DISCH AIR is at NORMAL.

here is how it is set up:

Trigger is a new email, then i get a list of items from the sharepoint, then compare the subject of the email to branch yes is "multiple messages" no is "single message":

now - i have the same problem with multiple or single, so, i'm going to stick to single for this explanation cause it is simpler:

split on the splits the message in two at 'Point', then step1 trims it so it becomes:

ANT HVAC4B.DISCH AIR is at NORMAL.

then the compose action which i neglected to name splits that at is to give:

ANT HVAC4B.DISCH AIR

i then use the get value to compare the Title of the entry, to the output of the compose to see if the entry starts with it:

if it does, then, get the id of that entry and update the item with the step1 compose, if it does not, then create the item with the step1 compose:

in this particular case, it does match, but, as you can see from an earlier run, it had a create instead of an update and this is where things go beyond my intention: it creates a new entry for each existing list item - i just want it to create a new entry once. I think it is because of the "Apply to each 3", but, when i add the id to the "yes" branch it automatically nests it in the apply to each.

here is the output from the item that created 4 entries instead of 1:

output from the email trigger:
"receivedDateTime":"2021-04-08T19:06:46+00:00","hasAttachments":false,"internetMessageId":"<BL0PR12MB25314BB7D5C97684A4D4A2A4A8749@BL0PR12MB2531.namprd12.prod.outlook.com>","subject":"Thursday, 04/08/2021 at 09:40:18. The Point ANT CHW.CW PUMP ALARM is at NORMAL.","bodyPreview":"Thursday, 04/08/2021 at 09:40:18. The Point ANT CHW.CW PUMP ALARM is at NORMAL.","importance":"normal","conversationId":"AAQkAGJlZDM0NDk4LTUxODYtNDI0Ny1iMjM3LTRjZTgzYjA2NTE5NAAQAI2KcB07AjdFtSMmwslPAjI=","isRead":false,"isHtml":false,"body":"Thursday, 04/08/2021 at 09:40:18. The Point ANT CHW.CW PUMP ALARM is at NORMAL.\r\n\r\n\r\n","from":"IPF.SA.HVACFlow@msu.edu","toRecipients":"IPF.SA.HVACFlow@msu.edu"

so it does it 4 times, because there were 4 items in the list when it did the "apply to each 3" action.

not sure what to do differently to get it to iterate through the list (ultimately, there are likely to be hundreds of entries at any given time once this is live) but only create 1 entry, not a new item on every iteration?

i thought about relocating the get items, but, i need that to do the conditional check, so i'm not sure how i'd make that work?

any input would be great

[DamoBird365]

Hi @Seuadr 

I'm guessing the real problem is your expression?  1 should evaulate as false and the rest true but all 4 are evaluating as false and therefore creating a new item?

I would suggest you stick a compose in temporarily for the apply to each 3 title and see what string is output (as you cannot see this from the condition history).  Then I would compare your select string and the title string with your own eye and try and spot the difference?  An extra space or maybe case?  You can change the case of both strings to upper or lower (ToLower or ToUpper).

Give that a go and let me know what you find.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Seuadr]

i'll give that a try - i now have 10 items in the list, so the next message that doesn't exist in the list will make 10 entries 😕

[Seuadr]

@DamoBird365 So i tried putting a compose between the create and updates in the apply to each and it is as i suspected, it is checking each item to see if it matches with the "title" from "get items" and if it doesn't, then it is creating it. I am pretty sure it is because it is inside the apply for each, but, if the condition is no, i do want the item to be added once, so, i'm not sure how to make it check ALL of the list items and only add it once instead of checking each list item?

[Seuadr]

so, to expand on this - my expression is "starts with" - here is a run with 2 list items.. item 1:

no match, create an item.

item 2:

match, update item.
now here is a run with 3 list items - this is a new item, so no update:

because it checked 3 items, it created 3 items. not sure how to have it check the entire list and only create 1 item?

[DamoBird365]

Hi @Seuadr 

I think I get you now.  Instead of an apply to each, how about a filter.  If the filter contains a match the length will be greater than 0.  The condition is therefore based on the length rather than each item of the list.

Does that work for you?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Seuadr]

i'll look in to that after this power platform training. that sounds like something that makes a lot of sense. 

if i am following you correctly, i'd put the get items into a filter like the data "filter array" and then have the condition do a match there? 

[Seuadr]

@DamoBird365 finally got back to this - i am structuring my filter was wrong and was hoping that you could give me some input:

so my "value"  from is output('Get_items')?['body/value']
the filter uses item()?['Title'] - which is the entry on the list i'm looking to filter

then starts with outputs('Compose') which is the compose action that strips it down to just the first part of the split for comparison (which is what it starts with...)

so, for my test run, my inputs from the sharepoint list are:

data.etag": "\"1\"",
        "ItemInternalId": "2520",
        "ID": 2520,
        "Title": "ANT REF 1330 MEAT POULTRY is in Alarm at PRI3",
        "Timestamp": "2021-04-12T12:00:00Z",
        "Modified": "2021-04-12T12:12:54Z",
        "Created": "2021-04-12T12:12:54Z",

"@odata.etag": "\"1\"",
        "ItemInternalId": "2521",
        "ID": 2521,
        "Title": "ANT REF 1330 MEAT POULTRY is in Alarm at PRI3.",
        "Timestamp": "2021-04-12T12:01:44Z",
        "Modified": "2021-04-12T12:18:12Z",
        "Created": "2021-04-12T12:18:12Z",

and my compose was:
ANT RM 1330.RM TEMP

but my output from the filter was:

[ ]

and since the input to the condition following for length doesn't trigger because (presumably) it has a null?

[DamoBird365]

@Seuadr I cannot see a title in your example starting with ANT RM 1330.RM TEMP

[Seuadr]

right, so if it isn't in the list, i need to create the item.

so i need to figure out how to work with that

[DamoBird365 · Accepted solution]

Hi @Seuadr 

Have your condition after the filter.  If the length gt 0 i.e. YES then update, else NO create new?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Seuadr]

@DamoBird365 that is what i have - but, for some reason the condition isn't triggering - i presumed it was because the array is returning [ ]

not sure what i am doing wrong?

[DamoBird365]

Hi @Seuadr 

Not sure if we are misunderstanding each other here but if you are now using a filter on the results, you don't need to use an apply to each to check them all?  The filter will let you know if there is a match or not and so the condition and the yes no branches do not need to be in an apply to each?  Why are you creating an apply to each with the body as the source?

Damien

[Seuadr]

when i add the ID from get_items it automatically nests it in an apply to each - i have to add the id to update item, so, :shrugs:

[DamoBird365]

Hi @Seuadr 

You will be using the dynamic content ID from the Get Items, in which case it will automatically assume an apply to each as the result is an array?  If the length is greater than 0 your filter array should have the ID e.g. 2520, you can obtain this with the expression first(body('Filter_array')?['Id']), you might get away with body('Filter_array')?['Id'].

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Seuadr]

@DamoBird365 i am getting an error when trying to add the expression:

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Compose_3' at line '1 and column '5795' 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_3', or be a Trigger.'.'.

compose_3 was the compose i put in to "see" the output of the expression before putting it in the update item. 

i may be misunderstanding, but, compose 3 is under the yes of the conditional check for length, i don't understand how i could have the filter array in the run after path for that? i also can't add the expression directly to the id, nor a dynamic content from the filter array to the id.

i feel like based on your description this should be straight forward and i'm missing something?

[Seuadr]

oddly enough, i deleted it and re-added it as a new compose and now it is happy? whatever i guess 😄