Skip to main content

Cycling Subform Numbers for Shift Rotation

Learn how to use cyclic numbering in a subform to automatically assign duty staff in a repeating rotation pattern.

When to Use This Feature?

This method is useful when you need to create a repeating duty schedule in a subform.

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

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

You can then use this number to automatically populate the corresponding staff member.

How Does It Work?

This solution works in three steps:

  1. Assign a number to each duty staff member, such as 1, 2, 3, and 4.

  2. Generate continuous row numbers in the subform.

  3. Convert the row numbers into a repeating cycle such as 11223344, then use data linkage to return the matching duty staff member.

What Does It Look Like?

In this example:

  • There are 4 staff members

  • Each staff member is scheduled for 2 consecutive days

  • The cycle repeats automatically in the subform

How to Set It Up?

Step 1 Numbering the Duty Staff

Create a form or data source for duty staff and assign each person a number.

For example:

Staff No.

Duty Staff

1

Staff A

2

Staff B

3

Staff C

4

Staff D

This number will later be used for data linkage.

Step 2 Creating the Subform Fields

Create a subform for the duty schedule and add the following fields:

Field Name

Field Type

Location

Serial No.

Number

Subform

Random ID

Single Line

Subform

Group No.

Number

Subform

Rotation No.

Number

Subform

Duty Staff

Single Select / Linked Field

Subform

Fixed Value

Single Line

Main Form

Random ID List

Single Line

Main Form

Current Last Serial No.

Number

Main Form

Previous Last Serial No.

Number

Main Form

Set the default value of Fixed Value to 1.

Step 3 Configuring the Formulas

Select the corresponding field and go to Field Properties > Initial Value > Formula > Edit.

Configure the following formulas:

Field Name

Formula

Random ID

UUID()

Random ID List

CONCATENATE(Subform.Random ID)

Current Last Serial No.

Subform.Serial No.[COUNT(Subform.Serial No.)-1]

Previous Last Serial No.

MAPX("last",Fixed Value,Fixed Value,Current Last Serial No.)

Serial No.

(SEARCH(Subform.Random ID,Random ID List)-1)/36+1+Previous Last Serial No.

Group No.

IF(MOD(Subform.Serial No.,2)==0,INT(Subform.Serial No./2),INT(Subform.Serial No./2)+1)

Rotation No.

IFS(MOD(Subform.Group No.,4)==0,4,MOD(Subform.Group No.,4)==1,1,MOD(Subform.Group No.,4)==2,2,MOD(Subform.Group No.,4)==3,3)

Step 4 Setting Up Data Linkage

Set Duty Staff as a data linkage field.

Use Rotation No. to match the numbered duty staff records, so the corresponding staff member is filled in automatically.

This creates a repeating pattern such as:

  • 1, 1 → Staff A

  • 2, 2 → Staff B

  • 3, 3 → Staff C

  • 4, 4 → Staff D

After that, the cycle repeats automatically.

Step 5 Hiding Auxiliary Fields

To keep the form easy to use, hide the auxiliary fields and keep only the necessary visible fields.

  1. Select the auxiliary fields and go to Field Properties > Field Permissions.

  2. Uncheck View to hide them.

  3. Go to Form Properties > Hidden Field Default Value.

  4. Select Always Recalculate.

Keep Duty Staff and any user-facing scheduling fields visible.

Notes

  • In this example, the repeat frequency is 2, which means each staff member is assigned for 2 consecutive rows.

  • The total number of staff is 4, so the cycle repeats every 4 groups.

  • If your scenario is different, adjust the formulas accordingly.
    For example, if each person is assigned for 3 days, replace 2 in the Group No. formula with 3.

  • If the number of duty staff changes, update the Rotation No. formula to match the new total.

  • If multiple users submit records at the same time, duplicate numbering may occur.

Related Articles
Did this answer your question?