Recently, we received the following request from a customer: they wanted to generate bookings for participants. Each participant could have bookings for several weeks. Instead of manually creating all the records, they simply wanted to select the participants and the weeks so that all the bookings for each combination would be generated automatically.

Another example where this could be handy is generating the records for the clothes that are in stock. Let’s say you have blue and yellow jackets in sizes S, M, L. You want to generate the records for all the possible combinations.

We will demonstrate the settings using the booking example.

To begin with, you should have three tables: Bookings, Weeks, and Participants. You will have several records in Weeks (Week1, Week2, Week3 etc.) and in Participants (Participant1, Participant2, Participant3 etc.) tables. We will create a Booking record where we can select specific weeks and specific participants. The aim for the system is to generate the booking records for all possible combinations of the week and participant values we select.

The system goes over all the selected weeks, and then in the second cycle, it processes all selected participants. As a result, the records for all possible combinations will be generated.

First of all, create multi-reference columns for Weeks and Participants in the Bookings table. Here, you will select the values for generating the bookings for all the possible combinations.

Part 1: processing weeks

To process the selected weeks we need to create a Recordset column and “Dummy” timestamp column that will be used for the workflow action later.

  1. We need to pick out the records (weeks) for which bookings will be generated. Create a recordset column “Week Booking Recordset” in the “Bookings” table under the “Week Bookings” relation.
  2. Create a “Dummy” timestamp column in the “Week Bookings” table.
  3. Now let`s assign a value to that “Dummy” column: in the “Booking” table create a workflow action “Loop All Week Booking”. This action aims to assign the Now() timestamp to all the records in the “Week Booking Recordset”.

Part 2: capturing the current week

We need to capture/memorize the current week that the system is processing:

  1. Create a “Dummy Current Week” column in the “Bookings” table.
  2. Create a workflow rule “Set Dummy Current Week” in the “Week Bookings” table that will identify the current week that the system is checking.

Part 3: processing people

Now we need to go over the selected Participants.

  1. Create a many-to-many relationship between “Week Bookings” and “Participant Bookings”.
  2. Under this relationship create a recordset column with the following settings:

The purpose of this recordset is to get the Participants that we have selected in our multi-reference column when creating a new booking record.

Part 4: generating records

The last step is creating a workflow action, that will actually generate those records with all the possible combinations of selected Weeks and Participants.

  1. The action “Create Week Participant Records” must be configured in the “Week Booking” linking table:

2. In the “Week Participant Booking” linking table create a lookup column “Dummy Current Week” from the “Week” table. And then add this lookup as the Default/Calculate to the “Week” reference column.

In the “Week Booking” linking table, create a record change trigger and assign the two workflow actions configured above. This trigger will update the current week that is being processed and generate the records with combinations:

Once all the above settings are done it is up to you how to run it: you can create a custom button that the user will click or a trigger that will run automatically once the initial record is created.

Additional option

If you made a mistake when generating the records, you can make the settings to remove the wrongly generated records.

To do that, create a recordset column “Generated Records” in the “Bookings” table. This must be done under the linking table “Week Participant Bookings”.

This recordset column will pick up the records, previously generated by the workflow action. And you can delete those using a delete record workflow action applied to this recordset. This way you can delete the earlier generated records if you need to make changes in your selection and regenerate the records for the same booking.

As a result: you can create a record in the “Bookings” table and select Week1, Week2, Participant1, Participant2. Run the settings (by trigger or custom button) and the system will generate the records for all the possible combinations of the selected values.

This might be a bit extravagant, but we hope you can make use of it. Check out the settings in this template, available for free trial.

Author
Kateryna Dmytrenko
Date
Share