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
[_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.
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |