I am working on creating an app for maintenance plans with scheduled tasks. Each maintenance plan has a start date and a duration. For example, a plan may start in 2024 and last for 20 years. Within the plan, there are recurring maintenance tasks that have their own start year and interval. For instance, a task like “Inspect AC” might start in 2025 and repeat every 2 years.
The issue I am facing is calculating the specific years when each task should be scheduled using Glide tables. For example, the first occurrence of the task should be in year 1 (2025), and then every 2 years after that for a total of 10 years. This means the task would be scheduled in years 1, 3, 5, 7, and 9.
I have created the following tables:
Maintenance Plans
Maintenance Tasks
Maintenance Schedules
The challenge I have is creating the years the tasks are scheduled dynamically. I imagine this is usually done by creating loops which don’t exist in Glide tables.
The calculation would normally be: Scheduled Year = Start Year + (Interval × n)
where n is a sequence of integers (0, 1, 2, …) representing the scheduled occurrences within the plan’s duration.
Could you help me @Darren_Murphy?
hey rubin was just making a solution before you updated the post anyways here’s how to get it
here’s the code
function calculateInspectionYears(p1, p2, p3) {
// Split p1 into start year and duration
const [startYear, duration] = p1.split(",").map(Number);
const firstInterval = Number(p2); // Convert p2 to number (first interval)
const interval = Number(p3); // Convert p3 to number (regular interval)
const endYear = startYear + duration; // Calculate the end year
const inspectionYears = []; // Array to store inspection years
// Calculate the first inspection year
let firstInspectionYear = startYear + firstInterval;
// Add the first inspection year to the array if it's within the duration
if (firstInspectionYear <= endYear) {
inspectionYears.push(firstInspectionYear);
}
// Calculate subsequent inspections
let currentYear = firstInspectionYear + interval; // Start from the year after the first inspection
// While the current year is within the duration, add it to the inspection years
while (currentYear <= endYear) {
inspectionYears.push(currentYear);
currentYear += interval; // Increment by the regular interval
}
return inspectionYears.join(", "); // Return a string of years separated by commas
}
return calculateInspectionYears(p1, p2, p3);
there was some workaround used but the video explains it hope it helps
also I think regular columns would work let’s watch
Hi @micheal,
Many thanks for the solution. I tried it and it works great.
My challenge now is to show instances of the tasks for the calculated years in a maintenance plan. I have tried to show what i mean in the image below.
I have created 3 tables in Glide and connected them to each other. A plan can have many tasks and a task can have many schedules:
Maintenance Plans
Maintenance Tasks (based on your example)
Task Schedules
I hope I have the right approach.
I greatly appreciate your help.
My general requirements are the following but I hope to solve them later:
The start year of the plan should update automatically at the start of every new year.
The end year of the plan should also update at the start of every new year, based on the current year plus the plan duration.
A start year of the plan in the future should take precedence over the current year when calculating the end year based on plan duration.
Task occurrences should automatically be added to the plan based on changes to the plan’s and task’s start year.
Yearly task cost increases should be calculated for the year of the task occurrence based on a yearly increas in cost and a changeable percentage.
Total costs per task per year and plan duration should be able to be calculated.
Total maintenance cost per year and plan duration should be able to be calculated.
Readjusting start years, occurrence, duration should dynamically update the plan, costs and years.
Tasks should be able to be added and removed from the plan and update the plan dynamically.
