(Solved)How to filter out groups that don't have a row value?

[Xian]

I’m wondering how would I be able to filter out groups that don’t contain a specific row. I’m currently doing an audit on some of our data and this would be very helpful to see which groups are missing rows, so I may verify and record them as such.

Without filtering

My issue is this. Without filtering, I have grouped up products by whichever category currently has them in stock. Now, I want to find out which Category does not have Product 1.

What I’ve done is to Filter by Where Product does not contain Product 1 . And my expected output is to only show Category 2 , which does not have Product 1 in it.

With Filtering

However, doing this kind of filter also returns Category 1, which has both Product 1 and 2. How can I filter it out so it will only return groups that do not have Product 1?

Expected outcome

[Elias_Gomez_Sai]

Xian:

which Category does not have Product 1

You don’t have Categories that contain Products, you have Products with Categories assigned. The filters works against the records data, namely, you can check for properties of products so you could get products with a particular category assigned or not, but not the opposite.

You need to convert Categories into a Table (convert the Field into a Linked Record type), and then do the filter in the Categories table.

[Xian]

You need to convert Categories into a Table (convertir the Field into a Linked Record type), and then do the filter in the Categories table .

Thanks! Now the question would be filtering, how do I go about filtering the records to only show products (or rows) that only belong to Category 1 only? If I do filter by where does not contain Category 1?

I noticed that the column included in the link table is the First Column. Is it possible to have a different column referenced in the other table? – I just realized this is done automatically as it picks up the primary field all the time

Here’s the current setup of the main table.

Here’s the other table that is linked

What I want to do is to reference the 2nd Column (Product Manufacturer) in the Categories table, then filter out which Manufacturers only have a Product 1 or Product 2 only, but never both at the same time. For instance if say I want to know which Manufacturers only have Product 1, it will only show me Nintendo, and not Microsoft and Sony since those have both Product 1 and 2.

I sincerely apologize if this is confusing, but I hope you can help or hint me on this!

[Xian]

I may have figured out how:

What I did first was to reference it to another table, like @Elias_Gomez_Sainz suggested, then duplicated the generated field, and referenced that to a second table, that’s how I ended up with the result I wanted.

Would there be a cleaner way to do this? I notice that if I add an additional row in the original table, it will not update in the third table, since the third table is referencing from a duplicated field in the second table.

[Elias_Gomez_Sai]

Xian:

how do I go about filtering the records to only show products (or rows) that only belong to Category 1 only?

Elias_Gomez_Sainz:

then do the filter in the Categories table .

Xian:

filtering the records to only show products (or rows) that only belong to Category 1 only?

You can set a filter to “Category contains Category 1”, but you also would need to add filters to exclude the rest of categories. Maybe you could do another thing: create a Count field for the categories, and add a Filter to “Categories Count is 1”.

Xian:

If I do filter by where does not contain Category 1?

I don’t understand. You’ll get the Products that have not assigned Category 1, obviously.

Xian:

Is it possible to have a different column referenced in the other table?

No, but you can make the primary field to be a Formula field, so you can show the information you want, adding first a new field for the Name itself.

Xian:

I sincerely apologize if this is confusing

Yes it is :grinning_face_with_smiling_eyes:

Xian:

then duplicated the generated field

What do you mean?

Xian:

Product Manufacturer in the Categories table, then filter out which Manufacturers only have a Product 1 or Product 2 only, but never both at the same time

You want to filter Categories or Manufacturers? It’s the same as in Categories, you need the Manufactures in another Table. Then you could add the Counter field and create the Filters:

• Product is Product 1
• Counter is 1

[Xian]

Elias_Gomez_Sainz:

You can set a filter to “Category contains Category 1”, but you also would need to add filters to exclude the rest of categories. Maybe you could do another thing: create a Count field for the categories, and add a Filter to “Categories Count is 1” .

Yes looks like that would only be the way to go about filtering, but I’ll give the count field a shot if it’s cleaner.

Elias_Gomez_Sainz:

No, but you can make the primary field to be a Formula field , so you can show the information you want, adding first a new field for the Name itself.

Xian:

Is it possible though to have a varying primary field for each view? Or is a separate table the only solution?
I can’t wrap my head around the primary being a formula field, but if I understand it correctly, a formula field where it would reference the field I need/want displayed, and then modified for each time I want to reference another one?

Elias_Gomez_Sainz:

then duplicated the generated field

What do you mean?

I think it’s easier to show you instead of explaining it over a reply, I hope this makes sense

Airtable

Demo Base - Airtable

Explore the "Demo Base" base on Airtable.

[Elias_Gomez_Sai]

Primary Field: