Inserting a variable number of rows into a section

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


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 the Day and Month columns type to a number and Shift column type to a checkbox. Set the Shift column to editable, and change the visibility of the Month and Year columns to false.


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.


Create a spreadsheet called Month with two cells. In the first cell, enter the formula =month(today()), which will return the month number of today's date, and set this cell to editable. In the second cell, enter the formula: =if(MONTH.A1=4||MONTH.A1=6||MONTH.A1=9||MONTH.A1=11,30,MONTH.A1=2&&number(Date.A2)%4=0,29,MONTH.A1=2&&number(Date.A2)%4!=0,28,31), which will return the number of days in the month entered in the above cell. Spreadsheet Date will be created in the next step and in Date.A2 cell will be the year chosen by the user.


Set the visibility of this section to false.


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.


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.


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.


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


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 Day and Month columns to number and the Shift column to Boolean. Select the key for the columns: Day, Month and Year, so there will be only one row for one date. Save the table.



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.



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.



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.


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.


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.


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.


Have more questions? Submit a request


Article is closed for comments.
Powered by Zendesk