I have a medical bill worksheet. My hope is to have a single row represent the amount charged. I have a column for Adjustments from the Provider and another column for Payments Made. Each of those two columns are lookups to tables representing those individual payments and adjustments. I’ve enabled multiple select so that I can see all of of the corresponding payments/adjustments. I’d like to have a final column to do the math and show me my current balance. Original Charge - all adjustments - all payments. I assume I am not able to do this how I have it laid out…but perhaps I can? Otherwise, I need suggestions on how to build this out. Thank you for any help you can give me.
there is indeed a way to do what you want using a single formula
and i outline that formula here
but your video did not show me all the tables you are using
so i am making a guess at the names of the actual amount columns in your Payments and Reduction tables, but i hope you will get the idea
so in your Bill Worksheet, you need to add the following formula to the Current Balance column
Original Charge - Reductions.Reduction Amount.Sum() - Payments Made.Payment Amount.Sum()
i am guessing the name of the Reduction Amount on the Reductions table (its not shown in your video)
when you link like this to multiple rows in another table, what you get is a list of links
so the Payments Made cell contains a LIST of links to the Payments table
so when you enter Payments Made.Payment Amount you get a list of the amounts from those links
so you add the .Sum() function to the end to get Coda to sum them up and give a single amount
i cannot be 100% sure this is exactly what your formula should look like
it would be useful to share the actual document here (but well done on making a useful video)
but get back if it does not work