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

Hi I have a similar problem - I have a choice dropdown component allowing multiple entries into a OCHA Cluster column in a sheet. The column is updating fine when multiple items are selected via the choice dropdown. The Name column in this sheet is connected to an NGO column in a sheet as a relation (see image attached), however this NGO column is not updating when there are multiple entries in the source OCHA Cluster column. How to solve this?
This is for an NGO emergency coordination resource - help would be much appreciated!

The same happens if I enter multiple items manually in the source column, spearated by commas - perhaps there is a way these have to be entered for the relation to recognise them?

To create a relation from this, you would first need to create a split text column from the joined list, and then use that for the relation.

1 Like

Ah OK I just saw this I didn’t get an email! Let me try this thank you!

Split text worked, thanks so much for your help!

1 Like