SharePoint Item is Created -> Update Item

[kristof805]

I have five (let's call them lists A-F) SharePoint lists that users will fill out daily. I also have a separate list (let's call it list x) that I have a Flow setup for that creates each of those lists A-F as items, daily, and sets them to incomplete (choice of complete or incomplete as one of the columns). This will serve as daily task list for the users who fill out the lists.

I'm trying to have a Flow so if a new item on lists A-F is created, I want to update list X (change the appropriate item from incomplete to complete).

However, the IDs, a required field, do not match between lists A-F and list X so I get a "Could not find list item" error. I don't really want to match their IDs. I just want to check if a new item is created with today's date on lists A-F, then I want to update list X's corresponding item from incomplete to complete. Below is a screenshot of list X. I've enabled the ID column for troubleshooting.

This is the daily task list, aka List X

Current flow that fails because IDs don't match

[faustocapellanj]

Hi @kristof805,

If you are talking about the ID column that comes out of the box in SharePoint lists, that will not work. You will need a unique identifier such as the Title column or a custom one you create.

[kristof805]

@faustocapellanj thanks for the reply. I'm a pretty big SharePoint and Flow newbie. Can you recommend a method on how I can make a unique identifier? I've disabled the Title column as I was not using it (it's hidden on both the list and when you enter a new list item). 

[faustocapellanj]

Hi @kristof805,

Before I proceed, please clear something up for me. The users go to lists A-F and every item created in these lists are also created in list X. Is that a true statement? And once these items are in both lists, where do users go to update the items are complete: list x or lists A-F?

Thanks,

Fausto Capellan, Jr

[kristof805]

The way it should work is this way: on list X a Flow creates an item for each list A-F and sets it to incomplete. This happens once a day. When people go to list A, B, C, D, E or F and create a daily item, that Flow should then go to list X, find the corresponding item and change the status to complete. If a user added an item on list A, list X's item A for that day should be changed from incomplete to complete. If a user fills out an item for list B, list X's item B should be changed from incomplete to complete.

To answer your question, the users only update lists A-F and should not be interacting with list X. The item they fill out in lists A-F is not an exact copy on list X. The list X corresponding item is just the list name as an item (list A, B, etc) with today's date and its status (incomplete/complete). Lists A-F actually contain the information.

[faustocapellanj]

@kristof805

Another question: the List X items are being created thru a scheduled flow or manually?

[kristof805]

Scheduled Flow that happens every day at 12:01AM (right after midnight). This works.

[v-xida-msft]

Hi @kristof805,

Do you want to create a flow that is used to update corresponding item in ListX for each of Lists A-F?

Is there a column which stores the list’s name in the ListX? Which type of that column?

Further, if multiple users create items in one of Lists A-F, how do you want to update the corresponding item in ListX?

I suppose that there is text column which stores the list’s name in the ListX. Please take a try with the following workaround:

Within “Apply to each”, add a Condition, click “Edit in advance mode”, type the following formula:

@and(equals(item()?['ListName'],'ListA'),equals(formatDateTime(item()?['Date'],'MM/dd/yyyy'),utcnow('MM/dd/yyyy')))

 The flow works successfully as below:

Best regards,

Kris

[faustocapellanj]

Hi @kristof805,

Can you please post a screenshot of the scheduled flow step so I can replicate?

Thanks.

[kristof805]

@v-xida-msft thanks for that Flow! I think I am really close to getting it to work. Currently it executes but all the conditions that are met are false. It loops 12 times, which is the current number of items on list X aka Daily Lists (that's the actual name of the list on my SP).

Right now I'm trying different combinations in your syntax. I'm assuming you are supposed to replace both ListName and ListA? Should ListName be replaced by Daily Lists? Should the item I'm trying to find in Daily Lists be replaced in your code where it says ListA?

Here is your original code:

@@and(equals(item()?['ListName'],'ListA'),equals(formatDateTime(item()?['Date'],'MM/dd/yyyy'),utcnow('MM/dd/yyyy'))

Here are the things I've tried and failed so far (Alarm & Vent Log is a SharePoint list (list A-F) and an item on Daily Lists:

@and(equals(item()?['Daily Lists'],'Alarm & Vent Log'),equals(formatDateTime(item()?['Date'],'MM/dd/yyyy'),utcnow('MM/dd/yyyy')))

@and(equals(item()?['ListName'],'Alarm & Vent Log'),equals(formatDateTime(item()?['Date'],'MM/dd/yyyy'),utcnow('MM/dd/yyyy')))

Everything currently comes back as false so I'm guessing somehow the match between the item creatin's list and Daily Lists is not getting matched, therefore, incomplete is not getting changed to complete for the item (we can see that it never gets to the If Yes part of the flow since everything results in a false result.

Do you have any links where I can read up on the formatting / syntax of the above code?

[kristof805]

@faustocapellanj please see attached: 

[faustocapellanj · Accepted solution]

Hi @kristof805,

If you remember from my first response to your post, I suggested to use a column as your UniqueID to identify the items between the lists. I took a different approach and this is what I came up with. For the Flow that creates the items in the Daily List, I am using the Title column as my unique ID, and the value I set to this column is this: 

concat('ListA-',formatDateTime(utcNow(),'yyyyMMdd'))

I am concatenating the name of one of the lists, which in the example above is ListA, then I take the utcNow() and format it to yyyyMMdd, which outputs the date as "20171012". The Date field is set by taking the utcNow() and format it to output the date as "10/12/2017". As for the remaining fields, I set the Task Value to the pertaining task and the Status to "Incomplete".

formatDateTime(utcNow(),'MM/dd/yyyy')

The screenshot below shows one of the steps to create the items in the Daily List.

Now onto the item creation in the other lists. The first step on the Flow is update the Title column in the item right after is created. The value of the Title column will be set to the same value used in the Recurrence Flow above. It will concatenate the name of the list, followed by today's date, which is obtained from the formatted utcNow(). This Title column will help with matching the correct item in the Daily List. See the code and screenshot below:

concat('ListA-',formatDateTime(utcNow(),'yyyyMMdd'))

Here comes the interesting part. In the next step, I am using the Get items to get all the items from the Daily List, but in it I am using the Filter Query to get any items which Title is equals to the Title column from the Update item step in the screenshot above.

After the Get items step then a Condition needs to run to see if there's an item in the Daily List with the same Title as the item in the other lists, which in my example is "ListA". In order to enter this condition, you have to edit it in advanced mode. The Condition will check if the length of the number of items retrieved from the Get items step is 1 (1 is true, 0 is false). See the Condition below:

@equals(length(body('Get_items')?['value']), 1)

 

If the Condition returns false, nothing happens and the Flow stops, but if it returns true, then the Flow updates the matching item in the Daily List by setting the Status to Complete.

If you can, give this approach a try and see if it works. Please let me know if you have any questions.

Regards,

Fausto Capellan, Jr

[kristof805]

@faustocapellanj thank you for the detailed write up. I will try this on Monday and get back to you. Thank you again!

[kristof805]

Hey @faustocapellanj !

Sorry for the long wait but I was finally able to put this together on my end and.. it didn't work (do these things every JUST work)?

This is the step it got stuck at.

So it failed on the Get items phase. I'll post a screenshot below. I can try populating the Filter Query from the When an item is created dynamic content.

I got the Title from Update Item part of the Dynamic content

Also, one problem I am going to run into this is your second action in the Flow is Update Item. While that step executed properly, I have about 10 fields people will fill out when filling out ListA. Once they hit submit and the Flow starts, the Update Item phase overwrites the fields because those fields are required to be filled out by the employees but that makes it a requirement for them to be filled out by Flow too.

I verified this and saw on ListA that the last time it was modified was identical to when the Flow ran and completed the second step.

Let me know if you find something wrong with my Step 3 or can think of a way to solve this problem (overwriting newly filed item on ListA).

I do love the "ListA-10192017" unique ID solution. I hope we can make it work. I'll be tinkering with it too.

Cheers,

Kristof

[faustocapellanj]

Hi @kristof805,

The purpose of the Update item step is to set the item Title field in ListA, but if you can find another way to set its value, you can do it outside of Flow; this will keep your fields from being overwritten. But if you need to do it from within Flow, just grab the fields value from your trigger step I just did it in Flow to maintain consistency with the naming convention from the Daily List. Now, on your Get items step, your Filter Query is wrong. You have to type the name of the column followed by eq and then followed by the name of the column available in the dynamic content. Check out the marked area in the screenshot below for my Get items step.

Please let me know if you have any questions.

Regards,

Fausto Capellan, Jr

[kristof805]

@faustocapellanj here is an update for you:

So, I took out the Update Item step since I can autofill the Title column on ListA when a new item is created by setting it up w/ a Calculated value by default:

=CONCATENATE("Pressure-",TEXT(NOW(),"yyyyMMdd"))

Although this gave me Pressure-20171020 yesterday at 5pm when it was still 10/19/2017 (US date format for October 19th) which is off by a day, I think I can fix this part fairly easy.

Onto the problem, the Flow is now failing at the Get items step:

Failed step and messageSyntax of the step, copying what you said to put

So the Get items step gets the Title (dynamic content) from When an item is created and that comes up with the correct result, as seen on the screenshot: "Pressure-20171020". However, the front part doesn't execute it just leaves the word "Title eq".

What am I missing?

[faustocapellanj]

Hi @kristof805,

You are missing the single quotes around the Title coming from the dynamic content. See the below screenshot for the single quotes. They are really easy to miss. 

[kristof805]

@faustocapellanj progress!

Okay so it got to the condition but it returned false, which I hate, since there is nothing to investigate:

Progress??

Behind the pop-out is "is equal to" and 1

I hate troubleshooting this specific part since it doesn't show the code that's execute for it to return false.

Quick update: Also I can click Edit in advanced mode and the code is still the same as you provided:

@equals(length(body('Get_items')?['value']), 1)

[faustocapellanj]

Hi @kristof805,

At least the flow is not failing. Question: are you naming the item in the Daily List the same as the item in ListA?

[kristof805]

The Daily List has items which are identical to the names of the SharePoint lists that get filled out each day, yes. I don't really name the item, just the list. Each new item created on ListA now does have the ID, for today for ListA, Pressure-20171020.