Obtain Airtable line number based on an alphabetical field and a creation date field

[Frederic (Frédéric)]

Hello everyone

I would like to obtain the record number of a row for a given month within a column (month repeated multiple times) relative to the last creation date.

In my example I want to retrieve the last December (from the MONTH column) created in relation to the CREATE_LE column.

Thank you all.

[RaYzZz (Gianluca Franco)]

Hi dear,

I couldn’t find documentation about this function (query) in Airtable, except for the option to create a pre-calculated formula, but I don’t think that’s what you’re looking for.
One idea that comes to mind would be to download the entire table or the two columns and calculate the value you want inside the app, but I don’t recommend it because it’s really inefficient.
You could switch to a database that allows you to filter data more easily.

[Frederic (Frédéric)]

I’m going to take a different approach. How do I get the row number of the last “December” in the MONTH column? This last December is supposed to be the most recently created one.

Who can help me ?

Thank you all.

[Still-learning]

This is seems to be complex request. Anyhow in my earlier post i have suggested to use fileterByFormula GET request and we got succeeded. Pls look unto this thread, make GET request url and run in your app using web component. If it succeed you will get response under when Web got text in json format and convert into list then using dictionary blocks do the further formalities

20 different ways to GET and filter data from Airtable

by the way if you prefer google sheet as your database then it be very easy to filter the data based on your requirements using query method, you can save any number of records, retrieve at any number of times, no limitation but this airtable will have all such limitations.

[Ibrahim_Jamar]

There’s alot of way to archive this functionality, but my first question is, is the data stored manually? Or it’s uploaded from the app? Any answer from these questions still have a way to archive this. Since you’re storing data with exactly date it’s quite possible. Though I would recommend using gsheets than airtable.

[Ibrahim_Jamar]

@RaYzZz

like 10,000 or 50,000, it becomes a problem.

He needs to shifts the data from airtable, about the data entry can it reaches 50k in free plan even paid it will still heavy also I think that’s not a one year data. Which will course December comes twice.

Maybe you can share the solution. Since am not familiar with airtable I won’t test anything but in gsheets I already tested and it’s possible but not with 50k data​

[RaYzZz (Gianluca Franco)]

I studied a bit and I think I’ve found a solution with filterByFormula.

https://api.airtable.com/v0/BASE_ID/TABLE_NAME?filterByFormula=MOIS=“MOIS_NAME”&sort[0][field]=CREE_LE&sort[0][direction]=desc&maxRecords=1

A special thanks to @Still-learning who made me realize that perhaps I hadn’t read carefully, at point 18 there’s exactly what you need.
18. Get data sorted in ascendent or descendent order

[Frederic (Frédéric)]

Hello,

The data is saved in Airtable with the application and I retrieve data from Airtable with the application as well.

[Frederic (Frédéric)]

Thank you, I’ll look into reproducing all of that.

[RaYzZz (Gianluca Franco)]

I’ll leave you the mai procedure blocks separately so you can import them directly into Kodular without having to copy block by block.

[Still-learning]

Thanks, yeah we tried all those few years ago and even i have shared a post regarding how do have to use this technique in kodular. I felt happy to use the filterByFormula which is easy to do our works with airtable. May be thats why i have 1500+ solution marks

[Frederic (Frédéric)]

Another solution found : [FREE]AirtablePro Extension - For airtable lovers

thanks @Still-learning