Scheduled Email to each user that don't fulfill a condition

[eddie61]

Hi all

I have a timelog table where my employees enter their working time.

employeeID	starttime	enddtime	duration	date
23	10:00	12:00	2	15.09.2022
23	13:00	17:00	4	15.09.2022
34	08:00	12:00	4	15.09.2022
34	13:00	17:00	4	15.09.2022

Is it possible to create a scheduled (every evening) bot that sends a mail to all the employees that have a value less than 8 in the combined duration column of the date of today?

With the example of the table, the employee with the ID 23 would get a mail, as he has only logged in 6 hours total, and the employee 34 doesn't get a mail. The employee with the ID 18 also gets a mail, because he hasn't logged a row at this date.

Is a bot like that possible?

Thanks in advance.

Adrian

[TeeSee1 · Accepted solution]

In a bot's event that runs on a schedule base, select Employee table and set a filter condition similar to below..

SUM(
 SELECT(
  worktime[duration],
  AND(
   [employeeID] = [_THISROW].[employeeID],
   [date] = TODAY()
  )
 )
)
<
8

[eddie61]

Thanks a lot that looks great.

Unfortunately, I'm getting the following error now:

Arithmetic expression '(SUM(SELECT(Zeiterfassung[dauer],AND(([mitarbeiterid] = [_THISROW].[mitarbeiterid]), ([datum] = TODAY())))) < 8)' does not have valid input types

I guess it can't compare a duration (e.g. 07:00) with a number (e.g. 8). Is that correct? Is there a way to convert the duration of 07:00 to 7?

[TeeSee1]

Correct, If the column "duration" is of type Duration you have to convert it.

Use either TOTALHOURS([duration]) or TOTALMINUTES([duration]) or TOTALSECONDS([duration]) as you see fit.

Please consult the help doc files for details.

https://support.google.com/appsheet/answer/10108287

[eddie61]

Worked, thank you.

So I got this to work and send a mail to my account with all the employees that fulfill this condition. For each employee there is one mail sent, resulting in lots of mails.

Two questions:

1. Is it possible to just send a list of these employees (rows) to my mail and not a mail for each row?
2. Is it possible to send each of those employees (rows) a mail, but only to them and not others?

[1minManager]

1 = Yes.  You'd untick "for each row" and use something like 

<<START Select(worktime[KeyColumn],

SUM(SELECT(worktime[duration],AND(
[employeeID] = [_THISROW].[employeeID],[date] = TODAY())))<8

)>>

as a start condition in the template file 

https://support.google.com/appsheet/answer/11541779?hl=en

2 = Yes.  I'd suggest you create an Employee table, link each timesheet to this table with a Ref column, then you can run a Bot on each row of the Employee table

Simon@1minManager.com

[eddie61]

Thanks a lot for your help, really appreciate it.

I tried the START condition but I just get an empty list in my email.

<<Start:SELECT(Employees[employeeid],TOTALHOURS(SUM(SELECT(worktime[duration],AND([employeeid] = [_THISROW].[employeeid],[date] = TODAY()))))<8)>>

<<[name]>>
<<[mail]>>

<<End>>

Am I understanding this correctly?

 With this part I get all my rows from the "Employees" table.

<<Start:SELECT(Employees[employeeid],

 With this code I filter the selected rows from the "Employees" table.

TOTALHOURS(SUM(SELECT(worktime[duration],AND([employeeid] = [_THISROW].[employeeid],[date] = TODAY()))))<8)>>

 These are the values I pull from each of the filtered rows.

<<[name]>>
<<[mail]>>

I will try your solution to problem number 2 later. This one is already giving me headaches.

[TeeSee1]

---

@eddie61 wrote:

[employeeid] = [_THISROW].[employeeid]

---

you need to change [_THISROW] to [_THISROW-1]

[eddie61]

Works fine, I'm starting to understand this. Thanks a lot for your help @TeeSee1 and @1minManager 

Really appreciate it.

[eddie61]

I just looked into your solution for my second question.

I already have an employee table that is linked to the timesheet table. Are you proposing that I use the method from @TeeSee1 's first post? With the formula, I can trigger for each row in the employee table, that fulfill the condition. But how can I do it, that it sends the mail to the mail in these rows?

E.g.

Adrian should receive an mail (as he has only 6 hours logged in) and Peter shouldn't receive a mail (as he has 8 hours logged in). Every row in the employee table, that fulfills the condition, should receive an mail.

employee table

employeeid	name	mail
1	Adrian	adrian@test.com
2	Peter	peter@test.com

timesheet table

employeeid	date	duration
1	16.09.2022	4
1	16.09.2022	2
2	16.09.2022	4
2	16.09.2022	4

Hope it is understandable.

[TeeSee1]

Since you are sending an email per row, you can dynamically set the To field.

You should be able to select [mail] column.

[eddie61]

Sorry for the late reply. This works perfect. Thanks for all the help.

[MultiTech]

I recently talked about how to do something similar to this in a live stream

• here's the clip:

This example is based on a form submit - but there's elements in there that might be helpful

The things I'd do:

• Create a slice that holds "the users that need an email"
• Run an email task over that slice on a schedule
     - instead of triggering it, and doing all the file lookup stuff (you can likely ignore most of that), based on an interaction from a user

----------------------------------------------------------------------------------------------

Hope it helps!  (^_^)

[eddie61]

I could make it work with @TeeSee1 's solution, but I will save your video (and your channel) for future use. Thanks for all the content you are providing. Helps a ton.