Saving a records history as an attribute?

[Colin_Winhall]

Hi,

I am creating a social media monitoring database for esports players for how many followers they have between several platforms. I have it up and running pretty good but I now want to keep a history of how those numbers change over time instead of just always updating to the latest follower count.

I’m using integromat to update the numbers via the API and in the record’s activity these changes can be seen. However, I would like to save these changes in the record forever, obviously by date it changed. So that I may make queries about these changes, create graphs and show the delta of the change each time (up 2% etc).

Is this possible?

[Bill_French]

Colin_Winhall:

I would like to save these changes in the record forever

This is a common requirement for creating sustained analytics, and there are likely many ways to approach this, perhaps even with Integromat. @cor is likely imagining how to do this at this very moment.

In any case, jamming historical states of data points into the existing current state record are possible by using a JSON (or array) trick - simply use a long text field to capture and list states as they change. Indeed, it’s ugly but it works really well depending on the approach you require for rendering such data points.

The approach is simple - with each update to a record, capture the current value and append it to the list of previous historical values in the long text field. If you write this data into the text field with a degree of structure, it will be readable by users and also parseable when the time comes to expose it in a dashboard or something.

Imperfect, yes - but functional.

Sidebar - A long time ago I advocated for the additional feature to allow for base, table, and even record-level “static” values. These would be like addendum name-value pairs that could be stored for caching purposes.

[cor]

Hehehehe @Bill.French you caught me in the act and know me (or at least my type) quite well

The best way I can think of is to poll the numbers and then record each value as a record in a ‘transaction’ table linked to a player record. In the player table, you can display the latest value while you then have access to the changing values in the transaction table. You can save on operations by using airtable’s bulk post api.

Depending on how many players you are monitoring and the frequency of your poll Airtable might get expensive quickly. You might consider storing the poll data in a gsheet rather and only posting the most recent value to airtable.

Hope that helps a little.

@Bill.French would be able to help you to script this in gsheet when the volumes go up :slightly_smiling_face:

[Colin_Winhall]

Thank you @Bill.French & @cor for your replies. It gives me confidence there are solutions to this although I don’t fully understand the requirements of your solutions :slightly_smiling_face:

Are there any articles/videos or examples demonstrating the solutions you have advised that I could follow in order to achieve this result?

Thanks so much again for your help.

[Bill_French]

Colin_Winhall:

Are there any articles/videos or examples demonstrating the solutions you have advised that I could follow in order to achieve this result?

No, I don’t think you’ll find these concepts pervasively documented because each approach is fringe in nature and largely, when these requirements emerge, they are generally implemented for specific clients in consulting engagements - i.e., typically requires code and API for the most part.

[Bill_French]

cor:

you caught me in the act and know me (or at least my type) quite well

I’m a visionary; not all of my visions are about technology. :winking_face:

cor:

access to the changing values in the transaction table

Indeed, this is the less hacky way to do this, although, I recently delivered a script block solution to a client that does exactly this with a long text field and the client loves it despite the need to run the block every night.