Relation to multiple matching records within two categories within a list

I cant find any topics covering this, but I may not be wrapping my head around it well enough to search properly.

I have a list of emails directed at companies being created, which are appended to a growing list once created. There can be an unlimited number of emails made to each company, but each is classified into either Category A or Catgeory B. I want to be able to further classify these emails into three groups relating to the company theyre being sent to: Category A email in list but no Category B, Category B email but no Category A email, and both Category A and B emails in list.

Each row would obviously meet either the criteria for being either a Category A or B email, depending on what type it was itself, but im having trouble flagging whether the company that each row relates to also has a row in the table for the other type of email - it doesnt matter if there are multiple, just that there is at least one. Can someone help direct me as to what the best way is of achieving this?

Thus far, all ive been able to do is a single relation of the company name field it itself, with a lookup to the Category type. This works when there is only one of each type of email, or when there are multiple but the first two are both types, but when the first two are the same, it just picks only those two up but doesnt identify there is any others in the list of the other category.

Hope this makes sense.

It’s a bit difficult to visualise from your description. Would you mind adding a screen shot that shows how these tables/columns appear in the data editor?

1 Like