My brain goes to some kind of coarse batching as an initial step. My events are never more than about a week, so maybe I could group them into monthly batches, and number the batches. Then you could start by saying “is thisRow in the same batch or an adjacent batch”? (Something like AbsoluteValue(currentValue.batchNumber - thisRow.batchNumber) > 1).
Hello @Nick_HE!
What I prefer to do in these cases is make a separate table to add or edit events. So every time you add an event to this table it checks if it overlaps with the events of the Events table. If it does, you can’t add it. The same if you want to edit an event, you copy all the values to the Add/Edit events table and you can’t submit the changes if it detects overlapping.
I agree is not as easy but this way your doc stays sharp even with a lot of rows in it.
Edit: I’m sorry If I wrongly assumed that the events don’t have to overlap.
Yeah events are allowed to overlap, it’s just that I need to show certain warnings if they do, and certain other actions down the line are disallowed (e.g. having overlapping events happen in the same room).
But you’re saying still for performance, maybe create the event in a separate table, then submit it to the main table after, so that the 15sec calculation that has to happen at least happens after user input instead of before they can start?
(Cause current problem is the new row popup isn’t even interactive until the 15sec are up)
I see, I think the second table to add still applies.
The problem with the performance is that you have an ‘expensive’ formula in table with 400 rows that is updated 400 times every time you add or remove a row.
What I’m proposing is moving that formula to an ‘Add Event’ table. In this table you can show all kinds of warnings of overlaping. If it overlaps with other events in the same room you can even disable the button to add the event to the main table.
In the end, instead of having an expensive formula calculating in 400 rows you will end up with an expensive formula calculating only in the events you are adding
Ok thanks. I need the overlap data to stay current on the main event table (it’s not just a warning for the moment you’re adding) so somehow I still need that formula active on the event table.
But yes, if I can get the Add Event table working, it may at least alleviate the interface lag. How do you like to commit the new row (in Add Event table) to the Events table? a submit button?
Gotcha! Out of curiosity, what use do you have for the overlapping column after adding?
It ties into a whole other inventory management system in the doc to make sure that if we own 3 tripods, we don’t try to book 5 tripods on events that overlap with each other.
Got down below 1sec by ignoring everything older than a month. It’s not very important to me to see any conflicts in the past really, so I gave it a month of buffer just to be safe.
I added a column for staleness (end date-today() > 30)
Then relied on staleness twice in my formula:
If thisRow.isNotStale, proceed with the detailed calculation where we filter each row and check for conflicts…
For each row in the filter, check for currentValue.isNotStale before doing all the date overlap comparisons
The result is events older than a month show no conflicts, and multi-day events around the 1-month boundary are unreliable, but that’s fine!