How does other org check data entry error?

Alex Wong

Hi all,


I'm wondering how other organization check for data entry error. (i.e. gift entry, constituent entry, registration entry, etc)

I am talking about org-based business rule that RE cannot enforce.

Example:

Gift Rule: If a certain funds are used on a gift, 2 gift attributes is required

Gift Rule: if a certain fund is on a pledge, all its pay-*** must also have the same fund

Constituent Rule: if a certain Constituency Code is used, one of another set of Constituency Code must be there as well (i.e. TriState, you must additionally select NY, NJ, or CT)


I'll start with what we do here at my organization. We are a self-hosted RE, therefore we created a R/O ODBC Connectivity into the SQL server that host our RE and FE and created an internal web based application that runs report directly against the database tables using SQL.


So something like this is possible:

Show all payment type gifts where fund does not equals the linked pledge's fund. This real time report is seen by all data entry and is config to show their own erroneous gift to fix.


Our org is considering moving to RE NXT which will require us to move hosting back into Blackbaud Hosting. The likelihood of Blackbaud allowing direct (even if readonly) access to the SQL database is very low. I'm trying to see what other org does in this arena without using something custom-made.


Thank you all in advance,

Alex Wong

Database Manager/Programmer

FIDF

John Heizer

We have several custom Crystal Reports created to check data entry and highlight errors/missing data.  These reports don't require R/O ODBC access to the database, they use exported MDB files and should work fine in the BB hosted environment.

Austen Brown

Hi Alex Wong‍ - I do this through queries.  I go through my maintenance queries (150+ in all) every 2 weeks or so, to check any new information added to RE or records that have been updated.  This includes anything from constituent records, tribute records, gift records, solicit codes, contact numbers, notes, actions, salutations/addressees, etc.   I also have specific queries for gifts records that I check before I post any gifts to the GL.


You can easily create queries like these and link them into a dashboard or the home screen for your gift/constituent/registry entry users to utilize and help keep the data in your system up to the standard.


There is another thread you might be interested in - "Monthly Maintenance Queries and Global Changes" - it has a lot of great responses from community members on what they look for when running maintenance queries. 

Alex Wong

John Heizer‍ What I understand is even in Export using MDB file, you are still limited to what RE allows you to export. I did not find an export field that link the payment to the pledge, well some years ago. Is it now available?


While I understand that using query and export, global change and import you can achieve many error checking/data maintenance tasks, there are some specific ones that I can't seems to do with just RE alone.


Anyone else figure a way to check payment against pledge? Payment's gift record's campaign/fund/appeal/package needs to match the pledge's campaign/fund/appeal/package. The most important is obviously Fund and Appeal as it has GL distribution implication in FE too.

John Heizer

You can get pledge payment information in a Gift Export via Installments -> Payments -> Payment Gift

Alex Wong

Thank you John.


I understand we can get the exported data in Export. I guess that's the only route then. Export into Excel then play around in excel to find mismatch. I was hoping for a more RE solution that I can pass to our normal data entry that's not good in messing in excel with formula. (unfortunately even simple formula)


Here's what I'm thinking from your info:

Gift Query on all Pledge.

Export using this query, and output all payment's fund (let's keep it simple and only fund) and the pledge's fund

Assuming that Pledge is not split gift, and payment is not split gift. I can compare the payment fund column with the pledge fund column and any mismatch is the problem that needs to be worked on


Obviously split gift would be a problem, but would you think that's the route that you would take too?


Alex