Summing Values from Multiple Tables based on Current Row Criteria

Hello all!

Here is the link to my editable coda doc: IV Formulas Template
This is a document to help my medical practice predict how many vials of medications we will need the following week so we can order enough.

Here’s the problem I am trying to solve:
In the Volume Checker Page, I am trying to sum up the total number of units (collective term for vials or bags) across many different IV formulations in the Fluids and Ingredients to Order Table. I have tried three different formula variations and I can’t seem to figure out the logic.

In layman’s logic, I’m trying to say this:
For this rows Ingredient/Solution, Sum the Number of Units Required across Volume Check IV #1-#6.

Thank you for any guidance/assistance!

Hi Drew !
if your table Volume Check IV#i (i=1…6) have to be independant (and are frozen in number), the easiest way would be to simply add it like this, creating a “total amount” column in your “Fluids and Ingredient to order” with

with i

[Volume Check IV #i].Filter(Ingredient.ToText()=thisRow.[Ingredient/Solution]).[Number of Units Required].Sum()

sum of i

[Volume Check IV #1].Filter(Ingredient.ToText()=thisRow.[Ingredient/Solution]).[Number of Units Required].Sum()+[Volume Check IV #2].Filter(Ingredient.ToText()=thisRow.[Ingredient/Solution]).[Number of Units Required].Sum()+[Volume Check IV #3].Filter(Ingredient.ToText()=thisRow.[Ingredient/Solution]).[Number of Units Required].Sum()+[Volume Check IV #4].Filter(Ingredient.ToText()=thisRow.[Ingredient/Solution]).[Number of Units Required].Sum()+[Volume Check IV #5].Filter(Ingredient.ToText()=thisRow.[Ingredient/Solution]).[Number of Units Required].Sum()+[Volume Check IV #6].Filter(Ingredient.ToText()=thisRow.[Ingredient/Solution]).[Number of Units Required].Sum()

To understand : why do you need to have several Volume Check IV#i different table ?

THANK YOU!

That worked splendidly.

To answer your curiosity (and maybe this will help find a more elegant solution), I created multiple Volume Check IV#i tables because I wasn’t sure how else to create the data that could be summed. The 6 variables at the top of the doc IV #i select list and Number of IV’s: will change each week, and I wasn’t sure how else to get the data that is currently being put into the Volume Check IV#i tables. Any ideas on this?

Hi Drew, be my guest !

Hey hey, using buttons you can wayyy optimize. In your case, you can create buttons that would run through your global library and fill a very unique Volume Check IV with all needed ingredients according to your IV Formula

Would you be interested in something like this, that allow you to have any number you want greater than 6 :hugs:?

Untitled

Please reach out in DM if you want some help building and improving your doc deeper ! it will be with great pleasure

1 Like