How to automatically fetch date ranges for each assigned person's worked time when people (may) work different amounts of time in a given project?

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:

  1. Projects
  2. Acts
  3. People
  4. 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).

Welcome to the community, @A.R!

But how can I get that data per individual , so that I can see the date ranges in which John worked in each project ?

You would actually need to go back and restructure how your database tables are setup.

Instead of linking your “Acts” table directly to the “People” table, you need to create a “join table” (aka a “junction table”) that acts as the intersection between Acts & People.

Each record in this join table would be linked to one unique person and one unique act, which is what makes it become the “intersection” between Acts & People.

This “join table” is where all of your reporting would be done from.

See link below for an Airtable support article that gives a brief overview of junction tables.

p.s. Your system sounds like a relatively complex system. If you have a budget for this project and you’d like to hire an expert Airtable developer/consultant to help you develop it, feel free to send me a private message.

2 Likes

Hi @ScottWorld, thank you for replying. I had read that article but it seemed counterintuitive that I needed to separate information about each act from the acts table, and it seemed intuitive that I could fetch the information from wherever I had it originally inputted.

Unfortunately there is no budget for this, so I am the person going hands dirty on this.

I’ll try implementing it immediately and report back here.

Perfect. Just did it and it’s working fine.

I still have a question though: how can I add total time worked in projects disconsidering simultaneous work in more than one project? I.e., if John worked from 3 to 10 Dec. in project X and from 5 to 12 Dec. in project Y, how can I get the total time equivalent to the range from 3 to 12 Dec.?

If you wanted to do it natively within Airtable (without writing a JavaScript or without using automated external tools), probably the easiest way to do it would be to simply filter your view for the specific person & date range that you’re looking for. Then, use the summary bar:

You could also use the summary block, which pulls from a specific view that you have already filtered to show the records that you want. There might be other blocks that would help as well.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.