Please help, i just would like to send a item via flow from one sharepoint list to another when its due. Simple right?

[Anonymous]

Hi Guys,

im starting to panic,

What im trying to do is achieve is to create a flow that looks at one sharepoint list and looks at a datecolumn [Dateof_LASTservice1] and creates a new item to another list when its due. Simple right?..

So im handy with excel so i try to use calculated columns to calculate a year and ill make a powerquery on a get items or a condition of apply all on the new calculated date [DateofNEWservicedue1], nope you cannot use calculated date columns with flow and powerqueries and the condition didnt want to work either.

So back to the drawing board, so now i figure ill make use the formatdatetime(adddays etc powerquery via flow to send a date of 11months to the same list (then its not calculated 🙂 ) and ill set it too when new item is created or modified :thumbsup and ill use a powerquery of [DateofNEWservicedue1] eq 'utcnow()' etc

Sweet now i will just add another 3 flows for the other types of due dates, nope it creates a loop and they constantly run as all 4 flows check for modified or created so now i turn them off and start to do the panic ie (FARK ME, ive got to give them something by Monday or its my arse) So i put them all on different schedules and i think im ok, except the dates just keep adding 11month... ffs

Ok so i stay up late for the 3rd day and figure ill go back to my first problem but still use the the formatdatetime(adddays and add 12months to the date and use a condition to check for a range and the create an item, now heres where i currently am. (if anyone wants to show me another way or how to fix the two ideas above please do)

But as far as i can tell (if your still reading thankyou, much internet love), my flow has two issues

Firstly the new list item that is created from the conditions (of less than or equals to 30days and a greater than or equal to 1), has nothing i can reference and just creates a new item every days as (i want it to create a new item if their is not the same item already there, (then update or do nothing)

Secondly, i tried to just use the a formatdatetime and get the output so i dont even need a column with the date in it.

Could someone far smarter than me, just show me a simple step by step guide on how to create a flow that looks at a date column [Dateof_LASTservice1] in a sharepoint list called Asset Register and if its a a month before the end of that new due date, then create an item in another list called Work Order and if there is already an entry there to do nothing or update list item

I should say that i have four dates column in the same list Asset Register that need to be looked at daily and the list only has 50 items in it.

Thankyou to whomever helps.

[ScottShearer]

@Anonymous 

Can you tell me what should trigger your Flow?  Should it run only when the Asset Register entry is updated?  Should it run on a schdeule (daily?) looking and loop through all items in the Asset register list?

[Anonymous]

---

@ScottShearer wrote:

@Anonymous 

Can you tell me what should trigger your Flow?  Should it run only when the Asset Register entry is updated?  Should it run on a schdeule (daily?) looking and loop through all items in the Asset register list?

---

Hi Scott,

I guess id perfer a Schedule flow, that runs each morning id like it to check each of the four date of service columns and create a new item with a Asset ID (its the machinery number) and Whichever date column is expirered and if the sameflow runs the next day then not add a new item?

Does that make sense?

Cheers

Essentially what im doing is once the date is flagged and a new item is created, someone else will look at that work order list via powerapps and then they will mark a choice column as complete and then an email will be sent externally to say this task is done and here is the list item marked as complete and who performed it, and we can give them access to just that list (i can do this part of it, i thought i could do this part)

---

[ScottShearer · Accepted solution]

@Anonymous 

Below is a Flow that should get you started.

I use a column called AssetRegisterID to link the work order items to the Asset register.

You can extend my solution by adding "or" statements to the Get Items action for the Asset Register list.

Here is a link o a post that better explains OData filters (used in the filter query).

[Anonymous]

---

@ScottShearer wrote:

@Anonymous 

Below is a Flow that should get you started.

I use a column called AssetRegisterID to link the work order items to the Asset register.

You can extend my solution by adding "or" statements to the Get Items action for the Asset Register list.

Here is a link o a post that better explains OData filters (used in the filter query).

---

Thankyou so much for this, I wish i could better convey the emotion of grattitude, i have for you for this through text.

I should send you some beers, ill put it in and mark as solved when it works 🙂

[Anonymous]

---

@ScottShearer wrote:

@Anonymous 

Below is a Flow that should get you started.

I use a column called AssetRegisterID to link the work order items to the Asset register.

You can extend my solution by adding "or" statements to the Get Items action for the Asset Register list.

Here is a link o a post that better explains OData filters (used in the filter query).

---

Hi Scott,

I dont think the odata get date or add date is working, the value of the body is []

Ive also check to make sure there is one date that is due (i built a few test assets)

[ScottShearer]

@Anonymous 

Can you please post a screen shot of your Flow - at least the part where you have the OData filter.

[Anonymous]

---

@ScottShearer wrote:

@Anonymous 

Can you please post a screen shot of your Flow - at least the part where you have the OData filter.

---

Certainly,

Also the code here is copied and pasted,

Compliance_x0020_Date eq '@{addDays(utcNow(),-30,'yyyy-MM-dd')}'

afaik it should be working as the '' are in the right spot. I feel cursed haha

Edit: Removed identifying info from the image

[Anonymous]

---

@ScottShearer wrote:

@Anonymous 

Can you please post a screen shot of your Flow - at least the part where you have the OData filter.

---

Also worth nothing that im in Australia and the Site is set up to AEST +10 (Brisbane). Could it be the date format?

[ScottShearer]

@Anonymous 

While the timezone may impact the results, it will not cause an error.  The timezone has no impact on the date format.  Also, SharePoint stores everything as UTC time.

Can you verify that the terminate action is set to success?  If not, set it to success and test again.

When you test again, experiment with entries that are 29 and 31 days forward and see if you get any results.  Once you get results, I'll explain how to control for the timezone.

[Anonymous]

Hi Scott, 

---

@ScottShearer wrote:

@Anonymous 

While the timezone may impact the results, it will not cause an error.  The timezone has no impact on the date format.  Also, SharePoint stores everything as UTC time.

Can you verify that the terminate action is set to success?  If not, set it to success and test again.

When you test again, experiment with entries that are 29 and 31 days forward and see if you get any results.  Once you get results, I'll explain how to control for the timezone.

---

I was able to get it too work however its looking at completed work orders (from the previous system) so what im going to do is delete all the old ones and create a another list called completed and after each task is marked as complete a flow sends it elsewhere,

However what happens if i have say one date column and another both during the same period? i guess ill have to make a yes/no column call it DATECHECK1 and get the odata query in the 2nd Get items to be DATECHECK1 eq 'Yes' and Asset eq 'Asset'

A million thanks i slept well last night,

what i will do is finish it and the when its perfect today, ill mark is as solved for your last response and i will put up a screenshot for anyone looking to do the same thing in the future.  

[Anonymous]

Thank you i was able to get it working, 

there was a couple of things i needed to change one was the 2nd condition, i had to set it too 

length(body('Get_Items')['Value']) is greater than or equal to one and change the if No to Terminate (Thankyou before your flow i had never heard of that function)

and i also put the apply to each for the if yes to Get_items 1 (not the 2nd get items)

Because it only runs once a day, it will not get doubles so it has no need to update (it wasnt working when it was  set it too 0 and equal too, i think it had something to do with the apply all above the 2nd condition i was only able to set it to the yes and no, it was an ID match problem)

---

@ScottShearer wrote:

@Anonymous 

While the timezone may impact the results, it will not cause an error.  The timezone has no impact on the date format.  Also, SharePoint stores everything as UTC time.

Can you verify that the terminate action is set to success?  If not, set it to success and test again.

When you test again, experiment with entries that are 29 and 31 days forward and see if you get any results.  Once you get results, I'll explain how to control for the timezone.

---

But once again, i really wish to personally thank you. This has saved my job. If your ever in Australia send me a message.