Summing and selecting specific date

AppSheet AppSheet Q&A
1

I have two tables. One is a table of invoice. There’s a [PAY] and [PAY TYPE]. I have another table that will sum up for the day. [Pay] is an amount, [Pay Type]= “credit”. I want to create a virtual row that sums up all the credits for the day.
select all with [pay type] = “credit”
sum up [Pay] if [Pay Type] = “credit” and [date] = today
Can somebody help with the expression?
My guess: sum(select([PAY TYPE] = CREDIT, [PAY], TRUE))

2

Tin_Nguyen1:

My guess: sum(select([PAY TYPE] = CREDIT, [PAY], TRUE))

Well, you got the SUM(SELECT()) portion correct, but that SELECT expression is nowhere near properly formed, give this SELECT article another review:

You’ll also need this:

1 Like
3

Here’s my actual expression.
SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],AND([PAY TYPE] = “Credit”, Daily Report[APP DATE] = Form Responses 1[App Date], TRUE)))

I want to filter today’s report( Daily Report[App Date]) with my Datasheet(Form Responses 1[App Date])
It doesn’t work.

4

Referencing a Column value in a Table, versus referencing ALL Column values in the entire Table Tips & Tricks ?

Having answered this question about 1 million times now, I guess I should make a tips-and-tricks post about it. Expressions of the form Table[Column] return a List type data value that contains the values in Column for ALL records in the entire Table. Most likely, If I’ve referred you to this post, the answer to your question is simply to change it to just: [Column] One of the most common places I’ve seen new app builders use this, is within SELECT() expression, like: SELECT( Table[Col…

1 Like
5

For the criteria part, I’m trying to pull only data from Table 1 that has the same [date] from Table 2. I tried
SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],AND([PAY TYPE] = “Credit”, Daily Report[APP DATE] = Form Responses 1[App Date], TRUE)))

Daily Report[APP DATE] = Form Responses 1[App Date] → I’m trying to filter the date from one table with another. It’s valid but not summing up. Is my first expression wrong?

6

Ahh… I got it to work with this.

SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],and([PAY TYPE] = “Credit”,[app date] = today()), FALSE))

However, what can I use instead of [app date] = today() ? What if I want to choose another day besides today?

7

I assume you’re running this expression from the context of a record in your Daily Report Table? Try replacing TODAY() with [_THISROW].[APP DATE]

2 Likes