In this tutorial, you will learn how to insert a variable number of rows into a section on the example of a simple shifts planner app, which will load the appropriate rows with the number of days for a given month.
1. List
Create a list called DAYS in which add one numeric column called DAY. In this column, type the numbers from 1 to 31 consecutively. From this list, the app will take the number of days for the corresponding month.
2. Calendar with shifts
2.1
Create a new app and add a table called Calendar in it. Give the columns the following headers one by one: Day, Shift, Month and Year.
Change column types:
- Day and Month - types: Number
- Shift - type: Choice Button
Set the Shift column to be editable, and set the visibility to false for the Month and Year columns.
Bind the table with the DAYS list, and bind the Day column with the column from the list as above.
The appropriate number of days for a given month will appear in this table, and in the Shift column the user will be able to select the days on which he is on duty.
2.2
Create a spreadsheet named 'Month' with two cells.
In the first cell, set:
- Type: Number
- Cell editability
- Enter the formula =month(today()), which will return the current month's number from today's date.
In the second cell, enter the formula:
- Type: Number
- Enter the formula =if(MONTH(A1)=4||MONTH(A1)=6||MONTH(A1)=9||MONTH(A1)=11,30,MONTH(A1)=2&&number(B2)%4=0,29,MONTH(A1)=2&&number(B2)%4!=0,28,31), which will return the number of days in the month entered in the cell above.
The Data sheet will be created in the next step, and cell Date.A2 will contain the year chosen by the user.
Set the visibility of this section to false.
2.3
Create not visible "Months" spreadsheet and in its first column list all month names.
Create a new spreadsheet called Date with two cells. In the first cell type the formula: =nth(Months.A1:A12, MONTH(MONTH.A1)), which will return the name of the month from the number in the first cell of the Month sheet.
Make the second cell editable and change its type to drop-down list. In the drop-down list items, enter an array of several years, as above. Enter the formula: =text(year(today())) into this cell, which will return the year from today's date in text format. Thanks to this, the current year will appear in the cell by default, and the user will be able to change it by selecting the year from the drop-down list.
2.4
Create two buttons and place them on both sides of the Data spreadsheet, and in their headers write arrows in the appropriate direction as below.
On the buttons, add the Set cell value action. Set Month.a1 as the cell address for both buttons. In the value for the left arrow button, enter the formula:
=if(MONTH.A1=1,12,MONTH.A1-1), and for the arrow pointing to the right - the formula: =if(MONTH.A1=12,1,MONTH.A1+1).
This allows the user to change the month using the arrows.
2.5
Return back to the Calendar table. In the filter, add a condition to the lookup formula: DAY<=MONTH.A2.
As a result, the table will display the numbers of days that will be less than or equal to the number of days in a given month.
2.6
In the Month and Year columns, enter the following formulas: =MONTH.A1 and =DATE.A2. The appropriate months and years will appear in these cells, which will be needed during save of the data later.
3. Saving shifts
3.1
Open the designer and create a new table and call it SHIFT_REGISTER. Name the table columns as follows: Day, Shift, Month, and Year.
Set the column types as follows:
-Day and Month column - Number
-Shift column - Boolean
Select the key for the columns: Day, Month and Year, so there will be only one row for one date. Save the table.
3.2
Go back to the app designer and the Calendar table. Fill in the data field in the appropriate columns by entering the name of the SHIFT_REGISTER table and the name of the appropriate column after the dot. For example, in the Day column, enter: SHIFT_REGISTER.DAY. This will be needed for the next step.
3.3
Create a workflow action widget and go to the workflow tab. Create an action from start state to itself named Save. Add action step 'Save to database', as a section enter Calendar, and as a list / table SHIFT_REGISTER. The action saves the data from the columns in which the data field is properly completed to the specified table.
In post workflow, set the 'Refresh page' action step.
3.4
Create a new table and name it Shifts. Change the headers as in the Calendar table and bind the table to the SHIFT_REGISTER table, and bind the relevant columns as shown below. Make the Shift column editable. Set the Month and Year columns to invisible.
In the table properties in the filter, add the following condition to the lookup: MONTH=MONTH.A1&&YEAR=DATE.A2, which will make the table display data for the month and year selected by the user.
It will be a table in which the selected days will appear after saving, and in which you can make changes.
3.5
Create a spreadsheet called Auxiliary with one cell. Bind the worksheet to the SHIFT_REGISTER table and this one cell to the DAY column. In the filter, add the same condition to the lookup as in the Shifts table.
Depending on whether the SHIFT_REGISTER table has no rows for the selected month and year, this cell will be empty or not.
3.6
In the visibility of the Calendar and Shifts tables, enter the following formulas: =if(isempty(Auxiliary.A1),true,false) and =if(isempty(Auxiliary.A1),false,true).
Thanks to this, at the beginning you will be able to select days for duty in the Calendar table, and after saving, you will be able to change these duties in the Shifts table.
3.7
Go to the Workflow tab and add the second Save to database action to the action Save, in which enter Shifts as a section.
4. Tests
On the screen, the user can select the year from the list and use the arrows to change the month. The number of rows in the table should change according to the number of days in the month, so for example in August there should be 31 rows. Select a couple of days and mark the duty in this days and save.
The message 'Action performed' should appear. Change marked fields and save again. Corrected rows should appear in the table.
0 Comments