product ingredient analysis

[martincilio]

Hi,
I am new to Airtable, but hopefully it makes sense what I am trying to achieve. 
I am creating a product database that will include product name and it´s ingredients. I want to be able to tell if a product is vegan friendly, not vegan, or unknown based on the ingredient status.
For that, I created a separate table that stores ingredient names as a key and their status (vegan friendly, not vegan, or unknown). Almost each ingredients have synonyms that have to be included, but the synonyms should be treated the same way as the main ingredient (as for the status). So I created another table that stores synonym name as a key and their respective ingredient as a foreign key. 
Back at the Product table, I want to be able to add product ingredients and for Airtable to check the Ingredient list and Synonym list and assign the status for the product (non vegan - if there is at least one non vegan ingredient, vegan if there are no non vegan or unknown ingredients and unknow - if there is at least one unknown ingredients. Ideally, I would like to pull out of the product ingredient list the ingredients by their status. I think I have something missing here. So far I tried: 

1) make a linked record within the Product table that links to Ingredient table. This way I managed to import ingredient list as a string of text separated with comma directly to the record. As the result, the ingredients that existed in the Ingredients table got linked and the others were created as new entries in Ingredients table, although they were synonyms that already existed. 
2) I tried to use a formula in the Ingredient table that uses the ingredient name and puts all the synonyms after the ingredient within [ ]. This way, when I try to import the product ingredients, it can look up by the ingredient synonym as well (as it is included in the name within the brackets). The downside is that I cannot import ingredient string, I would need to add each ingredient separately, which is not viable. 

I am sure I am missing something on the way. Can you please point me in the right direction? 

Thanks!

[augmented]

My initial response would be to uncomplicate a bit. First, use one table with ingredients and synonyms with their corresponding status. In the products table, link to the ingredients and create a lookup through ingredients to status. Now, you will have an array of statuses (e.g. VEGAN, NON-VEGAN, VEGAN, UNKNOWN). Create a formula field with this rough outline...

IF(FIND('NON',{lookup field}&''),'NOT VEGAN', IF(FIND('UNKNOWN',{lookup field}&''),'UNKNOWN', 'VEGAN'))

The {lookup field}&'' turns the lookup array into a string.  Make sense?

[martincilio]

Hi augmented,
Thank you for your advice. I did manage to follow the steps and I got this: 

The names are in Spanish but I think they are easy to understand. I input the list of ingredients that are linked to the ingredient table and assigned the status field in 3 colors. Later I applied your formula that gave a value. 

In my example, I only used the ingredients I have before specified in the ingredient table. The "issue" is that almost every ingredients have a synonym so I created the synonym table and assigned it to the ingredients. It looks like this: 

  

Each synonym (key) is assigned to a ingredient name and the status is a look up field from the ingredient table. 

And my ingredient table then looks like this:

So back to the Product table, if a product contains ingredients that is not in the ingredient table (as the main ingredient), it will create a new record with that name. Even though I have a synonym name created already. In my example, imagine that I would add E441 (that is a synonym of aceite de colza), it would add a new record. I wanted to avoid doing that because later if I need to update the value of one ingredient, I would need to update it for every synonym separately.  Am I doing something wrong here? Thanks!  

[augmented]

I don't think that you are doing anything wrong. What if you add your primary ingredient to the synonyms table as well? Then your product table is linked to synonyms, instead of ingredients. But, you maintain the status of the primary ingredient in the ingredients table.  Does that work?

[martincilio]

Do you mean something like that: 

This is the synonyms table. I added the ingredient "aceite de ballena" and I needed to asign it to the same ingredient so the status "no apto" would appear. 

If I understood you correctly, I would then need need to populate the synonyms table with all ingredient names that already sit in the Ingredient table, right? 

To understand how it works: when I make a Linked record like for Product Ingredient record, Airtable only checks the primary key (the first column)? There is no way of making it check the first two column (ingredient name + synonyms) or two primary keys (Ingredient name from ingredient table and Synonym name from Synonym table)? If there is no way around that, I assume that I need to have all ingredients and synonyms in the same table and same column.  Thanks!

[augmented · Accepted solution]

If I understood you correctly, I would then need need to populate the synonyms table with all ingredient names that already sit in the Ingredient table, right? 

Yes, this is what I was saying. It's not perfect, but you don't have to give up your ease of updating status because you keep an ingredient list with links to the synonyms table.  So, three tables: 1) products with links to synonyms table, 2) ingredients table (maintains status for all ingredients and links to ingredient and synonyms in 3) synonyms table containing ingredients and synonyms with lookup field to linked ingredient record.

So, if you want to change the status of an ingredient and all its synonyms, you go to the ingredients table and make the change once.

I don't really know anyway else.

[martincilio]

Thanks for your advise, really appreciate it. I think I can start with this and see how far can I go.