I have a multitude of parts and revisions that have several Materials. What I am wanting to do is find the parts and revision s that do not have ink in them. But I keep running into just filtering out ink by itself and not everything else as well.
Run a sub query to get a list of parts that do have ink in them, then compare that list to a list of all of your parts in the next level up. Use a left outer join to include all parts from the part master to your subquery and you will get a list of all parts and some will have the ink and some will be blank in that field. The ones that have a blank are the ones without ink. I know there examples of creating a list of negatives using this technique. Search around a bit, and if you still have questions let me know and I can walk you through the query. I’ll search a bit and see if I can find a link to an example.
sub query (I’m filtering by part number starting with 721, stainless steel for us)
Sub display fields
Top level tied by part number,left join (I also filtered my part master list a bit as we have over 10k part numbers)
Top level display fields
Here are the results, the list with nulls in the sub fields are parts with no 721 numbers in them.
This is a basic technique for finding a negative that can be used a lot of ways. (it would be nice if we could get a sticky for this technique, because the same question is asked quite a bit.)
2 Likes