Run a sequence of Actions not based on the same table

[Griff]

I have a table where users enter their leave requests including Name, StartDate and EndDate.
When the request is approved I need to take that single record and add one record for each Weekday of the leave period to another table (Inputs).

To accomplish that I want to set up the following series of Actions when a supervisor approves the leave request:

1. Set the Selected By field in the Leave Requests table to USEREMAIL() to identify the record that’s just been approved

2. Sets the value of the Date field on Row 2 of the Dates table to the Start day of the leave period that’s just been approved (Dates is a spreadsheet that lists week days starting from the date entered in Row 2)

3. Perform an AddRecord action for each row in the Dates table, Filtered to only include dates up to the EndDate of the Leave Request record
   The AddRecord action adds the required record to the Inputs table

4. Reset the Selected By field in the Leave Requests table to blank

Essentially I think I have worked out each of the steps and tested steps 1 & 2. I wanted to test the sequence by creating a grouped Action but it looks like I can only run a grouped action if all the Actions originate from the same table.

Is there any way around this, or do I need to go back to the drawing board again?

[Lucinda_Mason]

I use what can be called a bridge action.
This is the combo action that runs two actions. The first action is based on the same table, Requests.

But the second action is ‘called’ from the Requests and acts upon the Users table.

Will this help you?

[Griff]

I think I have it worked out but will test a little further. Thanks very much for your support and input. I will respond again as soon as I can

[Griff]

Hi Lucinda, thanks for the suggestion. It seems like I don’t understand Actions properly. How does an Action called from the Requests table work on the User’s table? Does your Set_Last_Used action act on the Users table or Requests table? If it works on the Requests table, it wouldn’t even appear on the drop down would it?

[Steve]

Griff:

Sets the value of the Date field on Row 2 of the Dates table to the Start day of the leave period that’s just been approved (Dates is a spreadsheet that lists week days starting from the date entered in Row 2)

This step requires a sync. An action can set the Date column value of row 2, but the spreadsheet won’t see that new value until a sync occurs. Unless your process explicitly stops and forces that sync to occur, the spreadsheet won’t get the value, won’t recalculate with the desired dates, and the app won’t see the new row values from the recalculation. But if you stop and force the sync, the user will then have to manually resume the process after the sync completes.

[Steve]

This is how I might approach your problem…

First, replace the Selected By column with AddDate of type Date. Then add the following actions:

(action 1)

This is the action that you should invoke to approve the leave requests: it does everything.

• For a record of this table: Leave Requests
• Do this: Grouped: execute a sequence of actions
• Actions: (added below, after actions 2, 3, and 4 created)
• Only if this condition is true: ([StartDate] <= [EndDate])

(action 2)

This manages the AddDate column value, which contains the next date within the requested leave date range to add to the Inputs table. It’ll start at StartDate and increment to EndDate.

• For a record of this table: Leave Requests
• Do this: Data: set the values of some columns in this row
• Set these columns:
  • AddDate: IF(ISBLANK([AddDate]), [StartDate], ([AddDate] + 1))
• Only if this condition is true: TRUE

(action 3)

This action adds one row to the Inputs table using the current value of this row’s AddDate column.

• For a record of this table: Leave Requests
• Do this: Data: add a new row to another table using values from this row
• Table to add to: Inputs
• Set these columns:
  • Date: [AddDate]
  • (other columns as desired)
• Only if this condition is true: TRUE

(action 4)

This action implements an action loop by recursively performing (action 1) for each successive date in the requested leave date range, from StartDate to EndDate.

• For a record of this table: Leave Requests
• Do this: Data: execute an action on a set of rows
• Referenced Table: Leave Requests
• Referenced Rows: LIST([_THISROW])
• Referenced Action: (action 1)
• Only if this condition is true: ([AddDate] < [EndDate])

(action 1)

Return to (action 1) and add actions (2), (3), and (4) to its Actions list.

• Actions:
  • (action 2)
  • (action 3)
  • (action 4)