How to get a list of dates between two dates and insert it into backend table

AppSheet AppSheet Q&A
1

I need to create an app in Appsheet have following functionality:

  1. Bulk Update of the respective KPI for each employee categories (permenant, casual) across all availble team Monthly or quarterly.
  2. when the KPI value is set for a range (month or quarter, or smaller ranges), the user should have the ability to change (override) the KPI values for specifc day(s); For example assume that the range a certain KPI for a certain group of employees have been set for period (1/05/2023 - 30/05/2023) to 100. Now, at a later time, the user wants to update (overrider) the value of the same KPI for the same (or a subset of) group of employees for dates 5/05/2023 and 7/07/2023 and etc (or alternatively a smaller range e.g. 8/05/2023 - 12/05/2023) to 200, while the KPI value for other days of the original range remain intact.

The simplest design for my database table to be able to efficintly avoid overlaps between periods is to have each row representing a date (not a period). However, the user will need to enter ranges (start date to end date) for bulk update of KPIs. (ask them to enter 30 different dates to capture a month would not be an acceptable user experience). From my research, doesn’t look like I can receive a date range from the user on the form and somehow translate it into multiple rows in the table. Also, looks like a feature to create a list of dates based on a given range (as a Formula) is not available, that would have made this possible. Does anyone know a possible approach to achieve this?

In addition, if I wanted to capture rows for periods of times, then in requirement 2 example above, I will need to check the ranges for overlaps and subsequently split them accordingly which sounds like a very complex things to fulfil with appsheet.

I do appreciate your comments that can help me with this.

regards
Nicole

2

So what I do with is to create a new table in appsheet where the user can select multiple variables. Then once they are all selected present them with 1 or more action buttons to do what you want. Normally I use this setup with my appsheet clients to generate complex PDF reports with lots of variables. Buy you could so the same thing with a bot or the API to add/change/delete data.

Yeah overlaps are a PITA. There’s no other way that to write a check for each of the 9 types of overlap. Done this a few times…

Simon, 1minManager.com