Hello everyone. I am trying to build a somewhat complex base. I thought I had figured it all out, but I can’t get the last part together, so I would like some help please.
First, I have (1) projects. Projects are created by (2) acts, which assign (3) people to the (4) group working on each of them. But an act can also do other things (extending deadlines, changing its type, etc.), which are all covered already maybe except for one that seems tricky for what I still need to solve: an act can replace people in the group or simply add more people to it.
So I have the following tables:
- Projects
- Acts
- People
- Groups
The (2) acts table has the following columns:
A) [Single line text] Act ID
B) [Date] Act’s date
C) [Multiselect] Purpose/object [one or more of: “project creation”, “deadline extension”, “group member replacement”, “group member addition”, “final report/project closure”]
D) [Link to one or many] Projects [because a projects sometimes are “connected” so a group will work on more than one at a time]
E) [Link to one] Group
F) [Link to many] People assigned/added [initially or in the middle]
G) [Link to many] People removed/cleared [in the middle or at the end]
So:
- a “project creation” act will have the initial members in the F field (say “John”, “Jack” and “Jill”, pulled from the (3) people table);
- a “deadline extension” act will have empty F and G fields;
- a “group member replacement” act will have one or more people in both F and G fields (say “John” and “Jack” are sick and “Joe” and “Jim” stepped in);
- a “group member addition” act will have one or more people in the F field (say “Paul” and “Peter” came to help);
- and, finally, a “final report/project closure” act will effectively clear everyone from keep working in the project, so everyone still working in the project will go into the G field (in this case, “Jill”, “Joe”, “Jim”, “Paul” and “Peter”).
Ok. So far so good. I have a table which effectively contains the starting and ending dates of everyone’s involvement in each project. But how can I get that data per individual, so that I can see the date ranges in which John worked in each project?
After that, I would also like to add up all his worktime, both considering and not-considering simultaneous work in more than one project (tips on how to achieve both are welcome!), but first I would like to get an automatic result of project-time-per-person based on the data in table 3 above, which should be enough if I am not mistaken.
BTW, one of the links in your “Getting started with base design / FAQs” post (Getting started with base design / FAQs) is not properly set: where it says “This 7-minute screencast goes over how to structure your base to represent more complex data (the specific example discussed is tracking building inspections by various staff members at various properties, though the base set-up could be applied to any number of use-cases)”, but the linked screencast (https://share.getcloudapp.com/04uK4lx5) is only 3 minutes long and it’s about something else (a lot less complex that announced).
