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:
Set the Selected By field in the Leave Requests table to USEREMAIL() to identify the record that’s just been approved
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)
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
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?
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?
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
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?
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.
This is how I might approach your problem…
First, replace the Selected By column with AddDate of type Date. Then add the following actions:
This is the action that you should invoke to approve the leave requests: it does everything.
([StartDate] <= [EndDate])
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.
IF(ISBLANK([AddDate]), [StartDate], ([AddDate] + 1))
TRUE
This action adds one row to the Inputs table using the current value of this row’s AddDate column.
[AddDate]
TRUE
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.
LIST([_THISROW])
([AddDate] < [EndDate])
Return to (action 1) and add actions (2), (3), and (4) to its Actions list.
User | Count |
---|---|
40 | |
34 | |
28 | |
23 | |
18 |