Adding rows in a third table based on values in 2 others

[cdevitt]

Hello.  I'm a newbie to Appsheet and trying to create what I thought was a simple app to onboard employees and track tasks to be done to prepare for their arrival (the templates don't work for this).  I have one table where new Employees are entered, one for the standard list of Tasks that need to be done for every employee and one for the actual Assignments that have due dates.  My thinking was Assignments would get a copy of every row in Tasks and the triggering employee ID would be added as a column to tie everything together.  I have created the action/bot to copy every row from the Tasks table but I cannot seem to get the triggering employee ID to be referenced.  Is what I'm trying to do possible (and appropriate?)?  If so, how can I copy the triggering ID into the Assignments table with each task.  Thanks for any help.  Happy New Year all.

[Marc_Dillon]

I would accomplish this by flagging the record with the current timestamp (set the value UTCNOW() to some datetime column on the table), then in your add-new-row action you can look up the record's id with MAXROW().

[cdevitt]

Thanks - can you give me a bit more detail as I am new to Appsheet?  Am I setting the timestamp in the Employee table?  I think I am leaning toward the intermediate step being to add the relevant employee ID to the Tasks table.  If I use Maxrow to get the latest employee added, how do I copy the employee ID to the Tasks table?  Right now my add new row action is copying each task in the Task table to the Assignments table.  Hope I'm making sense!  Thank you so much for responding. 

[Marc_Dillon]

Yes, timestamp the employee record you want to add the tasks for. In the same add new row action where you're copying a task to an assignment record, that's where you use the MAXROW to set the value of the employee column.

[TeeSee1]

Another possible approach (test is well before you move this into production) is as follows.

Create the following three actions on Employees Table

1. Copy Task to Assignment

Add a new row...

[ref to employee] = [_THISROW]

[ref to task] = INDEX(TASKS[task_id] - SELECT(ASSIGNMENTS[ref to task], [ref to employee] = [_THISROW]), 1)

(optional) Only if this cond is true: ISNOTBLANK(INDEX(TASKS[task_id] - SELECT(ASSIGNMENTS[ref to task], [ref to employee] = [_THISROW]), 1))

If you want to copy a bunch of fields from TASKS, then create fields in ASSIGNMENTS and set their APP FORMULA to something like [ref to task].[field name]. The values will be auto-populated. For instance, if your TASKS table has [due in days from on boarding] (numeric, different for each task) and EMPLOYEES table has [on boarding date], then the [Due Date] in ASSIGNMENTS can be calculated as

[Due Date] = [ref to task].[due in days from on boarding] + [on boarding date]

2. Loop Repeat

Execute an action on set of rows

Ref'd Rows: LIST([_THISROW])

Ref'd action: Loop Start

Only if this cond is true: ISNOTBLANK(INDEX(TASKS[task_id] - SELECT(ASSIGNMENT[ref to task], [ref to employee] = [_THISROW]), 1))

3. Loop Start

Grouped action: set actions 1 and 2 above

(optional) Only if this cond is true: ISNOTBLANK(INDEX(TASKS[task_id] - SELECT(ASSIGNMENT[ref to task], [ref to employee] = [_THISROW]), 1))

Set up a bot on Employees table row adds.

Run an action on set of rows...

Ref'd Rows: LIST([_THISROW])

Ref'd action: Loop Start

Hope this gives you another option.