Hi,
I have a "calender" for bookings and I need to continually increase the rows during the year, right now it's done manually. Which takes a lot of time.
Below picture shows how it looks and I'm wondering if I could make a script or do something to increase the rows for the calender.
It's the same time slots everyday and only on weekdays.
Thanks
@Fraita I haven't looked at the video but the title of it doesn't lead me to think it has any relevance for your scenario. For this I've just used the dates & times that were in your image and then created the equivalent items for the following week. So the initial SharePoint list looks like this - make sure your weeknumber column is a number column.
Then your flow will be as follows:
1. The trigger is a recurrence schedule set to run once a week
2. Then initialize 2 float variables, one for the current week number and one for the next week number:
3. Next, we want to get what the current week number is, so add a SharePoint "get items" action and in the Order By field type WeekNumber desc and set the Top Count field to 1:
4. Next, even thoiugh we are only bringing back 1 item you neverthelss need add an apply to each and select value from the dynamic content box (if you don't do it Powwer Automate will add it for you). Add a compose action and for the Inputs field select WeekNumber from the dynamic content box.
Next add 2 Set variable actions. For the first one set the value to be the outputs of the previous compose which you select from the dynamic content box. For the second one click in the Value field and select the Expression tab in the dynamic content box enter add(variables('varCurrentWeekNumber'),1) which adds 1 to the current week variable and stores it in the next week number variable.
5. Next, we want to bring back from the list all those items where the week number equals the current week number variable. To do this add another get items action, open the advanced options and in the Filter Query field type WeekNumber eq '' and inside the single quotes select the current week number variable from the dynamic content box.
6. Add another apply to each control and make sure to selecdt the correct value from the get items 2 section of the dynamic content box.
Next, add a compose for the current items date column, and select Date
Next, add another compose and for this we'll add 7 days to the date in the compose above using the expression addDays(outputs('ComposeCurrentDate'),7)
7. Finally, add a SharePoint create item action. For the weeknumber field select the next week number variable. The weekday, PVV and UNI3 columns aren't changing so just select the appropriate one for each field from the dynamic content box. And for the date field select the outputs of the compose new date you added in the previous step.
When you run the flow it it will create a new equivalent item for next week for each of the items in the current week. This runs every 7 days but you could do it further ahead just by changing the comose new date expression.
Hope that helps.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Hello @Fraita,
In that case i would simply create a list with
- a "CalendarWeek" column and
- a Choice Column with all the possible events in that week (like UNI Wednesday 14:15) and
- rename the "Title" column to "Name"
Then take the URL of your list (i.e. https://mytenant.sharepoint.com/sites/mysite/TaxiBooking) and append "/NewForm.aspx" like this
https://mytenant.sharepoint.com/sites/mysite/TaxiBooking/NewForm.aspx
and give this form to your users
Then you don't need to add more rows manually...
Every taxi-booking request will create a new row.
2 hours before the event you just filter the list by weeknumber and event to get the names of the persons for the taxi.
This could make your setup much more simple.
Best Regards,
Sven
Accepted Solutions
@Fraita I haven't looked at the video but the title of it doesn't lead me to think it has any relevance for your scenario. For this I've just used the dates & times that were in your image and then created the equivalent items for the following week. So the initial SharePoint list looks like this - make sure your weeknumber column is a number column.
Then your flow will be as follows:
1. The trigger is a recurrence schedule set to run once a week
2. Then initialize 2 float variables, one for the current week number and one for the next week number:
3. Next, we want to get what the current week number is, so add a SharePoint "get items" action and in the Order By field type WeekNumber desc and set the Top Count field to 1:
4. Next, even thoiugh we are only bringing back 1 item you neverthelss need add an apply to each and select value from the dynamic content box (if you don't do it Powwer Automate will add it for you). Add a compose action and for the Inputs field select WeekNumber from the dynamic content box.
Next add 2 Set variable actions. For the first one set the value to be the outputs of the previous compose which you select from the dynamic content box. For the second one click in the Value field and select the Expression tab in the dynamic content box enter add(variables('varCurrentWeekNumber'),1) which adds 1 to the current week variable and stores it in the next week number variable.
5. Next, we want to bring back from the list all those items where the week number equals the current week number variable. To do this add another get items action, open the advanced options and in the Filter Query field type WeekNumber eq '' and inside the single quotes select the current week number variable from the dynamic content box.
6. Add another apply to each control and make sure to selecdt the correct value from the get items 2 section of the dynamic content box.
Next, add a compose for the current items date column, and select Date
Next, add another compose and for this we'll add 7 days to the date in the compose above using the expression addDays(outputs('ComposeCurrentDate'),7)
7. Finally, add a SharePoint create item action. For the weeknumber field select the next week number variable. The weekday, PVV and UNI3 columns aren't changing so just select the appropriate one for each field from the dynamic content box. And for the date field select the outputs of the compose new date you added in the previous step.
When you run the flow it it will create a new equivalent item for next week for each of the items in the current week. This runs every 7 days but you could do it further ahead just by changing the comose new date expression.
Hope that helps.
Rob
Los Gallardos
Microsoft Power Automate Community Super User