I have 2 simple tables. employee and payroll...

AppSheet AppSheet Q&A
1

I have 2 simple tables.

employee and payroll. i created the employee table and payroll via a form.

so for each employee, there will many records of payroll.

the tables are connected via employeeID as a REF key.

in the payroll table, there are the following columns being recorded for each employee.

DATE,

INCOME, TIPS.

each employee will be recorded a row each day for their INCOME and TIPS.

eventually, the payroll table will have many rows by DATE.

so at the end, i want to have select an EMPLOYEE and a range of the DATE, like the FROM and TO DATE to select from, to sum up their INCOME and TIPS.

can i read from both tables, EMPLOYEE and PAYROLL, to retrieve the records where the DATE is between the certain range and SUM up the INCOME and TIPS?

please let me know if i am not explaining it correctly for your understanding.

thanks!

2

@Tuan_Dang Unfortunately, there isn’t a straight forward way to do this in AppSheet (maybe a community wizard can prove me wrong).

Here’s a sample app that gets pretty close to what you want: appsheet.com - Sum by date - Compute stats over a custom date range Sum by date - Compute stats over a custom date range appsheet.com

3

As Tony wrote, you can’t have a summing row in your table, but you can do that in the employee table. Add two normal columns like Start and End. Then create a virtual column with the app formula like SUM(SELECT(Payroll[Income],AND([Date]>=[_THISROW].[Start],[Date]<=[_THISROW].[End]))).

1 Like
4

@tony Thank you so much!

5

@Aleksi_Alkio Thank you so much. That expression works but i also want to filter by employee.

So the start and end date works but it summed up all the employees.

On this payroll report form, I also include dropdown list with all the employees to choose from.

So each employee name has an assorted employee id PRIMARY KEY, but in order to make the match, i do not want to match it by name since it is not UNIQUE. I realize you made use of [_THISROW] syntax to pull the data that is currently selected on the form.

but this will only pull the name of the employee name.

how do i create a dropdown list that has both employee name and its associated employee id but only display the employee name on the dropdown list this way, when an employee is selected, i will take the employee id to read another table to retrieve the correct records for that employee?

Thanks!

6

@tony Thank you for the example.

I am a bit confused of the additional columns that have been added the CUSTOMERS and ORDERS tables even though they are not in the spreadsheet itself.

can you explain me what this syntax mean in the APP formula?

REF_ROWS(“Orders”, “Customer Name”).

Thanks.

7

@tony Ignore my last message. It was created because of the REF.

8

@Aleksi_Alkio I was wondering if you can help me with my AppSheet that

i created.

I am not able to sum up by selected employee and a date range to retrieve the total income.

I was only able to sum the income by date range, but not by date range and by selected employee.

this syntax worked: =SUM(SELECT(TIMESHEET[INCOME], AND([DATE]>=[_THISROW].[DATEFROM], [DATE]<=[_THISROW].[DATETO])))

I am not sure if my Payroll table is set correctly in order for me to sum the income by selected employee.

I can give you the link to the Appsheet that i created to see if you can check it for me.

Thanks.

9

Try something like… =SUM(SELECT(TIMESHEET[INCOME], AND([Employee]=[_THISROW].[Employee],[DATE]>=[_THISROW].[DATEFROM], [DATE]<=[_THISROW].[DATETO])))