Addrow logic for adding values based on day of week

[Dan_McDermott]

Hi, wondering if anyone can assist with the logic on how to add these same rows I’m doing here but only if the day of the week is Monday, Tuesday, Wednesday, Thursday, and Friday? I have some locations with different shift blocks/schedules/hours of operation during the week versus the weekends.

Any/all help is greatly appreciated and I promise once I see it, I’ll never forget it and will extrapolate it for my other similar needs.

[Christiaan_Huizer]

hi @Dan_McDermott

two comments

1. first in this construction you have a sequence that outputs the end date as row + the date after, is that what you intent?
2. second add a filter that checks on the weekdays by using the weekday numbers in a filter between the sequence and the FormulaMap like below:

I am living nearby Brussels and use the IsoWeekday number logic, my week starts on Monday.

hope it helps, Christiaan

[Dan_McDermott]

Hi Christiaan, thank you for responding. Looks like perhaps you are outputting a weekdayname?

Let me clarify what I’m trying to do. I’m trying to auto build (with a button) the employee scheduling template for 5 locations for one month. Each location has different shift blocks (start and end time) that vary if during the week or on the weekend. I figured out how to add the rows with fixed values like position, date, location, etc but getting stuck on trying to add rows values based on if the Date field 2/12/2022 is a weekday or weekend for certain locations. Hope this helps.

[Christiaan_Huizer]

hi @Dan_McDermott

the numbers you see reference the days of the week in the IsoWeekday Logic: Monday is day one, meaning that the numbers 1 - 5 reference the weekdays in the filter.

If you want to add different starting hours for the weekends, you can do it as below. I generate a list of dates in the sequence part. I used WithName() but in this you can do without and just use CurrentValue

Is this something that helps you further?

Best, Christiaan

[Dan_McDermott]

Thank you for the response. I can’t seem to get it working though. Do I need to add a column to calculate what the weekday or weekdayname is then reference that column? I’m also wondering if your “thedates” column was populated by the formula or do I need to have the dates in a table first, which seems to defeat the purpose. I’m hoping to have a blank table then I click the button and all the necessary shifts are added for all my locations. After I get this working, will need to layer into the formula to look at my fixed shifts table so it can add the fixed shifts then any open shifts based on what is left.

[Christiaan_Huizer]

Dear @Dan_McDermott ,

You asked for AddRow() and that only goes via a button and you get what you wanted:

Sequence(concatenate("01/01/2024").ToDate(),Concatenate("02/13/2024").ToDate()).FormulaMap(CurrentValue.WithName(TheDates, 
  
[Target Table].AddRow(
[Target Table].thedates,TheDates,
[Target Table].theweekdayname,TheDates.WeekdayName(),
[Target Table].Starttime,If(TheDates.Weekday().Contains(1,2,3,4,5),"08:00","10:00")
  
  )))

This is the empty target table.
If you create a table with the same names, you copy the code in the button it should work. after you change the names etc.

the date ranges is up to you, in my example it is about 40 days.

You can also write the function without WithName() and thus shorten it a bit.

Sequence(concatenate("01/01/2024").ToDate(),Concatenate("02/13/2024").ToDate()).FormulaMap(
  
  [Target Table].AddRow([Target Table].thedates,CurrentValue,
    [Target Table].theweekdayname,CurrentValue.WeekdayName(),
[Target Table].Starttime,If(currentValue.Weekday().Contains(1,2,3,4,5),"08:00","10:00")
  
  ))
````Preformatted text`
    
Hope it helps, if so please mark this contribution as the solution so others can pick it up for their specific case. Cheers, Christiaan

[Dan_McDermott]

I renamed my columns and table so I could just use exactly what you first responded with. It populated everything except the dates. Then I tried to modify the hardcoded 2024 dates you used with my document’s Start and End Dates but still says it cannot format the date value. I tried modifying thedates column to Date, Date and Time, even Text

etc but same issue. Pasting the formula I used below. I also had it just like you did previously with “01/01/2024” and “02/13/2024” at the beginning but same result i.e. red corner in all values for column “thedates” and says “cannot convert value to specified format”

Sequence(
Concatenate([Start Date]).ToDate(),
Concatenate([End Date]).ToDate()
)
.FormulaMap(
CurrentValue
.WithName(
thedates,
Me
.AddRow(
Me.thedates,
Me.thedates,
Me.theweekdayname,
thedates.WeekdayName(),
Me.Startime,
If(
thedates.Weekday().Contains(1, 2, 3, 4, 5),
“8:00am”,
“3:30pm”
)
)
)
)

[Dan_McDermott]

Also seems to be adding every day of the week. Probably because it isn’t pulling the dates correctly but wanted to mention as I forgot to in my last reply.

[Christiaan_Huizer]

maybe you can share a dummy doc in which you copy this code and from there we see what we can do?

[Dan_McDermott]

Of course the doc is not a dummy doc but I am only one using it atm as it isn’t anywhere near ready for operational use

Please only use the Me table at the top and the button I named Christiaan’s button. I will end up needing to add open shifts (no person assigned) and the rest all open shifts based on our needs.

Really appreciate all the help and I promise I’m capable of learning

[Christiaan_Huizer]

please have a look here : Clinic Scheduling - Employee Time & Attendance
if this code works please make it your own again