Apologies for the odd topic title, but I really couldn’t think of an easy way of explaining it!
Basically, we have a database of volunteers for an event, and there is a sort of management structure for the team. From the bottom up, it looks like this:
Volunteers are assigned to several different teams, and each team has a captain. Each assistant manager looks after a group of teams, and then the manager looks after everything.
The volunteer information is all stored in one table with a column to specify the team they’re in, and another column that sets their role (volunteer, captain, assistant manager, etc.).
What I’m trying to work out is what formula I’d need for a “Reports to” column that looks up who each person reports to in that structure. So if they’re a volunteer, who is the captain for the team they’re in, if they’re a captain who is the assistant manager in the same team as them, and so on.
Do you already have a Grist document set up with real or sample data in it that you could share?
Your Team column is presumably a choice or reference column type? This matters because it will determine how you need retrieve the value in this column.
Your Reports To column formula would then look for the corresponding volunteer with
$Role == "captain" and $Team == "Team Name"
Building on that, since you’re only looking for one matching record, you can use the LookupOne function.
That formula will work to look up Captains for volunteers assigned to a team, but what about Assistant Managers and Managers…you’ll need some sort of switch function or a series of if…elif statements.
This is not necessarily a fully formed example but should give you a good starting point.
I mentioned that if the Team column was a reference column, it would have to be iterated as a list within a list instead of a singular value. In that case, you would have to basically abandon the LookupOne function and instead use python list comprehension to iterate through all the records to find the matching “Reports To” individual. Unless your Teams column is actually a reference column type, I’m not going to take the time to outline that. You can search this forum for “list comprehension” and get an idea of what I’m talking about.
Ooh, this may be a good candidate for a spreadsheet makeover. It’s a very new video series you can see here: https://www.youtube.com/@SpreadsheetMakeover. Episode 3 should be coming this week!
I’ll message you, David, to see if there’s a fit and interest.
You’re correct in that the Teams column is a reference type. I did actually add a few columns into that table that lookup the captain, manager and assistant manager for those teams using formulas along the lines of Volunteers_Table.lookupRecords(Team=$id,Responsibility="Captain").Full_name
But I appreciate it’s a bit difficult working out the best way to help without seeing the structure of the whole thing!
Let me work on either an empty version of the document or one with some dummy data that I can share - or maybe try and explain it all a little better!
In the meantime I’ll take a look at the “list comprehension” you mentioned.