Is there a quick way to show what DB fields we use and what we do not use?

Cathy Spencer

Our CEO would like to see a database field list for Raiser's Edge that shows all fields we populate and those we do not. I was beginning to hand write something, but he believes this should be something that can be provided or created. He is asking for constituent fields to the best of my understanding. Do you know of this? Thanks!

Christine Robertson

@Cathy Spencer I think Query/Export might be faster, but still not an easy method to necessarily. Definitely want to see what others say!

Alex Wong

@Cathy Spencer
if you are looking for specific constituent field being used, go to query and under the constituent information list of fields, criteria each one being “not blank” .. no output fields and see how many constituent records in result. you will have to do 1 field at a time, though should be pretty fast.

Cathy Spencer

@Christine Robertson thank you!

Cathy Spencer

@Alex Wong, thank you

Karen Diener

@Cathy Spencer:

Our CEO would like to see a database field list for Raiser's Edge that shows all fields we populate and those we do not. I was beginning to hand write something, but he believes this should be something that can be provided or created. He is asking for constituent fields to the best of my understanding. Do you know of this? Thanks!

Do you know why he wants this? This exercise may or may not answer the question, and it has potential to be a lot of work. When someone asks me for something from a database, 95% of the time I respond with “what is your goal”? People so often ask for what the THINK they need to solve a problem, but it isn't really going to help them.

For starters, the idea that “this should be something that can be provided” is kind of an old school thought. Back when databases were a lot more simple, and built in Access, this might have been possible. But robust databases like RE are comprised of many related tables and there is no report in the system that will generate what he needs.

Yes, doing a Query or Export to see the data is an option, but I think you need to really understand what he is trying to learn, and what set of fields (constituent level, gift level, etc.) he needs to see.

Karen

Cathy Spencer

@Karen Diener, thank you. He is wanting to meld all databases across the organization so we can all see information rather than having to call each other. He wants to repurpose unused fields in RE with information other departments have. He says he has done this at other organizations. I really only want information in my donor database that belongs there. I can't imagine how this will be maintained.

Cathy Spencer

I have clarification from the CEO: He has shared that he wants to be able to import vendor spends into a field in RE so we can see what vendors give at which level, to help us with our asks for sponsorship at major events. This would put everything in RE and reduce dependence on excel spreadsheets.

So I am still needing to find out – is it possible to have a map of fields that shows what we use and what we don’t use?

If we are really only repurposing one field, I am confused as to why an entire field map is being requested. I'm guessing it is for future use.

Thank you for your thoughts!

Cathy Spencer

Alex Wong

@Cathy Spencer
I do think your CEO has a point in his request. It is important as an organziation (maybe not everyone needs to know, but at least the DB manager does) to know exactly what information is stored in the database and where.

I did the same thing when I started with my org almost 12 years ago now, I went field by field, record to record, record to sub-record, etc to know all data that are recorded, and boy did I find a lot of junk and inconsistancy. It took quite a lot of time, but well worth it in my opinion. Good luck.

NOTE: as an added bonus, after doing this exercise, you will know your RE database extremely well =D

Karen Diener

@Cathy Spencer:

I have clarification from the CEO: He has shared that he wants to be able to import vendor spends into a field in RE so we can see what vendors give at which level, to help us with our asks for sponsorship at major events. This would put everything in RE and reduce dependence on excel spreadsheets.

So I am still needing to find out – is it possible to have a map of fields that shows what we use and what we don’t use?

If we are really only repurposing one field, I am confused as to why an entire field map is being requested. I'm guessing it is for future use.

Thank you for your thoughts!

Cathy Spencer

That is helpful, but my eyebrows are still raised.

If he JUST wants to see vendor spends, add the vendor information and use a constituent attribute to track the spend. I would not “repurpose” any other field to track that information, especially since you would probably want to see their spending over a few years.

Make sure the constituent code is “Vendor” and put the appropriate contacts in the relationship field. Try to get a good address and not the PO Box for Accounts Payable, which is most likely what is in your accounting system. Add appropriate solicit codes to the record too so that they aren't included in bulk mailings or solicitations; my guess is that these would be personal asks.

To really do this right, the front-line fundraisers should also be recording their conversations in the Actions tab. And if you have the Prospect module, use it as well to track the proposals / opportunities. If staff isn't going to do this, the point of having everything in the database and not in spreadsheets is lost.

As for documenting every field in the database for this project? Probably no. There just is no reason for it. As pointed out, that is a GREAT exercise for the DBA - it is 100% your role to try and understand how predecessors used the data, and I have done that multiple times. Have I gone through the effort to document it? Absolutely not since it is constantly evolving and much of it should be obvious. Necessary fields are documented within procedures that require them, but I've never created an inventory.

Karen

Rashida Brown

@Cathy Spencer our company, Jacobson Consulting Applications Inc. (JCA) can provide you with a CFR (Codes Frequency Report) that details all the fields populated across your RE database, all code table values, and the number of times each code occurs across the database. We would need a copy of your database and can generate the report for a fixed price fairly quickly. Feel free to contact me directly at rashida.brown@jcainc.com or visit our website at jcainc.com and click on the Start A Conversation button to schedule a call to discuss.