identify values in one array not in another array for a record

[mark.lovell]

Hi,

I may need to refactor my model a little, but I have several tables modelling:

customer, product, link to “ingredients” used, link to “ingredients” permitted

So the view looks something like this, where the + are the multiple values returned by the table link:

ABC Co, Product A, Milk+Cheese, Milk+Cheese+Flour+Water
XYZ Co, Product A, Milk+Spinach, Milk+Cheese+Flour+Water

The first record is fine, Milk and Cheese are both permitted ingredients in Product A

The second record is not fine, Spinach is not a permitted ingredient in Product A.

I want to build a filter/view whatever that identifies the ingredient violations.

I think I’m on the right track with a FORMULA column added to the view, perhaps using JOIN and/or LOOKUPs or ISEMPTY

In SQL it would be a doddle as it just be looking for any ingredients used that were not in the set of ingredients permitted, but I need to get my head around how baserow flattens these joins into “arrays”. is that right?

Kind regards,
Mark

[ailead]

Hi @mark.lovell

As far as I know, there isn’t a direct way to check whether an ingredient is allowed or not. However, you can create a workaround by using an additional table, which we can call ProductComponents . It might look something like this:

From there, you can use a lookup field to pull the allowed ingredients from the CustomerProducts table. This allows you to check if an ingredient is permitted or not:

The CustomerProducts table might look something like this:

Finally, you can add a RollUp field that references the isProductComponentAllowed? field in the ProductComponents table to automate the validation of allowed ingredients:

Let me know if you have any questions!