Skip to main content

Hello, I am using airtable as a data source  project management tool for video editing projects. 

I have two tables, the first is a table containing all the data about a project then second table contains information about each draft video created for a project, and this table it linked to the master projects table.

In my app, I have a table block that displays all the projects we are working on with each project populating a row in the table. In a perfect world I would like a column of that table to tell where what was the last “thing” done in the project. In reality that ask may be too big, so instead I would settle for at least knowing the name of the most recent draft video posted to a project.

I need populate a field in the projects table with whatever the most recent row in the drafts table that is related that project.

For example, we have a row that contain info on project “football game”, and there a field called “current draft”. On day one there are no draft videos created yet so the, the field is blank. 3 days later we post “draft 1” and I would like the project data to show “draft 1” and then 3 days later we post “Draft 2” and I would like the project table row to up updated to “draft 2”

So the question I guess is what kind of logic would I need to apply  so that the row gets updated when a new row is added to the drafts table?

I am unsure if this is an automations thing or a field formula thing...

Thanks in advance

Hey ​@RedwolfSports  if you are using an interface you can use a grid view of the field that links to the drafts. 


You will need to build an interface and when selecting the field that links to the drafts, use the aparece to be a list. And you will be able to see it with extra information. To that view you can apply several filters and conditions. 


If you have any doubts, happy to help.

 

Felipe @Aspirity.com

Thank you I am not using an interface. this data is feeding an app I built in softr.

Hm, if you’re comfortable with using the creation date of the draft video as a proxy for ‘latest’, then you could use a lookup field for this:

 

I got this work with some help from Perplexity AI. I think we did something along the lines of what you suggest. I am able to pull in the name of the latest draft into the project record. I was hoping that once that draft record was referenced into the project record we could also then pull in additional fields from the draft record, but the AI was saying that I would need to repeat the entire lookup process for each desired field. 

 

If I recall the process was to first use a rollup to figure which draft is the “’latest” draft in the drafts table, which gave me the date/time of the latest draft in each draft record.  Then I had to find the name of that draft record by doing a formulas looking for which draft matched the “latest” date/time. Then in the projects table I did a rollup using the  arraycompact of the formula field in the previous step.

 

The result is I can now see the name of the latest draft (if one exists) for each project in the table.

Terms & Conditions