When to Use This Feature?

When creating a duty schedule, you may need to assign staff members in a repeating rotation.

How Does It Work?

This solution uses formulas to generate continuous row numbers and convert them into a repeating sequence. Data Linkage then retrieves the corresponding staff member based on the generated number.

What Does It Look Like?

For example, if 4 staff members take turns on duty and each person works for 2 consecutive days, the numbering pattern should repeat as:

1, 1, 2, 2, 3, 3, 4, 4, 1, 1, 2, 2...

Each number corresponds to a staff member, allowing the duty schedule to be generated automatically.

How to Set It Up?

Step 1 Numbering the Duty Staff

Create a form named Duty Staff and assign a number to each staff member. These numbers will be used to match the staff members through Data Linkage.

Staff No.	Staff Name
1	Staff A
2	Staff B
3	Staff C
4	Staff D

Step 2 Creating the Form

Create a form named Duty Schedule with a subform named Details and add the following fields:

Field Name	Field Type
Serial No.	Number(Subform)
Random Code	Single Line(Subform)
Group No.	Number(Subform)
Rotation No.	Number(Subform)
Duty Staff	Single Line(Subform)
Helper 1	Single Line
Helper 2	Single Line
Current Last No.	Number
Previous Last No.	Number

Step 3 Configuring the Fields

Select the corresponding field and go to Field Properties > Initial Value > Formula > Edit. Configure the following fields in the same order as the original example.

Field	Formula	Location
Serial No.	(SEARCH(Details.Random Code,Helper 2)-1)/36+1+Previous Last No.
Random Code	UUID()
Group No.	IF(MOD(Details.Serial No.,2)==0,INT(Details.Serial No./2),INT(Details.Serial No./2)+1)
Rotation No.	IFS(MOD(Details.Group No.,4)==0,4,MOD(Details.Group No.,4)==1,1,MOD(Details.Group No.,4)==2,2,MOD(Details.Group No.,4)==3,3)
Duty Staff	Set this field using Data Linkage. Select Duty Staff and go to Field Properties > Initial Value > Data Linkage. Select Duty Staff as the linked form. Set Staff No. in the linked form to equal Details.Rotation No.. Display the corresponding Staff Name in the current Duty Staff field.
Helper 1	Set the custom default value to `1`.
Helper 2	CONCATENATE(Details.Random Code)
Current Last No.	Details.Serial No.[COUNT(Details.Serial No.)-1]
Previous Last No.	MAPX("last",Helper 1,Helper 1,Current Last No.)

Note: In the MAPX formula, select the first Helper 1 from the current form. Select the second Helper 1 and Current Last No. from the historical records of the same form under All Form Fields.

Step 4 Hiding Auxiliary Fields

To keep the form clean:

Select the auxiliary fields and go to Field Properties > Field Permissions. Uncheck View.
Go to Form Properties > Hidden Field Default Value and select Always Recalculate.

Keep Duty Staff and other required scheduling fields visible.

Notes:

The repeat frequency is 2, meaning each staff member is assigned to 2 consecutive rows.
The rotation includes 4 staff members. Adjust the formulas if the repeat frequency or number of staff members changes.
For the first submission, ensure an empty MAPX result is handled as 0.
Simultaneous submissions may result in duplicate serial numbers.

Cyclic Numbering in a Subform