Can't work out nested relationships that share multiple entries

Bit of background:
I’m attempting to build a staff list that needs to be divide up into Productions, departments, Crew using inline lists.

My issue is that I can get Productions and departments to show nested correctly (using relationships), however I can’t get the Crew to come through on the department level correctly. Currently Crew are showing up in multiple productions as they all belong to departments that share the same name instead of showing up in a single department nested under a production.

ie. What is supposed to happen:

Production 1:
    |____Department (modeling)
            |____Crew (James)
    |____Department (Animation)
            |____Crew (Adam)
Production 2:
    |____Department (modeling)
            |____Crew (Paul)
    |____Department (Animation)
            |____Crew (Chris)

What is happening:
    Production 1:
        |____Department (modeling)
                |____Crew (James)
                |____Crew (Paul)
        |____Department (Animation)
                |____Crew (Adam)
                |____Crew (Chris)
    Production 2:
        |____Department (modeling)
                |____Crew (Paul)
                |____Crew (James)
        |____Department (Animation)
                |____Crew (Chris)
                |____Crew (Adam)

I need to be able to divide the crew into departments that share the same names but belong to different productions.

You must have a full unique condition/relation for it, in this case for James it should be a combination of production - type of department - crew.

I.e a template column of Production 1 - Department modeling - Crew.

Thanks for the feedback ThinhDinh but I’m not super sure I understand.

Currently my sheets are set up like so:
Tab (Staff):
Contains the Crew names, department and production (and other info like phone numbers etc)
Tab (Productions):
Contains the list of productions (only listing each once, not per department /crew)
Tab (Departments):
Contains the list of departments (only listing each department once, not per production / crew)

I have the list of productions from Tab (Productions) being referenced, then a relation for the Tab (Production) --> Tab (Departments) but then pulling in the crew via relation, no matter what I do it adds multiple entries to the departments. I think I understand what you mean about having a unique item for each but haven’t had any luck getting it working…

Example sheet: https://docs.google.com/spreadsheets/d/1g--G9snBz7m2mkFyX5omRwUALK51kEP2F-Jf4UBKQd4/edit#gid=0

I’m sure I’m doing something wrong that is actually super simple. I’m just not seeing it…

Figured it out.

Thanks,
Sam

1 Like