Re: 1 million+ Excel/CSV files
Hi Jovo, and welcome to the community!
I appreciate fast terse answers; no BS’ing around, quick, to the point, move the ball downfield! @ScottWorld is really the right guy to have inside the two-minute warning.
However, there are cases were it may be worthwhile to know more about the nature of the data before dismissing Airtable based on a record count alone. Sometimes, a time-out should be called to strategize the next play. Lil’ story…
I have a client with about 20,000 Airtable client records. Each of those clients produce from 100 to 1000 transactions a year with an average of about 500 transactions per client. That’s an equivalent of about 10 million transactions a year amongst the 20,000 clients and all of it nicely packed into a single Airtable table.
To achieve this I use a technique that is not often used and to be clear, it’s not a bed of roses. However, the use case works phenomenally well for this particular client and the way that it utilizes transaction data - in this case, social media activities.
This is how the design works:
The trick is storing transactions not as individual records in a linked (relational) table; rather, store them as JSON objects in a long text field. This is, of course, a sanctioned approach as evidenced by this new Airtable feature. To pull this off, a button (in the client record) fires a script block that unpacks and renders the JSON object containing all of the transactions related to the current client record in a script block grid control. Alternatively, users are able to copy the transactions into a table reserved for this and makes their viewing more pleasurable.
There are many nuances and opportunities associated with managing vast amounts of data like this and it clearly requires some JSON object considerations.
The takeaway is that it sometimes pays to understand the data, the use case, and the user requirements before setting any implementation strategy, let alone buying a specific technology like FileMaker Pro.
