conditional select

[sp_nz]

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?

[Joseph_Seddik · Accepted solution]

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]
))

[sp_nz]

Thanks buddy. This works! 

[bonameblisto]

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

[sp_nz]

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.