Skip to main content

I have a customer with over 157K files in the UploadFile table.  For reasons they know, they upload a LOT of files.  They also send thousands of invoices per month which creates PDF files as well.

They are running out of diskspace and they asked me for a way to purge files in masse. 

I created a screen to do this.  But, I see that system images are being listed in my grid.  I have tried very hard to find a way to identify these image files as “system files” so they cannot be accidentally deleted.

For example:

 I thought about restricting files created by admin, but that would mean they wouldn’t be able to purge attachments to emails from many years ago.

Further down in the grid shown above, there is a file that I think they should be able to delete without any issues being created other than losing history:

 

Strangely, the File Maintenance screen will let you delete these files.  They look to me like files that are needed by the core Acumatica site.  This is the “finger print” image.

I will restate that you CAN delete these system files using the File Maintenance screen.  I’m surprised Acumatica will let a user do this.  

I’ve dug through as many tables I can find and looked at the various fields in them to see if there is ANY way to restrict the files listing to exclude these files.

I was wondering if anyone knows of a way to determine files in the UploadFiles (and related) table are system files that should not be deleted.

In the root folder of each installation there is a filed called “files.list”

This file contains a list of all the files used by the platform.

If you get this file for your Acumatica version, you could generate a lookup table of system files to check against.

Take note that the file names in Acumatica UploadFile replace de slash with a dash. So, if a file is listed as “Icons\login_bg1.jpg” in the file it will have a name like “CstFile-Branding-Icons-login_bg1.jpg”. 
To check if a file can be deleted you would need to search if the part of the name matches any line in the list.

You may need to do some manipulation of the names to make the search fast enough, but I think it could work.


@Marco Villasenor  THANK YOU!  I love it!  I will work on it.   I have a customer with almost 200,000 files.  Doing string comparisons will probably kill it, but if it means not deleting core files, so be it.  My program is really too dangerous as it is, so this is a must.

THANK YOU AGAIN!


Doing string comparisons will probably kill it, but if it means not deleting core files, so be it.

Are you concerned about not showing the system files in the list, or just preventing them from being deleted?
Here is one idea if you’re willing to show all files in the list, but want to prevent the system files from being deleted.
Store your SystemFile names in a lookup table. When a user selects files to delete, you can create a HashSet with the SystemFile names, then loop through the selected files and do a lookup against the HashSet. This would be a really efficient way of processing the records and if a match is found, you can either skip that file, or throw an error notifying the user that they’re not allowed to delete a system file….


I want to prevent any system files from being shown in the grid.

I created a custom table ICSSystemFiles that I will store the list of system files.  In my select statement, I will do a left join and only select records where the file is not in the custom table.  I put an action on the screen to Rebuild System File list that will be required when the version of the system is not the version of the system files.  I created a setup table for the project that stores the last system version when the custom table has been updated.  This way the only time the custom table will need to be updated is if there is a build update.

Testing it now...


Gotcha. Sounds like that should work!
I’m really curious how the “Left Join” method would compare in speed to the other thought I had, which would be to implement the same HashSet lookup as I described in my previous comment, but putting it in a dataview delegate instead of only running it when the user tries to delete records.


I wondered too how a left join would affect performance as well.  To be completely honest, I’m not sure how to implement the “hashset” idea.  Ultimately, I want to prevent anything from showing in the grid of items to delete that is forbidden, rather than checking at the time of deletion. If my approach causes a problem, I will come back to your idea and try to understand how that would be implemented.  

When you say “Store your SystemFile names in a lookup table”, does that mean a dictionary?


Sorry, I should’ve been more clear! I just meant a custom table to store the System File names that you can then query to use for lookups. Same thing you’re already doing.
I’ve used a HashSet before and it’s blazing fast, but I pasted this comments section into the AcumaticaGPT that I use and it thinks the Left Join would be faster. 
 


Have you looked at the UploadFileInq.clearFiles() action?  It’s hidden by default so, you’ll have to drop in a customization to show it - Screen SM202520.  I’m not seeing any of the system files that are in your screenshot so, it seems to be filtering them out.  There’s a view delegate in play.


I found a solution that I am using.  I loop through the files.list and store the file names is a custom table.  I then do a left join with that table and if the file name for my grid is not in the custom table, it ignores the record. 

I store the current acumatica version in another custom setup table.  When the screen loads, if the current acumatica version is not what is in the setup table, it forces the custom table to be refreshed.

I haven’t seen any negative performance impact doing the left join so I think I am good to go.  

@bgraves89 thanks for the tip anyway.  I did an export of the files returned in my grid and searched against the files.list file and I don’t see any “bad” files either.  

Thanks ​@MichaelShirk for the idea of storing the file names is a table.  That seems to do the trick.


Reply