Help needed with formula

[Foerster_Matthias]

We have the following tables:

• MasterServiceCalendar
• Employees
  Every Employee needs to have its own calendar due to internal rules.
  We have 125 employees. Yes, it results in a big table “MasterServiceCalendar”, but it works now for two years.
  To create new calendars for the next year, I have created a select-control (S_Employee) from the employees table and a date control (S_Date) to select a fixed date range.
  What I want to achieve is via a button:
  Depending on which employees are selected in the list control and depending on the date range (first/last) I want to add rows in the table MasterServiceCalendar for each employee in the selected date range. Maybe I need to split it in two charges because of the data amount.
  By example:
  Employee1 → add 365 rows in MasterServiceCalendar → Employee2 → add 365 rows in MasterServiceCalendar
  It is no problem for me to do that for a single selected employee, but if I have multiple employees selected, I run in problems. The Employee list needs to be finished step by step.

Has anyone of you an idea, how a formula for this could look like?

Thanks for your help.
Matthias

[joost_mineur]

Hello @Foerster_Matthias ,
What you want to do is create a formula that looks something like this:
S_Employee.foreach(sequence(start_date_range,end_date_range).foreach(addrow(MasterServiceCalendar,column 1, value 1, column 2, value 2,…)))
If you have a hard time doing this, make and share a dummy doc and we can show you how to do this.
In order to prevent duplicates, you could use AddOrModifyRow() instead of AddRow

[Foerster_Matthias]

Thank you @joost_mineur for your help.
I had something similar in mind, but I failed in my version at the same point.
As soon, as I select multiple names from the employee list, it creates entries in the MasterServiceCalendar with one date row, but combined employee names.
I tried to recreate my structure here:

Can you please have again a look on it and try to find my mistake?
What I try to achieve is, that every employee gets his own range of date entries.

Thanks
Matthias

[Daragh_O_Shea1]

Hi @Foerster_Matthias , I have edited the doc and added a green button which I believe does what you need.

The formula you had was close but slightly incorrect, referencing the control value, and not the row of the table for the employee name.

Here’s the button formula I wrote that works:

S_Employees.foreach(
  CurrentValue.WithName([current employee], 
    
    sequence(S_Date.First(),S_Date.Last()).foreach(
      MasterServiceCalendar.addrow(
        MasterServiceCalendar.Employee, [current employee],
        MasterServiceCalendar.Date,CurrentValue
      )
    )
))

[Foerster_Matthias]

Dear @Daragh_O_Shea1 , that was exactly what I needed. It works perfectly. Many many thanks for your time and effort. I’ll analyze the formula difference and dive deeper in this “WithName” which I’ve nerer used till now.

Again, many thanks for your help.

Best
Matthias

[joost_mineur]

Hello @Foerster_Matthias ,

Just for completeness sake, I added two more buttons and changed AddRows() to AddOrModifyRows(). Reason being: if you (or another user) hits the button again or tries to create an identical range or an overlapping range of calendar items, you get an enormous amount of duplicates. So I created the new buttons to prevent this. The buttons look similar, but they are not: button one finds existing event rows matching the selection criteria and overwrites them, the 2nd button finds the same existing rows but leaves them alone.
Both approaches serve there own purpose, as you will see when you start playing with your test doc.

[Daragh_O_Shea1]

@Foerster_Matthias you’re very welcome.

The withName method is quite useful when there are more than 1 loop like the for each, as the currentValue can become ambiguous.