Prepopulate table depending on 2 tables

Hello,

I am creating a time tracking app. The times are going to be updated only by the team manager, so I am trying to create a set of records each week, so every week the team manager will create a new time sheet for the week that will be a cartesian between the dates and his team members automatically.

So far I can create the records for only 1 employee using the API, but I can’t figure it out how to call the API looping the employee table.

These are my tables:

And the JSON that I am using is :

{
"Action": "Add",
 "Properties": {
    "Locale": "en-US",
    "Location": "47.623098, -122.330184",
    "Timezone": "Pacific Standard Time"
  },
 "Rows": [
 <<START:SELECT (lkp_dates[date], AND( [date] >= [_THISROW-1].[start_date] , [date] <= [_THISROW-1].[end_date]) ),
SELECT (employee[employee_id], true) >>
 {
	 "id":"<<UNIQUEID()>>",
	 "employee_id":"<<employee[employee_id]>>",
	 "date":"<<[date]>>",
	 "paid_by":"<<[_THISROW-1].[pay_period_id]>>"
 }
 <<END>>
 ]
}

how can I make this call using all the employees?

Thanks in advance for your help :slightly_smiling_face:

If you are OK NOT to use API, you can implement this (or something similar) by modifying this technique.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-add-row-per-value-in-EnumList/m-p/357133

Basic idea is

  1. You set up a control table like
    [manager]: key, to reserve one rec per manager
    [pay_period] :ref
    [dates]: vc to populate a list of dates related to the [pay_period] selected
    [index]: number

  2. Let the manager choose a pay_period

  3. “Action 3: Add Row For This Enum” in the FAQ needs to be modified
    3-1. Create on the table employees an action to “add a new row to another table (time_card) using values from this row”
    Use values both from Employees and the Control table (using LOOKUP())
    3-2. Call the action created in 3-1 for each of the [dates] (should be seven days or whatever you define in each pay_period) by an action of type “Data: execute an actionon a set of rows”
    For a record of this table: control,
    Referenced Table: employess
    Referenced Action: 3-1

  4. Repeat

I have added a step to initialize the index. This can be done manually in step 2)

This is a demo app I created. See timesheet_2. (https://www.appsheet.com/portfolio/3401559)

Note. I have used security filter to control the control table which forces user login.

Copy the app and play with it if you like.

Animation.gif

2 Likes