You’re offline. This is a read only version of the page.
Announcements
Hi Everyone,
I have a collection that contains a staff ID, appointment reference and date that is generated when a staff member clicks an appointment slot to make appointments for other staff members.
Each time an appointment is created, a new row is created in the collection. I need to be able to identify consecutive date ranges where they exist for each user and then copy the start and end dates of the range to a new collection that has a start date and end date column.
For example, three individual rows in the first collection with the same staff ID and reference dated 03/01/2023, 04/01/2023 and 05/01/2023 need to be in the second collection as one row with the staff ID and a start date of 03/01/2023 and end date of 05/01/2023. Ideally, the data needs to be copied from the first collection to the second when the staff member clicks a button. To complicate matters slightly, the dates in the first collection don't run consecutively.
If it helps, the image below shows how the dates look in the first collection on the left look and how they need to appear in the second collection on the right.
I am not sure if this is possible but if anyone knows how to do this or can point me in the direction of something that might help solve this problem I would be really grateful.
If it helps, here is the code for the two collections in the image:
Share
ReportClearCollect(colAppointmentDetails,
{ID: "ID1", Date: Date(2023,01,01), Reference: "00001"},
{ID: "ID1", Date: Date(2023,01,02), Reference: "00001"},
{ID: "ID2", Date: Date(2023,01,03), Reference: "00001"},
{ID: "ID2", Date: Date(2023,01,04), Reference: "00001"},
{ID: "ID1", Date: Date(2023,01,06), Reference: "00001"},
{ID: "ID2", Date: Date(2023,01,05), Reference: "00001"},
{ID: "ID3", Date: Date(2023,01,07), Reference: "00001"},
{ID: "ID3", Date: Date(2023,01,09), Reference: "00001"},
{ID: "ID1", Date: Date(2023,01,08), Reference: "00001"},
{ID: "ID1", Date: Date(2023,01,10), Reference: "00001"},
{ID: "ID1", Date: Date(2023,01,11), Reference: "00001"},
{ID: "ID3", Date: Date(2023,01,12), Reference: "00001"},
{ID: "ID3", Date: Date(2023,01,13), Reference: "00001"},
{ID: "ID2", Date: Date(2023,01,15), Reference: "00001"},
{ID: "ID3", Date: Date(2023,01,14), Reference: "00001"}
);
ClearCollect(colAppointmentDetailsCombined,
{ID: "ID1", StartDate: Date(2023,01,01), EndDate: Date(2023,01,02), Reference: "00001"},
{ID: "ID2", StartDate: Date(2023,01,03), EndDate: Date(2023,01,05), Reference: "00001"},
{ID: "ID1", StartDate: Date(2023,01,06), EndDate: Date(2023,01,06), Reference: "00001"},
{ID: "ID1", StartDate: Date(2023,01,08), EndDate: Date(2023,01,08), Reference: "00001"},
{ID: "ID1", StartDate: Date(2023,01,10), EndDate: Date(2023,01,11), Reference: "00001"},
{ID: "ID3", StartDate: Date(2023,01,07), EndDate: Date(2023,01,07), Reference: "00001"},
{ID: "ID3", StartDate: Date(2023,01,09), EndDate: Date(2023,01,09), Reference: "00001"},
{ID: "ID2", StartDate: Date(2023,01,15), EndDate: Date(2023,01,15), Reference: "00001"},
{ID: "ID3", StartDate: Date(2023,01,12), EndDate: Date(2023,01,14), Reference: "00001"}
);
Thanks in advance,
Lucy
Just to confirm, you only need to automate the process of creating the second table based on the first?
If this is the case, something like the following code could work:
All responses (
Answers (
2,637
ClearCollect(
colAppointmentDetailsCombined,
ForAll(
Distinct(
colAppointmentDetails,
ID
),
{
ID: Value,
Reference: LookUp(colAppointmentDetails, ID = Value).Reference,
StartDate: First(SortByColumns(Filter(colAppointmentDetails, ID = Value), "Date", SortOrder.Ascending)).Date,
EndDate: Last(SortByColumns(Filter(colAppointmentDetails, ID = Value), "Date", SortOrder.Ascending)).Date
}
)
)
Hope this helps!
