Base design for chemical formulation

[Scott_Mullin]

Hi folks,

I'm looking for ideas on base design(s) for chemical formulations. I've taken one stab at this where I hardcoded some columns, but that fell apart pretty fast. I'm hoping some Airtable creatives have ideas. 

Here's the general problem:

• An ingredient inventory has some number of ingredients A, B, C, D, E, F... etc and can have an arbitrarily large number of entries
• A formulation has some number of ingredients (an arbitrary subset of the ingredient inventory, so maybe B, C, F as a random example) and a defined amount of each included ingredient (xB, xC, xF). That amount could be a fraction or a mass in most cases

Simple case: how do I deal with a formulation having arbitrary composition of ingredients?

More complex case: How do I deal with the above, but ingredient inventory can also include formulations (so it's getting recursive)?

Any ideas are most appreciated!

Regards,
Scott

[kuovonne · Accepted solution]

I would use at least three tables.

An Ingredients table holds ingredients. It has a field for the ingredient name and whatever ingredient-level info you want such as vendor. It will link to the formulation-ingredients table.

A formulations table that holds the name of a formulation, but not the actual ingredients. It might have some recipe specific instructions in a long text field. It will link to the formulation-ingredients table.

A formulation-ingredients table. This has two linked record fields—one to the formulations table and another to the ingredients table. It also has another field to represent amount of the ingredient. You will have one record in this table for each ingredient in each formulation. Group this field by the formulation linked record field to see all the ingredients in each formulation.

Some notes:

I you say your ingredient amounts could be either a mass or a fraction. It is easier if you always enter the amounts by mass. Then calculate the fraction using a system of back-and-forth rollups.

You say that some formulations can themselves be ingredients in other formulations. This gets a bit trickier. There are multiple ways of dealing with this depending on what your formulations look like, the level of nesting, and how much overlap there is. I recommend you experiment with the basic three table setup I described above before venturing into a nested/recursive setup in Airtable.

One option is to add an additional link to the formulations table in the formulation-ingredients table so that when picking an ingredient, you pick from either the ingredients linked record field or the formulation-as-ingredient linked record field. Note that if you need to do recursive calculations for amounts, Airtable will not do that well without a script.

Another option is to merge your ingredients table and formulations table into a single table.

Another option is to have additional tables where you combine different formulations into different products. This is a bit of a unique case and does not allow mixing of ingredients and formulations at the same level.

[Scott_Mullin]

Thanks Kuovonne! I'll give that a try and let you know how it goes. I appreciate the advice.

[Scott_Mullin]

Kuovonne,

That worked well for the case without recursion. Thank you again! I'll have to think further on the recursive case to see if it's worth messing with. That could get pretty wild.

Regards,

Scott