Creating a table from 3 other tables

AppSheet AppSheet Q&A
1

Hi

I am new to AppSheet application for budget creation.

Three input tables

  • Month (Just 1 -12)

  • Month_ID

  • Employee hours by department

  • Employee

  • hours

  • Employee Department

Salary

  • From period

  • To period

  • Salary

  • Hours per week

Output table - Budget

  • Month

  • Employee

  • Department

  • Amount

I need a routine to create all records in the budget table with something like this

Month = 1

([Employee hours by department].[row_id] = 1

(

insert budget.amount = salary.salary*[employees hours by department].Hours/[salary].[hours per week], month = Month.month, employee = [employees hours by department].Employee

where salary.From_Period>= Month,

salary.To_Period<=Month,

salary.employee = [employees hours by department].Employee

)

[Employee hours by department].[row_id] = [Employee hours by department].[row_id]+1)

Month = Month+1

Sorry, I am not explaining this well but I just want these three table to be used to populate another table.

2

Hi @swamija ,

Here’s a simplified breakdown to guide you:

— You want to loop through:
→ Each month (1–12)
→ Each employee + department (from Employee Hours table)
→ Each matching salary period (from Salary table)

For each combination, you want to insert into the Budget table:
Amount = Salary × (Employee Hours ÷ Hours per Week)

How to approach in AppSheet:
Use an AppSheet automation (Bot) or action to “add rows to another table”

Build a FOR EACH loop over the Month table
Inside that, loop over Employee Hours by Department
Inside that, check if the Salary record applies for that month and employee
Calculate Amount using an expression
Insert the result into the Budget table

Because AppSheet does not support true nested loops directly, you may need to:
— Precompute or flatten the data with virtual columns or
— Use a Google Sheets formula or Apps Script to generate the bulk data and then sync it into AppSheet

1 Like