Creating daily report for summary email

airind · 01-05-2022 05:32 PM

I have found a few other threads but none seemed to have an answer. I see I’m not the only one experiencing this.
Sidenote - it is frustrating that you can’t test scheduled automations before you deploy, because now I’m deployed and it’s not working.

I have an app that is used to log employee absenteeism - a person enters the absence into the app, in real time it emails their leader to let them know.

Then at the end of the day, I need it to email out a summary of all the people who were absent today.

I have the email template, coding, etc. All works great. But now that I’m deployed, it says The event and process of the ‘End of Day’ Bot are not compatible. The output of event ‘Absenteeism Report’ and the input of the process ‘Daily Report’ must be the same table or ‘None’ in the case of a scheduled event that is not ‘For Each Row’.

When I turn on “for each row”, it sends one email for every single change made that day. We have a lot of absenteeism lol. This is not feasible.

When I turn off “for each row,” this is the error. But It is attached to the table, and I need a report on the actions on that table. When I tried to create a new task with no table attached, it doesn’t even populate the email i create to use.

How can I attach this daily report to the table but have it send out the email only once, daily at a certain time as a summary?

airind

Plot twist - the email sends out at the time I set with only one instance but the app fails. It sent out at the times I set no issue, but the app itself says it’s unable to fetch app definition and that’s not runnable.

So the setup kinda works, but it thinks doesn’t.

Marc_Dillon

Scheduled Events, without ForEachRow enabled, must be paired with a Process and Task that are not attached to any Table.

airind

Exactly. Which is not helpful lol. Every row in table sends one email for every instance, not one summary email.

But I have created a workaround by locking the first result of my google sheet with a unique ID in an ID column, and using a filter condition on the event to restrict the rows to only those with that ID (ie only one row)

Then my email table still reports all the people who are absent today, as per the formula in the template.

It sends out only one email this way. But I do not like MacGyvering things and hope to find an actual solution.

Marc_Dillon

How is it not helpful?

Sounds like you have a ForEveryRow Bot, with an attached Process/Task that only sends a single record’s of data.

You need a new Process/Task that is not attached to any Table, that sends all records at once.

airind

I’ve read that page so many times, it was very helpful when creating my email template.

I created a new process and task not attached to any table and nothing happened. The email didn’t send, the process didn’t execute. The email template filters perfectly and only returns the records I want. The trouble I’m having is getting the automation to send one email at a specified time with all of today’s records.

How might I get a process and task to send all records at once if it’s not attached to a table?

airind

Hmm I see the example of Start. I’ll try that now.

airind

@Marc_Dillon I added <<Start:Absenteeism Reporting Client Care[ID]>> at the very beginning and < < End > > (without spaces) to the very end of my template, I used ID because that’s my Key Column, but it didn’t trigger. Is that the right code to return ALL results?

I’m going to have to come back to this tomorrow, I’m getting way too frustrated lol. It shouldn’t be this hard to send a daily summary. What if I didn’t want to use a template? In the old reports, this would take me 2 minutes. Now it takes 5 different steps, a complicated email template, a start expression, an end expression… and I couldn’t test it before I deployed it. Luckily I deployed it early and set the emails to send to me because I had a feeling. And I don’t discount this is just me not understanding, but I see so many threads of people just like me not understanding.

airind

Update: nope. Cannot get it to work.
And I’ve consulted with 2 appsheet experts in my organization who also can’t figure this out.

If I attach it to a table, it sends one email for each row
If I don’t attach it to a table and use a start expression, it duplicates my template for every instance (so if I have 20 reports, my template repeats 20 times in the email).

I cannot figure out how to have it send ONE email with ONE template instance as an aggregate of the day’s activity. Every way I try sends multiple reports.

If anyone knows how to use a start expression to solve for this, or any other trick, I’m fairly desperate.

Marc_Dillon

Sounds like a problem with your template. Post it here so we can see where you may have gone wrong.

WillowMobileSys

I have looked over your template

Because it appears that most of your instances where you are referencing the Absenteeism Reporting Client Care table are aggregations - COUNT, SUM etc. - you do NOT need the first START block at the top and END at the bottom. So remove that Start/End that you had added.

However, you do have Team Member tables in several places that are NOT in the proper format and it would appear to me to be causing errors that are preventing the template from properly executing. You should be seeing errors in the log entries.

In this example above,

The START block expression should be the first thing in the first cell and Team member column under it - no spaces or line breaks between.
Each column value reference must have square brackets - e.g. `<<[Team Member]>>’

Marc_Dillon

Yah I have no idea what’s going on in that template. Maybe you need to take a large step back. The basic concepts just aren’t as difficult as you’re making them out to be.

Start with a very simple template:

<<START:absenteeism_table[key_col]>>
<<[absent_employee]>>
<<END>>

Make a new Bot.
Make a new Event, schedule-driven, ForEveryRow not selected.
Make a new Process, select “none” as the Table.
From the Process, create a new Task, and use the above template.
Test it.

airind

My original hack works, I will stick with that.
The hack is to attach the bot to the table, but set a filter condition to only look at one row
Even though it’s only looking at one row, it still populates everything in my template for the day.
And only sends one email

WillowMobileSys

Hmm, I am surprised this even would work because it seems to me the template has syntax errors where the Team Member columns are not encased in square brackets.

Regardless, I think if you look closely at the Team Member table data, you are likely not getting the results you would expect.

If you apply the 2 corrections I noted in my post above AND turn off the “ForEachRow” setting, I think you will be good.

Steve

Yeah, this an oddity of the template parser: the text between << and >> can be just a column name, in which case the column value is substituted. It does work. I don’t know how the parser decides when to treat it as a column name versus an expression. Probably: does it match a column name? If yes, it’s a column name; if no, treat as expression.

WillowMobileSys

I see. I didn’t realize this.

@airind This explains why the template works when you have the “ForEachRow” turned ON. However, `<<[Team Member]>> in each Team member table will be the value from the row passed to the template. Based on the filter conditions of each expression used in each table, that is not what you want and the tables will not show correct team member info.

When “ForEachRow” is turned OFF, there is a template error because <<[Team Member]>> is outside of any expression AND there is no row passed to the template. This prevents the template from executing.

airind

You’d think so, but nope! The team member info is correct and populates just fine. The row I specified has a team member name of “DO NOT EDIT” lol.
The entire template works just fine when filter condition for the first row. If populates all information .

I also do not get any errors and all information is correct.

WillowMobileSys

Coming to the party late but I think it’s important to emphasize Scheduled Bots and the “ForEachRow” setting.

If you have “ForEachRow” turned OFF, the Scheduled Bot run criteria simply acts as a “Run/Don’t Run” switch - that’s it! No rows are passed downstream. Typically, in this case, the template will control what rows are used and appear in the generated doc through a START/END block. Perfect for a summary report which sounds like what you need!

Just to contrast…

If you have “ForEachRow” turned ON, all rows matching the run criteria are sent downstream to the Process. If you are generating a doc from a template, you will get each row passed to the template. You could ignore the passed in row and use your own START/END block to choose your own set of rows BUT the document will still be generated once per row that matched the Bot run criteria.