Automation to modify sharepoint list with dates

(0) Share

Report

Posted on by Calcioscacchi

413

Hello there,

I have a sharepoint list which collect events from PowerApps. For what we need to know, we have:

-a StartDate column called "Inizio", type date/hour

-a Enddate column called "Fine", type date/hour

-a column called "Velocità" that describes the production speed, type number.

-some calculated column:

duration: (1440*(Fine-Inizio))
theoretical meters: Velocità*duration

-.....

and other columns.

My need is to have for each row the same day for "Inizio" and "Fine". Actually, an event can start the 15th January and end the 18th January and this is not good for my analysis.

So, the goal is create an automation which starts at midnight and that makes the job. For this example, suppose ID row = 5, starts at 03/02/2023 21:30:00 (european datetime conv, dd/mm/yyyy)

The steps that I thought:

starts everyday at 12:00AM (or 00:00:00, I prefer this notation) --> DONE;
get data from my list, looking for the most recent event-->DONE
check if the column "Fine" is valued. If yes, simply exit. If not, continue the flow.
update the "Fine" column with "03/02/2023 23:59:59"
create a copy of the row with ID=5 with the same informations, but:
- "Inizio" is valued as 04/02/2022 00:00:00
- "Fine" is empty.

I don't want a complete solution (well, if you like it would be a pleasure :P) but I really need some hints to reach this.

Any ideas to help me?

Thank a lot.

Categories:

General topics

All responses (3)

Answers (0)

Sort by

Hardesh15 7,087 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Calcioscacchi What is the challenge here? Have you designed any flow? if yes pls share screens.

Please 'Thumbs Up' the posts that helped you and 'Accept as Solution' if my post answered your question.

@Hardesh /Gopenly.in
Calcioscacchi 413 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your answer. It's a challenge for me, i'm pretty new in PowerAutomate.

Anyway, this is my actual flow:

to get Data I use this configuration and it works:

Now I'm trying to modify the "Fine" Column" so I put in variables the ID row and the mandatory elements:

and now i'm trying to update the field "FINE" with a datetime vale. Surelly, I need to extract the date (dd/mm/yyy) from "Inizio" column but it was just a test to see if it writes on the correct column.
Calcioscacchi 413 on at

Like
a
(0)

Report
Copy link

Link copied!
Ok, I'm improving my skill with powerautomate and right now I perform this:

starts everyday at 12:00AM (or 00:00:00, I prefer this notation) --> DONE;
get data from my list, looking for the most recent event-->DONE
check if the column "Fine" is valued. If yes, simply exit. If not, continue the flow. -->DONE
update the "Fine" column with "03/02/2023 23:59:59"
create a copy of the row with ID=5 with the same informations, but:
"Inizio" is valued as 04/02/2022 00:00:00
"Fine" is empty.

for the point
update the "Fine" column with "03/02/2023 23:59:59"
actually i'm testing the flow putting the utcnow() expression and it works quite fine, but something strange happens:

This is my condition: for each element (but it will be always one element) check if "Fine" is null:

if no, simply give me an html table with the ID of the row and the variables IFYES and IFNO (integer variables that are incremented by +1 everytime I enter in the YES or NO flow.

If Yes, increment the IFYES variable, create an html table, and update the "Fine" column with utcnow().

As you can see here I have a null value in my last row:

but the flow seems going on "no" path, should means that the "Fine" field was not empty. So WHY it is updated?

It works, but something is scarying me.

Anyway, next step is to update the "Fine" field with the same day of "Inizio" and hour = 23:59:59.
Can you give me some hint for this one?
Thanks.