Sum specific rows on one table. their sum is subtracted from a specific value in a different table

AppSheet AppSheet Q&A
,
1

My app requires a formula that will be used in a virtual column, that will calculate available field size for my app. Where the farm size is located in the farm table, and fields are in a different table linked by column Farm Name. in the app we have different farms and each farm has it’s fields. when the app user wants to create a new field, this virtual column will inform the user the available field size, for the specific farm, that can be used for the new field. What formula can I use on Appsheet platform.

2

Try something like

[ref_farm].[total_size] - 
SUM(SELECT(fields[field_size],[ref_farm]=[_THISROW].[ref_farm]))

for a VC in the fields table.

You can also create a VC in the farms table like

[total-size] - SUM([related_fieldss][field_size])