Filter last 10 records by date

#1

Can’t seem to find anyone with this problem and been cracking my skull on it…

I use airtable to manage the factory and I have 6 welding tables which I need to divide work out fairly. I would like to be able to “group” by the last 10 jobs that I gave each of the welders in order to see how to divide the work fairly (because some work is more difficult which affects the bonus that they get).

The problem is they don’t all work at the same pace, so filtering by date doesn’t give me what I would like. I would like to display the last 10 records before now, grouped by employee.

Any clever ideas? We have the pro subscription so a solution through blocks or pivot table is also ok.

Thanks!
Michael

0 Likes

#2

This method requires one extra step whenever you create a new “Job” record, but it should get you close to what you want. I am assuming you create all of your jobs in one table, which I am referring to as “Jobs”.

  1. Create a new table called “Job Stats” or something like that
  2. In your “Jobs” table, create a “Link to another record” field that links to the “Job Stats” table
  3. In your “Job Stats” table, create 1 record (1 row) - this is the only row your "Job Stats table will ever have (I usually name the record with an emoji like 📊 or something in the name field so that when I link records to it, I can easily see in the linked table when a record is or is not linked)
  4. Link every existing job to that one record in the “Job Stats” table - this can be done quickly by first linking the top record, copying the cell with the linked record, selecting that field for all the remaining records, and then pasting
  5. From now on, whenever you create a new job record, link it to the same “Job Stats” record - this can be done very quickly by selecting that linked record field, and hitting enter twice - hitting enter the first time brings up the selection dialog to select which record to link to; since you will only ever have one record in there, that record will always be selected by default, so hitting enter the second time selects it and creates the link
  6. In your “Job Stats” table, create a field of type “Count” that counts the number of linked jobs
  7. In your “Jobs” table, create a field of type “Lookup” that looks up this count field from the “Job Stats” table
  8. In your “Jobs” table, create a field of type “Autonumber” - the autonumber field will assign the number 1 to the first record you ever created there, and then increment for each new record added
  9. In your “Jobs” table, create a field of type “Formula” with the following formula:
IF({Autonumber Field} + 10 > {Count Field}, "✅")
  1. You can now filter your “Jobs” table like this (where “Last10” is the formula field described above), and then group those 10 jobs by employee:
    image

This only finds the last 10 records by order of when the records were created - it does not take into account any “Dates” you enter in to the records – so it may end up being of limited usefulness if you are often adding records well beyond the dates of the jobs currently being processed – but it’s at least a starting point that you may be able to tweak to get closer to what you need to see.

3 Likes

#3

brilliant, never thought of that.

0 Likes

#4

also, to expand on Jeremy’s solution:

When I need to ensure every record in a table is linked to a record in an “auxiliary” table as is the case here, I create a view where records are grouped by that Link field. This way whenever I add a record in this view it will automatically link to the aux table without much thought from me.

2 Likes