I have two forms, an Expense form and a Purchases form. I want to very simply set up the expense form so it is:
Account Number Budget Spent Balance
xxx-xx-xxxx $15,000 $1,294.56 $13,705.44
yyy-yy-yyyy $20,000 $0.00 $20,000
Very simply I want the Spent column to automatically pull the total sum of all the records in the Purchases form that matches that account number. I tried doing all this scripting that would automatically send a purchase submitted to the spent field but that didn't work properly because anytime you edited the field or updated, it would re-add the information so the spent column was never accurate. I've tried creating a bidirectional field in the Purchases form connecting the account number but the Spent column will only show one of the purchases cost, not the sum.
How do I just make the Spent field pull all the sum of the cost from the purchases form and always stay up to date when changes are made to the purchase?