Adding consecutive rows when a record is added

AppSheet AppSheet Q&A
1
  1. Table Structure First, you need two basic tables:

Device List Table Holds device information.

Example:

Device ID Device Name
C01 Device 1
C02 Device 2

Periodic Maintenance Table

Holds scheduled maintenance dates for each device.

Example:

Device ID Maintenance Period Maintenance Order Planned Maintenance Date
C01 3 Months 1st Maintenance 01/01/2025
C01 3 Months 2nd Maintenance 01/05/2025
C01 3 Months 3rd Maintenance 01/10/2025
  1. Periodic Maintenance Addition Form

User enters the following information via the Periodic Maintenance Addition Form:

Device ID

Maintenance Period (Monthly, 3 Months, 6 Months, Annually)

Planned First Maintenance Date

how can I do this scenario Or can I continue this scenario in a different direction? thanks in advance for your interest

2

First, your question is vague but I assume you mean to ask how to add the Periodic Maintenance rows automatically based on the Maintenance Period specified.

Secondly, in your example, the Planned Maintenance Date list doesn’t seem to match up with the Maintenance Period. I’ll assume that is just a mistake.

Lastly, you would need to determine how to deal with how far into the future you would automatically insert maintenance rows.

Here is what I would recommend…

Have 2 tables - the Periodic Maintenance table and a Recurring Schedule table. This will allow you to more easily support and manage the recurring schedules as you have outlined but also support one-time appointments that have no recurring schedule.

When a Recurring Schedule entry is made, you can enter the Periodic Maintenance rows using either automation OR an action attached the Form Saved behavior.

To deal with the “when do the entries end?” question, I would have them entered in a r_olling fashion_. Decide how far in the future users need to see the schedule and enter rows only that far in advance. Make sure to include the Recurring Schedule that the maintenance entry is based on. For instance, let’s say users only need to see into the future 1 year. Then when a Recurring Schedule entry is made for maintenance of an item every 3 months, the app would enter the first with the Initial Start Date, say its 1/1/2025, and then enter another every 3 months into the future that is within 1 year from today - the last would be 1/10/2025.

For the rolling part, each time a Scheduled item is completed, a new entry is added based on the Recurring Schedule from the date of the last entry on that schedule. This is where 2 tables helps. The Recurring Schedule will automatically have the “Related Periodic Maintenance” items to find the last schedule entry.

I hope this helps!

1 Like