Appsheet - Summary per engineer

AppSheet AppSheet Q&A
,
1

Hi,

I need some advice on creating a Summary report that runs daily that shows the sold amount per engineer per day. I basically have two tables:

  • Intervention

Columns: InterventionID, Client Name, Email (Security Filter), Date, Duration

  • InterventionDetails

Columns: InterventionDetailsID, InterventionID (Ref), Email (Security Filter), Product, Amount, Price, Total

An intervention can have one or more InterventionDetails rows.

I would like to create a workflow that sends an email every day showing the total amount sold/used per engineer for the day. So the table would be:

Engineer name (=Email), Total Interventions, Total Amount used/sold

I have been looking into how to do this but I can’t find the answer to this. Anyone that has a suggestion on how to achieve this?

Thanks in advance!

2

Some quick pointers.

Firstly you’re going to need a column to record the users email address. Create one with an initial value of UserEmail()

Next you’re going to need to create a template for the email body and/or attachment. You’ll need to use a START condition to list all the items in the subtable

https://help.appsheet.com/en/articles/961746-template-start-expressions

Finally you’ll have to create a Bot to run on a schedule and then email the template

https://help.appsheet.com/en/articles/4865307-appsheet-automation-the-essentials

So all-in its a lot to learn. My recomendation woud be to use one the of the Appsheet Developers (like me) to rattle this all off for you in less than an hour. Then you can use that to learn how its done and tweak it later. Sorry if this sounds like a sale pitch, it isn’t meant to be. But thats how I started off and sometime I still pay people to show me how specific bits work, even with 6years appsheet experience!

Simon@1minManager.com

3

Hi, thanks for the advice but not really an answer to my question. I have the app already running. I have a table with UserEmail() already. Have a template already that now gives a report of all the interventions and a Global Sum of them all but I would like to have a sum per engineer.

My current Start condition is <<Start:ORDERBY(SELECT(Intervention[InterventionID], [Date] = TODAY()),[Email])>><<[Email]>>. With a Global SUM at the end of the table <<SUM(SELECT(Intervention[Total], ([Date] = TODAY()),FALSE))>>, I added a VC to make this work. This works fine but I would like a summary per engineer in one report, not globally.

Thanks in advance!

4

You mean one report per engineer?

You would need an “Engineers” table and execute the bot on “ForEachRowInTable” in order to make AppSheet make X number of reports based on the rows of that table. You could create a condition to just execute it on the rows you want.

If you need just one report with all the data of your engineers separated:

Make a Start: expression that gives you the engineers and wrap the whole page of the report with it or just a table, for example. So that appsheet can create new pages per engineer or new tables per engineer.

Your inner expression would need to be changed in order to make sure just get the data from the current engineer.

We need your tables schema to help any further