I'll start this with I am quite certain the answer is yes, but I want to check this before I plan and go ahead with any changes in the near future as the app is critical to our business so I need to find a solution that works the first time...
This app started out 2+ years ago as a simple spreadsheet for listing jobs/projects but grew into a project management app that now heavily relies on virtual columns that are calculated every time it opens. The app still functions well but is getting slower and slower to open and I need to do some maintenance to reduce the data that is spiralling out of control.
There are 1100 projects. For each project, a cost is split into 20 categories. For each category, the app pulls the relevant data from 50,000 timesheet entries, 12,000 expense entries and a couple of other smaller tables. The app then calculates a profit and loss on each project using all this collected data, using virtual columns (for near real-time costs), which slows it down and is not sustainable for long-term use.
For projects that have been closed for a year, I can mark them as no longer active but I would like to keep the historic total costs. I could manually go through these closed projects and add the totals of all timesheets and expenses etc as a "normal" column within the app, then delete the records. This will reduce the size of the large tables. (I have tried but failed to automate this in the past).
For the virtual columns that currently look like this,
IF(
[WON] = FALSE,
0-([VC - Sum of Managment] + [vc - sum of tendering] + [vc - sum of operations] + [VC - Sum of Commercial] + [VC - Sum of Meeting] - [VC - PC - Management Variation]),
[Management Fee] - ([VC - Sum of Managment] + [vc - sum of tendering] + [vc - sum of operations] + [VC - Sum of Commercial] + [VC - Sum of Meeting] - [VC - PC - Management Variation]))
If I put another IF statement in front of the above along the lines of
If the project is closed use the value in column xx, otherwise continue
Would this speed up the app? Does AppSheet stop all calculations once an IF statement is met or does it still continue to think about the rest of the formula?
Are there any other guides/videos that give other ideas on how to maintain large data sets?
Instead of deleting the timecard entries, would a security filter work to not load the data if a column is marked as "No", or am I best just deleting them now to stop problems in the future with the table getting too big?
Thanks,
LinkedIn
Twitter
