Need help with an expression

AppSheet AppSheet Q&A
1

Looking help with an expression to build both a view in the app and a report to be mailed.

Here is what I’m trying to achieve, this is a google doc that I created that allows the user to select a driver and input the total number of trips for that Section during specific dates. It’s a manual entry. I would like to generate a report similar to this, doesn’t have to be exact, actually it can be a list.

Selection_628.png
Selection_628.png1002×458 30.2 KB

Here is the table that documents the daily trip count per section

Selection_629.png
Selection_629.png1089×338 44.1 KB

I’m doing a sum select, which works fine to get a sum for a specific selection, clearly doesn’t show what I want. Should I add an AND in front with another condition to display the driver?

And for a report (PDF) what would the start expression look like?

SUM(
SELECT(
Shuttles[Section A],
AND(
([Date]>=[_THISROW].[Start Date]),
([Date]<=[_THISROW].[End Date]))))

Thanks for your feedback and input!! Also you don’t need to submit the entire expression, just need to get going in the right direction. Thanks!

2

Hi there. Quick ask: do u know that if u change the key to driver´s name it will be solved?

3

How? I can see where it changes the name in the table, but don’t see where it changes the result in the expression. Or…where should I change the expression?

I’m getting this, but it’s not showing the total for a section within a date range..

Selection_648.png
Selection_648.png582×216 14 KB

4

Clearly? I can’t make sense of your post. What does your data table look like? In what way does your results table not look as you want? What are the exact expressions you’re using? Where are you using them?

@diogolupcosta 's suggestion to change the row key is a bad idea.

2 Likes
5

Sorry for the late response, been dealing with an illness and I apologize if I didn’t show this correctly.

What I’m trying to do is show a sum of total trips for a section, for each driver within a date range.

I would like to generate that and send it off as a PDF

For example:

Date Range 4/1/22 to 4/15/22
Driver Section A Section B Section C
Rod 15 22 1

This is the table:

ID Driver Section A Section B Section C Date
4868347f Ken Casiday 4 1 5/11/2022
230c72ce Ken Pope 2 2 5/11/2022
d196eb45 Orlando Pate 6 5/11/2022
6d0bf38a Judy Ellefson 4 1 5/11/2022
08a0b4ee Trevor M. 2 4 5/11/2022
6 
<<Start:UNIQUE(
  SELECT(
    Shuttles[Driver],
    AND(
      ([Date] >= [_THISROW].[Start Date]),
      ([Date] <= [_THISROW].[End Date])
    )
  )
)>>

<<SUM(
  SELECT(
    Shuttles[Section A],
    AND(
      ([Driver] = [_THISROW-1].[Driver]),
      ([Date] >= [_THISROW].[Start Date]),
      ([Date] <= [_THISROW].[End Date])
    )
  )
)>>
1 Like
7

Close. Using this I get a sum of the total trips for that section within the date rage

SUM(
SELECT(
Shuttles[Section A],
AND(
([Date]>=[_THISROW].[Start Date]),
([Date]<=[_THISROW].[End Date]))))

How can I auto show that result by each driver and their total trips? i.e.

Sum of Section A = 28

Tom 5
Don 10
Mary 13

and not having to select the driver in the form?
Again, only choosing a date range
or, is that possible?