Sending emails in bulk

[Wiktor_Jurek]

I’m trying to set up an appraisal system for our HR department. On a ‘per employee’ level, tracking holiday requests, credentials, training and hardware works super well for us. We’re really happy.

The only issue I’ve come across are appraisals. The difficulty in this is that it’s a four step process.

1. An appraisal form is sent to every employee to complete.
2. The employee fills out the form and the time that they would like to meet.
3. The HR department looks at the appraisal and confirms the meeting.
4. The meeting happens, and the appraisal can be marked off by the HR department

Step 1 difficulties:
Is there any way to send out uncompleted forms on a per-employee basis? I’d like to iterate through the employees, create a blank record for each one, notify them, then they can log into a separate appsheet instance and fill out the form.

Step 2 difficulties:
This is no issue, the employee can use a separate appsheet instance or view, and fill out the blank record, and we can use a behaviour to stamp it as ‘done’, and send a notification to HR.

Step 3 and 4:
No difficulties.

The main issue lies in creating these records for every employee to fill out, and emailing the employees that the records are there for them to fill out.

Any help appreciated!

[WillowMobileSys]

Wiktor_Jurek:

I’d like to iterate through the employees, create a blank record for each one

Is there some specific reason why there is a need to pre-establish a blank record?

The typical manner in which this might be done in AppSheet is to compare the list of completed entries against the list of employees and then send an email to each employee that has yet to fill out the form. The email would contain a link to the AppSheet Form view, employees click on the link, fills out the form and then upon tapping Save, the new row is inserted with all of their Form entries.

You can implement the Form to be dynamic to guide the user through the entry of fields such that certain fields must be completed or showing additional fields to enter based on entered values.

For the sending of the emails to a list of people, you might start with the article below to get you started.

help.appsheet.com

Sending Email from a Workflow Rule or Scheduled Report

A Workflow Rule can send email when data changes. A Scheduled Report can send email on a schedule you specify.

[Wiktor_Jurek]

WillowMobileSystems:

Is there some specific reason why there is a need to pre-establish a blank record?

The motivation behind creating the dummy records is to easily keep track of who had completed the most recent forms - If it’s blank, then it’s very clear that they haven’t.

WillowMobileSystems:

The typical manner in which this might be done in AppSheet is to compare the list of completed entries against the list of employees and then send an email to each employee that has yet to fill out the form.

Is this something that can be automated, or will this have to be a manual task? I guess in this case I’d still be asking the same question - that being is there a way to calculate the ‘missing’ records, and send emails in bulk to the folks that haven’t completed it?

[WillowMobileSys]

It can all be automated. Here is the way I would at least begin with building the app.

1. Create a table for Employees including email address and enter all of the employees.
2. Create a table for Appraisals.
3. Create a Form view that is the appraisal form employees complete.
4. Create a workflow to send an email to employees who have not yet completed the appraisal. It would use an expression to identify this list of email addresses. The list is applied to the “To” property for the list of employees to send the email to.
5. Include in the email a link to the Appraisal Form such when an employee click on it they are taken directly to the form to be filled out.
6. Introduce some process that will re-run steps 4) - 5) until all employees have completed the appraisal.

---

I understand this all very high-level and basic. But I don’t where you are in understanding of the AppSheet platform nor how far you have progressed in the building of an app.

I would for now concentrate on steps 1 - 4 in that order. Let us know where you are in the build process and what specifically you need help with or have questions about. Someone here will help!!

[Wiktor_Jurek]

Fantastic answer. Thank you very much. I’m going to struggle most with this:

WillowMobileSystems:

Create a workflow to send an email to employees who have not yet completed the appraisal. It would use an expression to identify this list of email addresses. The list is applied to the “To” property for the list of employees to send the email to.

Mainly around the expression that identifies the missing records. From my limited understanding, I can only trigger workflows by a record change. Does this mean I’ll need to have some sort of script that edits a field within a record to trigger a workflow that then emails the corresponding people?

In any case, I’ll do some more digging - thanks for your help!

[WillowMobileSys · Accepted solution]

Wiktor_Jurek:

I can only trigger workflows by a record change.

While I referred to Workflows, there actually are also Reports. These are very similar to Workflows but are scheduled events - e.g. Daily, Weekly, etc. I would recommend using these for an automated approach. Of course, you likely only want it running until all employees have performed their duty. So you could implement something that notifies you (or someone) once all appraisals are done and then disable the Report.

As for an expression, it would not be overly complicated. Assuming you have an Employees table and an Appraisals table with an Appraisal Name column and an Employee column, the expression would be something like this:

SELECT(Employees[Email], 
       NOT(IN([Employee ID], 
           SELECT(Appraisals[Employee], 
                          [Appraisal Name] = "This Appraisal"))))

In words this means, "return a list of employee emails for employees who have NOT completed “This Appraisal”.

This can at least be a start to get your email list. You may need to tweak it.

[Wiktor_Jurek]

That’s brilliant. Exactly what I needed. Hadn’t considered reports at all.

Thanks!