What to do when a constituent record has too many media files

Katie Watkinson · August 2015

Hi. We have a couple of constituent records that are getting unusable as they have so many media files attached. What do people do in this situation? Taking the files off and using links may not be an option for us - so do we start a second constituent record or is there a better way? Interested to know what any-one else does in this situation. Thanks.

Jen Claudy · August 2015

Not sure how a record becomes unusable with too many Media files...do you mean you can't find the Media file you're looking for? If that's the problem, I would think that a second constituent record would make it more difficult to find what you're looking for. Not to mention complicate and confuse many other pieces of the record.

We are now doing complete electronic filing, but whenever possible, we combine files together...so the gift paperwork and acknowledgement letter all go in the same file. And the Title field indicates the Gift ID for the associated Gift record. Then Type is used to sort correspondance from gifts from news articles, etc. Not sure if this helps, but it's working for us.

Katie Watkinson · August 2015

Thanks Jennifer. It's not that we can't find the media files, it's just that the whole constituent record becomes too slow to open or move around (we aren't hosted by Blackbaud). Maybe using links might have to be the only way we can go.

Gina Gerhard · August 2015

MagicFolder is getting pretty high praise as a free tool from Omatic to manage 'linked' documents. Might be worth exploring?

Jennifer McHale · August 2015

Depending on how you have uploaded the files, maybe there's an opportunity to reduce the size of some of them? Alternate formats might help (but probably is not a long-term solution).

Jarod Bonino · August 2015

Katie,

Would you mind sharing what types of files you are attaching to the constituent records and how many exactly you mean by "so many"? Just a ballpark figure would help. Are we talking 5 attachments or 50? Also, any additional details you could give about the general size of the attachments and how you are using them (or why they are important to store in the RE7 database) would be awesome information for us here at Blackbaud to steer the types of scenairos we consider when building and testing our applications to support various use cases.

Thanks!

Jarod

Nicole S · August 2015

I find that this is becomes a problem with adding media files for many people. The records start to hang. It can depend on your server and equipment too. I would look into another option like Magic Folder as Gina and others suggest or PaperSave -- I'm sure there are others too.

John Heizer · August 2015

I have MagicFolder installed on my computer (no one else's yet). I know at one point there was a problem with it when merging Constituents it didn't merge the folders (not sure if that's been fixed or not). It's free, so it's certainly worth trying out. One drawback is that you have to open MagicFolder to see if there any files and you can't store the searchable RE Media metadata (Type, Description, Title) and you don't have the ability to link to Proposals. That's a serious handicap for us so I doubt we'll ever use it.

I know that once upon a time there was a lot of concern in many organizations about storing the files in the database instead of as a link. There was a fear that storing the files in the database would bloat and slow down the database. Today, any of the SQL server systems certified for use with RE should handle MASSIVE amounts of media records efficiently. As efficiently as any "standard" file server or workstation. After all, a file server is really just another type of big database storing "media" type files. The media file is still going to take up the same amount of space on a server somewhere, you're just shifting the load from the database to the file system.

The only advantage I see to linking a file instead of putting it in the database is that you can then store one centralized document that can be linked to multiple Constituents. You're storing the file one time instead of 20 times if the exact same file needs to be on 20 Constituent records. If you then change content of the linked file it will then change for all 20 Constituents. This can be both a blessing and a curse. Likewise, if you delete the file from the shared folder the link will then be broken for all 20 Constituents and there is no warning that lets you know the file is still being used by other Constituent records. Also, SQL Server 2008 or newer has built-in compression techniques (an option that has to be configured) that can find duplicate data and store it only once negating that advantage of storing the file as a link.

Whether you link or "embed" the Media files, you'll still have the large number of Media records on the Constituent, and all of those records have to come from the server to the client when you open a Constituent record. Storing them as links won't change that. The actual content of the Media file (which is where all the "heavy lifting" happens) shouldn't have to transfer over the network to your PC until you actually open that Media record. SHOULDN'T doesn't mean it's actually designed that way and I don't have enough information to know for sure. If you select "Display as icon" may reduce the amount of data that has to be transfered to your PC before you open the Media record.

Just as an example, here is a view of how big our Media table is. As you can see there are gigabytes of data in it and we don’t have any problem opening records.

Talk to your IT folks and get them to analyze your server. It may just need optimizing. I would push for a technical fix before changing how you operate.

Faith Murray · August 2015

I guess I'm curious what types of documents your organization feels needs to be contained in the media file? If this is a donor record, the only items we keep are significant letters that the donor sends to us, or that we send to the donor - but certainly not every letter. We use Notes instead to summarize important communications that don't really require seeing the original document in pdf. We might also keep contracts, such as Annuity paperwork, or death notices. All other paperwork, such as copies of checks, gifts, life insurance premiums, etc., we keep in a paper file or else in a separate folder on the server.

If this is an Event or Appeal record, and you are keeping copies of annual flyers, posters, and other advertisements associated with the event, I would suggest breaking your Appeal/Event into an annual record. Each year, you create a new appeal/event record, such as "2014 Walk/Run", "2015 Walk/Run", etc. Since our organization has Never maxed out a constituent's media file, and our BB is also hosted on our own servers, I would suggest re-evaluating what types of documents you do and don't need to keep as pdfs.

Jen Claudy · August 2015

Just be aware that if you ever plan to move to RE:NXT, you must also move to Hosting. And Hosting requires embedded files, not linked (Hosted RE cannot access your network and has limited ability to connect to your local desktop, and you wouldn't want to store your media files on a local desktop anyways). Also, NXT Media will operate more like Dropbox, and while it's not available quite yet, it should be soon from what I understand.

Not sure how PaperSave or Magic Folder work, but I'd check them as well to see if they work with a Hosted database before you expend a lot of time and energy only to have to redo everything when you eventually move to RE:NXT.

Madeleine Holdsworth · August 2015

Jennifer Claudy:

Not sure how a record becomes unusable with too many Media files...do you mean you can't find the Media file you're looking for? If that's the problem, I would think that a second constituent record would make it more difficult to find what you're looking for. Not to mention complicate and confuse many other pieces of the record.

We are now doing complete electronic filing, but whenever possible, we combine files together...so the gift paperwork and acknowledgement letter all go in the same file. And the Title field indicates the Gift ID for the associated Gift record. Then Type is used to sort correspondance from gifts from news articles, etc. Not sure if this helps, but it's working for us.

Embedding the media file takes up way too much space. We link the media files but then you must make sure everyone understands that they cannot move those files. However, linking still takes up a lot of space. We've been linking media files for two years and it still eats up the most space (by far) in RE compared to the other table sizes. You are correct; it will slow down RE (we aren't hosted either). We have stopped linking media files as a blanket rule and now do it very selectively. The files we link are all stored on our network drive. It's convenient to have them in Media, but not necessary.

What to do when a constituent record has too many media files

Comments

Categories