Formula Help Needed For Inventory

I have a clinic that uses forms to input medication received from reps and it adds it to a table that the pharmacist sees to keep track of the medications. Then when the medications are dispensed, they log that information into another form to track the medications given (which again is only visible by the pharmacist). What I am trying to do is get a formula which will take the number given to each patient from the total number of on hand for that medication.

The solution suggested by Coda support won’t work because it adds a column to the top table but the goal is to get them to not have to manually track inventory numbers.

Thanks for any help you can provide.


Hi @Terry_Stagg
Could you please allow edit access to your document, or at least let us copying it to check in detail.


I changed the permissions so it should work now. Thanks

OK @Terry_Stagg . If I well understand youir point (please tell me if I’m wrong), you wanna manage the stock of pill (substract the Sum of quantity given to Patient written in Patient Table from the initial quantity in Drug Table)

  • First, I’d suggest to make your medication name in patient table as a lookup to your drug table

  • Then, enter this formula in Inventory in Drug Table, that will make the some of quantity given in Patient table and make the substract

Does that fit your needs ?



1 Like

That worked perfectly!!! Thank you so much for your help!

Happy to read that ! Be welcome and enjoy !