Hey there,
actually I try out the “Microsoft Outlook Calendar” pack.
We’re planning on-call-services in our Coda tool.
You can see in the screenshot an information page (there are a lot of informations more below in that page).
We filter the planned on-call-services via the control “Date range” and the the planned services via the control “Services”
Then we send this whole page via email with the “Microsoft Outlook Email” pack to all the recipients, which are listed in the table “On-Call-Services” - in this case to the users Förster, Matthias and Hagedorn, Martin.
At the same time we want to create calendar events in the users calendar.
As you can see, the user Förster, Matthias is planned for on-call-services from 19.10.2023 - 22.10.2023 and the user Hagedorn, Martin is planned from 16.10.2023 - 22.10.2023
The pack offers a start date and an end date for the event. But in this case they differ.
The table name is “On-Call-Services”. The relevant column names are:
Employee
On-Call-Service
Date
Can anyone help me to build a formula for the “Start” and “End” fields of this pack? The goal is to create only events for the users calendar on dates, where the user is planned for.
What I tried so far was a formula “On-call-Services.date.first()” for the Start and “On-call-Services.date.last()” for the End. That works, but sets the dates in the calendars for all users from 16.10.2023 - 22.10.2023, which is not valid for the user Förster, Matthias in this case.
your approach was correct, you only need to add a filter for the relevant employee, like “Services.Filter(Employee = CurrentValue).Date.First”, assuming you iterate through the Employee-Table to create the event for each Employee individually.
Let me know if you need further explanations, in German would also be possible.
Hey Marius, many thanks for your help. I need indeed further assistance.
Can I contact you in a different way, so we can switch to German, which would be much easier for me.
For all members who are also read in this post: I’ll try to write then a summary in english, if we’ve found a solution.
As you can see, it is possible to choose more than one on-call-service from the control in the upper section. The table provides then a view, which employee is planned on specific dates for a specific on-call-service.
The goal is to create an event in the employee’s outlook calendar, depending on the service he’s planned for and depending on the start/end date he’s planned for.
From my understanding it is now needed to go thru the table “On-Call-Services” to determine, how many and which On-Call-Services are identified, what is the start/end date for each of the services and who is the employee to adress for each service.
From my understanding, this must be done service by service.
In my opinion it’s not necessary (but possible if helpful) to create a helper table. All the data are already there, but I have problems to isolate them to set the correct start and end date for each employee.
A helper table could look like this:
It would also be possible to send out single all-day events for each day/service/employee.
But even if I have a helper table, how can I manage to send out the emails with the Outlook pack row by row?
Okay, sometimes it is better to go the easy way - as long as it comes to my mind.
I’ve included now a hidden button-column in the table On-Call-Services.
This button in each row creates a single all-day event in the employees calendar.
To send out all the create-events I included a button on the page, which is doing a runactions(on-call-services.buttons).
It’s solved for now, I am happy.
