Grouping By Criteria Across Multiple Columns

[Emma_Forster]

Hi all, hope you can help.

I’m trying to have a system that will show me head count per month at our different offices. So for example in November 3 of the team are moving to a different office but we have 4 new hires, then 3 more hires the month after across 2 different offices.

I’ve set up a table in a base that has the person as the main column, their current office, when they will change/join and what their new office will be. I then have a column per month that calculates, based on their moving date what office they will be in on any month over the next year.

What I want is to be able count the number of people in each office in each month. Is that possible? Is there a better way of setting this up?

Thanks in advance.

[Jeremy_Oglesby]

I think you’d probably benefit from abstracting out parts of your current table into their own tables.

I can imagine you setting it up like this:
Table 1 - “People”
Table 2 - “Locations”
Table 3 - “People-Location Records”

People has a record for each person with all of the relevant info you need for each person. Also included will be a linked record field, linking to the People-Location Records table, allowing multiple linked records.

Locations has a record for each office you have, with any relevant info you want to record about each office. Also included will be a linked record field, linking to the People-Location Records table, allowing multiple linked records.

People-Location Records has a record for every time a Person moves to/joins a new Location. You can have a field for the “Date” the Person it links to joined (or will join) the Location it links to. Also, you can add a checkbox field called “Archive” to mark a record as no longer current.

Next, you can use FILTERS in the People-Location Records table to create views showing only current records (show only records where “Archive” is unchecked AND where “Date” is on or before TODAY). You can then GROUP those records by Location to get a count of how many people are at each location currently in the summary bar.

This also allows you to create “projected” location changes for a person by creating a Person-Location Record with a date set in the future. I would add another checkbox you can use for creating a “Projected Head-Count” view – call it “Will Move Soon”, and check it on a person’s current Person-Location Record when you create a future, “projected” location change for them. If you use filters cleverly, you can create a view now that shows current records where “Will Move Soon” is not checked, AND show records where the “Date” is after TODAY.
Sometimes the filters can be a little difficult for doing complex things like that because you can only use “AND” conditions or “OR” conditions, but not both – you can’t mix them. In cases where you run into that limitation, the best option is to create a Formula field in your table that does uses conditional statements to do the filtering for you and output some other value that you can filter on.

I realized half-way through writing this that, if you are new to Airtable, this may be a bit overwhelming. I’m heading out for my lunch break now, but if you can hold on a bit, when I get back I may be able to throw together an example base for you that you can copy and use or just mimic in your own base.

EDIT
Also, take a look at the general advice I gave in this recent post:

Tracking history of shipping locations? Ask the community

Hi, I am new to Airtable and I am trying to migrate my studio’s crate inventory (crates contain artworks that we ship to museums and then back to us). We used an excel sheet unto which we had a ‘‘history’’ box. That is where we’d manually list all the previous locations with the years of where a specific crate had been shipped before returning to us. Is there a clever/simple way to keep track of such info in Airtable? Like a tab that keeps track of each crate’s previous locations when we update…

Her situation is a little bit similar to yours in the need to expand out to multiple tables and create entity relationships between the tables.

[Jeremy_Oglesby]

Here you go, Emma. This is a simple mockup base demonstrating what I described above. Once you join as a collaborator, feel free to copy the base to your own workspace if you want to dig in further.

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

Notice the two Views I made in the “People-Location Records” table - “CURRENT” and “PROJECTED”.
I used a field called “Status” to determine the status of a record as either:

• “Old”
• “Current”
• “Moving Soon”
• or “Future”

The CURRENT view filters by showing only records with status “Current” or “Moving Soon” (“Moving Soon” means the record is current, but there are plans for the person to move in the future).

The PROJECTED view filters by showing only records with status “Current” or “Future”.

You’ll notice that “Jim T. Jones” has an “Old” record, marked as Archive = TRUE() (checkbox) - he used to be at “HQ1”, but this record shows up in neither of the views above, because it is “Old”. But he moved on 8/9/2018 to “HQ2”, so that is his current location, and it shows up in the CURRENT view – however, there are plans for him to go back to “HQ1” on 1/8/2018, so I marked his current location record as Will Move = TRUE(), and created a record for his projected location change. The PROJECTED view reflects that in the future, he will be back at “HQ1”, and that view is sorted to show the most future dated records at the top, so those “Future” records are always at the top of the list for a location.

Also, you can see in the Summary bar for each Location a “Count” of how many records there are under that group (each one representing a person at that location).

I believe this should get you at least most of the way towards what you are wanting. You may want to see if you can get more granular with the “Record Status” to display “Will Move < 1 Mo”, “Will Move 1-2 Mo”, “Will Move 2-3 Mo”, etc so that you can make Projection views to show more granular periods of time, where the one I created currently encompasses ALL future moves in one view.

[Emma_Forster]

This is so helpful! Thanks @Jeremy_Oglesby I’ll mess around and see what I can achieve