Select value based upon multiple condition

AppSheet AppSheet Q&A
1

Hi, I have 2 tables. One is items which has (Item id, item name, amount ) columns and other is donation which has (donation id, donation amount, Item, beforeamount ) columns. Item columns in donation refers to item table. Item id in item and donation id in donation are unique id in their perspective table. Now i am trying to update amount in Items table when amount in donation is increased or decreased for that particular transaction. I am trying formula given below but getting error

[Amount] - (ANY(SELECT ( Donation[BeforeAmount], AND ( [Donation ID]=[_THISROW].[Donation ID], [Item ID]=[_THISROW] ))) - [_THISROW].[Amount])

error

Error in expression ‘[_THISROW].[Donation ID]’ : Unable to find column ‘Donation ID’

Please help

2

[_THISROW] refers to the items table which, according to your description, does not have [Donation ID]. Therefore the error.

A question on what you are trying to achieve.

amount = sum of donations’ before amount that contain the related item?

donation has no place in this calculation?

If above is correct

[Related Donations].[BeforeAmount] should do the job. ([Related Donations] should have been created automatically by the system)

See this for more details

EDITED:

A regular column’s app formula is not calculated unless you make changes to the row.

A virtual column may be better.

See this.

1 Like