Get and show latest status by person via form & view

I am looking to pseudo-automate a stand-up/status message from about 25-30 people on a team. My plan is to schedule a slack automated message weekly at a certain time that links out to an airtable form. The form is linked to a status collection table that would collect these over time. The person would select their name from a drop down, a general status (choice of 4, linked to single select in table), the projects they’re working on (linked to a projects table), and a few notes fields. Ideally I want to show the latest update from a person.

We’d then have another view of this data, filtered to show the updated entries. Currently I’m doing this by using Date of Entry > last seven days.

However - the question I have - is there a way to instead simple show the latest record by person? For example currently if a person fills this form out 3 times in a week, the view will show all three records. But I only want the last update from a person.

It’s been covered a few times here:

  1. Team Members Table: get a Rollup field to show the MAX(values) of the {Date of Entry} field
  2. Status Messages Table: get a Lookup field of that^ Rollup field.
  3. Status Messages Table: get a Formula field that does IF({Date of Entry} = {Lookup field}, 1, 0)
  4. Filter your view by where the Formula field = 1

Thanks for the response, and sorry if I am missing something.

  1. Team Members Table: get a Rollup field to show the MAX(values) of the {Date of Entry} field

When I go to create a Rollup field to do this, it lets me know I need a linked field first. So I add a linked field. I link Team Members table to Status Message table - however it just creates an empty linked field column. I then create the rollup field, which also results in an empty column.

I’m not sure what I’m doing wrong, but I think I’m misunderstanding or missing a step.

The fact that you didn’t already have the linked field tells me I misunderstood your base structure.

Are you entering the names of people for each status update as a Single Line Text, Single Select, or Collaborator field? Did you already have a table for team members and never link anything together?

I went back and carefully looked at things again. I had made a mistake as these bases evolved and didn’t have things linked properly, so the ensuing lookups didn’t work because the right fields weren’t properly linked.

I now have this working! Thanks!

However - followup question - I have a kanban that shows people’s status entry, based on their form responses. The primary key i have for status table is an autonumber. However I’d like to not have the autonumber on the kanban. Any way to remove the autonumber or have something more meaningful there?

I suppose I could go back to the team member table and then lookup the latest status and have a kanban on people table - but at this point I’m just in a circular confusing loop with lookups between the two tables.

You can use the Autonumber as a regular field and turn your primary field into a formula that’s something like: {Autonumber field} & " :" & {Person name}, which should be more useful to you.

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