as a beginner here I'm looking for some ideas for a workaround.
It is going mainly about the 500 row limit, however I think, to keep the database small is in general a key focus. I know about raising it to 2.000, but this will be too smale too, plus I don't want to slow down my app. My database is in Sharepoint lists.
So, I have a couple hundreds of active projects (maximum 200-300) steadily. This means, my app needs to work mainly with these and if I'm able to export all closed projects into an archive database, my general database will be able to work with any filters, formulas, even with non delegable ones, plus the communication will be quick. But parallel with these, I need to get into the archive database sometimes, plus I need to use minimum one formula, which is not delegable (SUM) and this database will get over the 5000 mark... I can find workaround to be able to use the SUM even if they are over the 5000 limit, but still the main task has to be solved.
The main task is to remove the closed projects and save them in an archive database. And here is the point, where I'm looking for advise.
Right now I'm thinking on the following solutions.
1. Option - Within Powerapps
Automatically move the closed projects from the Active projects database to the Closed projects database (most probably excel would be a good solution, strored on OneDrive Business, as this has no problems with the full formula options and no problem with the 500 row limit).
For this I have the idea to put the new row with Closed status into a Collection and then patch that Collection into the Excel database (however I'm not sure if I will be able to use all columns - for example attachments, as these might be needed later too) and bumm, we are ready, I can use SUM on the excel database without any dellegation issue, plus any other formula as my understand is, that there is problem on excel files.
2. Option - Use Flow
In this option, I'm thinking about to use Flow (never used yet...) and start the action on a fixed time every day for example to move the records containing the closed projects into an Excel database or into an other Sharepoint list.
The Sharepoint list could have the advantage of handling for example the attachment, but facing an issue again with for example date values if I would like to calculate (and use SUM) for total sales especially if several years data will be stored there.
I'm thinking about to use an extra column with the year in text format, which could be used then as a filter and then use a workaround or the SUM formula, but again, if Excel gives me the option to use any formula, there is no need for a workaround.
And I guess, there are other options too...
Please let me know your suggestions, ideas to choose the best and easiest way to achieve that.