Create view with date filtering

[Justin_Eppley]

Does anyone have any advice for how to create a filtered view of data I have that only keeps the oldest date of a “group” of records? I have a table where a product iD can show up multiple times but have different data for it depending on another unique field (a batch of the product). But, I need to have a view that only keeps the oldest batch. I have a date field attached to each batch. Any ideas?

[Vivid-Squid]

Hi @Justin_Eppley ,
Are the batch date fields a Look Up in the Products table?

If so add a roll up to the date field of batches in Products and apply the MIN(values) formula to it. This should only return the oldest date of batches.

[Justin_Eppley]

The date field isn’t a lookup. It’s pulled in from an API as a separate field alongside the batch number field and productid field.

[TheTimeSavingCo]

Hey Justin, I think you’ll need to:

1. Do what Vivid said about adding a rollup to all your fields and doing a MIN()
2. Use that data to check each record’s date to see if it’s the oldest batch
3. Create a view based on step 2

I’ve put something together here that should solve your problem

[Justin_Eppley]

Hi @Adam_TheTimeSavingCo thanks for the workup in Airtable. I feel like I’m woefully inexperienced so just grasping the rollup concept completely took me a bit of time but I’m not sure how to replicate your suggestions and I’m now just squinting too hard… haha

Here is what my data looks like:

Unfortunately, I’m not looking up the data from anywhere here and it seems like the solutions being put forward require a linking field (e.g., for the roll-up). Am I likely missing something blatantly obvious here? In the case of the data I’ve selected to show here, I would want to only retain record 59, as it is the same product ID (different batch) with the oldest date.

Hopefully this helps illustrate a little more of my particular setup or something will click on my end with the solutions put forward. Thanks to you both.

[TheTimeSavingCo]

Hey Justin, yeah, the idea is for the product IDs to be in a table on its own, and to link the batches to the product ID it belongs to.

With reference to the image, your primary field appears to be the Product ID value, so the steps below assume that that’s the case

1. Duplicate the Productid field (now called “productid copy”)
2. Copy the values of the Batchid field and paste it into the original productid field, that is to say, the primary field
3. Delete the batchid field
4. Rename the primary field to “batchid”
5. Rename “productid copy” to “productid”
6. Change the productid column type to “link to record” and select “new table”
7. You should now have a table called “productid”
8. In the “productid” table, create a new field, type “roll up”, and select the “modified date” field from the “batchid” table. Make it’s aggregation formula “min(values). You should see the date of the oldest batch in this field now
9. In the “batchid” table (the original table), create a lookup field and select the field you created in step 8. All the records should show the date of the oldest batch now
10. Make a formula field in “batchid” to check if the value of the field from step 9 is the same as the value of the field “modified date”
11. Use the field from step 10 for your filter

Let me know if you face any issues. You can also just DM me an invite link and I could do this up for you real quick

[Justin_Eppley]

Hey @Adam_TheTimeSavingCo … thanks for spelling this out. I think this won’t be a “tenable” solution for us as we are pulling in this data via API and refreshing it every 15 minutes. I would likely need to put all those steps into a script I think in order to have it all automated. I am working on trying to figure out a workaround. I’ve been able to work with the company who provides the API to get the lab data into a call of its own (when I pull in the rest of the product data it comes with) Headaches of its own I need to figure out now… haha… thanks again.

[TheTimeSavingCo]

Hm, those steps are for base set up and would only need to be done once per base. Are you creating a new base every time you do a data pull? If so, yeah, I guess you’d need to automate the steps