I’m trying to use a single Base to store all my business workflow information.
I have the following tables:
Employees
Clients
Jobs
I now want to track timesheets which link to these three tables. That is, I want my employees to enter timesheet information into a new Timesheets table where:
They select their name from a linked field tied to Employees table
They select the relevant client from a linked field tied to the Clients table
They select the relevant job from a linked field tied to the Jobs table
My issue is that I have 25 employees who are each recording 12+ timesheet entries per day (they work on many jobs for many clients each day).
As a result, I quickly approach the record limit for the Pro plan due to the size of the timesheet ledger.
I’m puzzled by how to proceed here. Can anyone think of a way to set this up where I don’t run into record limits for at least a few years?
My thoughts were to give each employee their own base for timesheets, but then I won’t be able to link up the existing workflow tables to their personal base timesheets (or could I?)
Or perhaps I could summarise the timesheets from a ledger format into individual records which shows the employee and the total time spent by job? Im not sure how to do this though (or if I even should).
I have no issues with data being visible across employees.
Unfortunately, Airtable is not a system that can be used for storing large amounts of data in one place. It was designed for smaller teams with smaller amounts of data requirements.
However, the good news for you is that you probably don’t need to search through old archival timesheet data too often, so you can just continually archive your old data elsewhere.
With 25 people entering 12 records a day, that gives you between 5-8 months worth of data, depending on how many days you enter data during the month. Possibly fewer months than that, depending on how many clients & projects you have, and depending on how many employees are entering more than 12 records per day.
You could pay Airtable $15,000 per year for their Enterprise Plan for 25 users, but that will only double the amount of records you get in one table, so that will only double the amount of time you have until doomsday.
So if you decide to stick with Airtable, your only real option is to continually archive the old data to make way for the new data.
Here are some ideas on what you could do:
Manually export the old data to a CSV file every quarter and then delete that old quarterly data from Airtable afterwards. You will end up with many CSV files to search through.
Manually duplicate the base quarterly as a new “archival base” for that quarter that you can refer back to later for that quarter, and then in your “active base”, delete all the records that you archived. You will end up with many archival bases to search through,
Continually append old records to an ongoing Google Sheets spreadsheet, and delete the records from Airtable as they are appended to the spreadsheet. This could be automated using scripting or Make.com, and could result in just one spreadsheet that you would need to search through later. Alternatively, instead of one never-ending spreadsheet, you could create several different spreadsheets to represent the quarter, the year, or whatever timeframe you want. All of this can be automated, so you don’t even need to manually get involved in it.
If you need help setting up #3 and you have a budget to hire an Airtable Consultant, please feel free to contact me through my website:
How long does a job for a client stay open? How many timesheet records do you typically have per job? Do you have a unique record for the job+client+employee combo?
When a job closes, you could store the timesheet information in a long text field and delete the actual timesheet records. Use a formula field to summarize the timesheet record. Use a rollup field to combine the info for all the timesheets for that job+client. When the job is closed, copy the contents of the rollup field into the long text field and delete the timesheet records. Depending on where you want the rolled up data, you might need to make copies into several records, and possibly append data to long text fields and/or number fields depending on what level you rollup the data.
You can do these steps manually or with scripting. It can also be done mostly with non-scripting automations, except for deleting the timesheet records. Back when I was accepting clients, I wrote scripts that summarized (and expanded) record data this way with a button click.
you can extend in width, considering 500 fields limit (for example, using weekly automation to store old records in transposed table - there are many possible ways to do it, but it will require a bit scripting).
next level - use ‘depth’, for example, store all timesheets per employee*day in a single long text cell.
You can even pack a number of records with JSON.stringify, and store all your base in a single record (at least before it 's size exceed tens of megabytes, 100kB per cell * 500 fields), but this needs advanced scripting and pack/unpack procedure.
You can also create aditional table with summaries per date and sync it to other base, disabling auto-sync and switch ‘When records are deleted or hidden in the source’ = Leave the records in this table, thus creating kind of ‘archive’
Start with business data requirements. Start by identifying the data strata your business needs. This is typically operational, strategic, and at-rest.
Operational data is the hot tier; it’s what you need to make a day, weeks, and a full month of information work well for your organisation. Strategic data is generally aggregations of operational data. It is considered the warm tier because you need to call on it from time-to-time for reporting, analysis, etc. The cold tier is your at-rest data; it’s generally unneeded but important to have around for compliance, research, taxes, etc.
If you try to wing it without carefully written requirements and a technical plan, you may be successful, but it will be painful and loaded with augmentations/rewrites to address all the eventual requirements.
One thing is certain though - you cant do this well without script.
In the job record within the Jobs table, I really only need the following timesheet information:
Employee Name
Duration
That is, I only need to see a summary of timesheet info on each job - grouped by Employee.
I’m imagining a Lookup field which holds only the summarised timesheet information of the employees who worked on the job.
What would an automation look like which accomplishes this (if even possible)?
Also, could this be accomplished without needing to delete records if I use Airtable Sync and give each employee their own Base to enter timesheet information which is then synced back to the Base with the Employees, Clients, and Jobs tables?
How many timesheet records do you typically have per job?
Between 3-5 employees work on a job, and each employee would create around 10 timesheet entries for that specific job.
Do you have a unique record for the job+client+employee combo?
I’m not sure I understand this question. I do want each timesheet entry to be a unique record which links to the job+client+employee. Currently, the employees, clients, and jobs are each stored in unique records.
Use a formula field to summarize the timesheet record.
How would I go about summarizing the timesheet record? I can’t seem to get it to worth without creating unique rollups for each client (which doesn’t seem right because I’m expanding horizontally).
This depends on how your base is setup and what you want the summarized data to look like. In general, you use a formula field in the timesheet record to create a text summary of the record in a single field. Then in the linked table you use a rollup field. One rollup for the text summary, and additional rollups for numeric values that you need to keep as numeric values. However, you have a three way junction table, and it sound like you want summary numbers grouped by employee, but no record to store that summary info, so things get more complicated.
You can (a) experiment around with formula fields and rollups to see if you can get the result you want, (b) provide more info on your base structure and data summarizing needs and hope that someone will give more detailed advice for free, or (c) hire a consultant to look at your specific base, discuss you needs, and build you a solution.
