conditional select

AppSheet AppSheet Q&A
,
1

I’m just trying to get my head around AppSheet.

I have two tables:

  1. “Members” with columns: “name” and “Balance”

  2. “Payments” with columns: “name”, “payment date”, “amount paid”.

Multiple payments will be there on various dates in the Payments table.

I want to sum all the amount paid for a member and fill it against the “Balance” column.

I tried the below expression but getting an error

sum(select (Payments[amount paid],[_THISROW].[name]=Payments[name]))

→ says Cannot compare Name with List in ([_THISROW].[name] = Payments[name])

Both “name” columns are of type “name” and I think because there are multiple rows for the same name, it is returning a list but I can’t figure out how to make this work.

Can someone please help?

2

Payments[name] is a list of all names in Payments table, not a single value. Your expression should be:

SUM( SELECT(
Payments[amount paid],
[_THISROW].[name]=[name]
))

5 Likes
3

Expression Payments[name] is really list.

  1. Im dnt understand how you “links” tables by Name field. What about “key” fields?

  2. https://help.appsheet.com/en/collections/377977-expressions

  3. Try to use [name] without Payments prefix

1 Like
4

Thanks buddy. This works!

2 Likes
5

Thanks buddy.

  1. Name is the key field for members table. For payments field, I was struggling to assign a key column as there will be multiple entries of names (payments made on different dates will be added). so I created a computed key concatenating date and name. Not sure if this is a good approach but still learning.
2 Likes