Hello and good afternoon, I have already read the other topics on “Comparing time periods”, but the previous solutions do not fit my challenge, so I am writing this new topic.
I have three tables. The first table contains several POI (fairs), the second table contains attractions.
The third table contains the tour plan of all fairground attractions. This means, when is an attraction on which POI.
My POIs therefore have assigned attractions. The funfair is time-limited and lasts for 2 weeks, for example. After that, the attractions go to other fairs.
Now I would like to enter the route of the attractions. An attraction is located at different POIs throughout the year.
Example: The attraction is already planned from 1.4.-31.4. and from 1.6.-30.6. Now the period 01.05.-31.05. or from 1.7. should be possible when adding it. A start date between 1.5 and 31.5 and an end date between 01.06 and 30.6 must be prohibited.
My table contains a separate field for “Period from” and a separate field for “Period to”. The attraction is clearly identifiable in the table.
How do I prevent the date entries from accidentally overlapping?
I would use JavaScript column to validate if new dates are not overlapping with the current dates.
create userpecific columns to enter the new Period from and Period to
since JS column only accept 3 values, i would concatenate the current period and the new perion in two different template columns, current period will become p1, and new period will become p2.
with help of claude, create the JS code to compare the new perion (p2) with the current one (p1) and return true if the overlap or false if the don’t
Use the result of the JS column to prevent the user to save the new period
I have a solution, but I can’t show it until I get back in front of a computer. The hardest part of scheduling and Glide is that you can’t just use a date picker because you can’t filter out dates that are already chosen. For that reason, you’ll have to come up with some other way to select a date (a helper table of dates that are in the next year, or the AI component, or something else).
Essentially, you’ll need to create a “custom form” That allows a user to select a start date that doesn’t fall within an already scheduled date. From that date, you need to compare all scheduled dates that are after this date and grab the earliest (Rollup column) As that will be the max end date for that timeframe.
I have found a partial solution. With the following Java Script I can check two time periods against each other: // p1 = time period exist (e.g. “2023-01-01 | 2023-10-31”) // p2 = new period (e.g. “2023-10-01 | 2023-11-20”)
let [p1_start, p1_end] = p1.split(" | ").map(date => new Date(date)); let [p2_start, p2_end] = p2.split(" | ").map(date => new Date(date));
let conflict = (p1_start <= p2_end) && (p2_start <= p1_end);
return conflict ? “ conflict!” : “ no conflict”;
Now the challenge is to check the newly entered time period against n time periods. To do this, I have created a relation so that I can look at all rows of an attraction AND with a lookup column I have all time periods together (e.g. 2023-1-1 | 2023-12-31,2024-1-1 | 2024-12-31,2025-1-1 | 2025-12-31).
But what happens now? I can neither specify the lookup column for the Java script, nor an array column (into which I could transfer the lookup column).
Please help me again. Thank you very much.
I have found the final solution - even if it is not entirely intuitive from my point of view. As already written, I cannot use the lookup column for a Java script. However, I was able to put a joined list on the lookup column - and the joined list can be selected for this.
Finally, the Java Script and everything works:
// p2 = User-entry: “2023-01-15 | 2023-01-20” (example)
let userPeriod = p2.split(" | ").map(date => new Date(date));
// Extract all time periods from the lookup column
let periods = p1.split(“,”); // Divided by commas, as Lookup provides the values as a list
// Check whether the user period overlaps with one of the existing periods
let overlap = false;
for (let period of periods) {
let [start, end] = period.split(" | ").map(date => new Date(date));
// Check whether the user period overlaps with this period
if ((userPeriod[0] <= end) && (userPeriod[1] >= start)) {
overlap = true;
break;
}
}
// Return the result
return overlap ? “ Overlap!” : “ no conflict”;
So to compare 4 date fields (2+2) with each other, I currently need 26 columns…
conflict!” : “
no conflict”;