We are a group of users of Blackbaud products and are not affiliated with Blackbaud. We'd love to have you join our community to help and be helped in getting the most from your Blackbaud software.
Register now to join us to get independant advice on your system, connect with 3rd party consultants to help you maximize your database and have a real alternative to the official Blackbaud website.
Every year we have an Employee Campaign where the employees can elect to have an amount automatically deducted from their pay. We then get a paper report from HR listing emp ID, name and amount per pay-period. This is then manually entered into a batch.
I've been asked to find a way to "automate" (or import) this but I'm not sure it's possible. I'd need an electronic version of the paper report, which is easy, but the problem is HR doesn't know which fund to apply the gift to - so that info wouldn't be in the file. Right now, when this is manually entered, RE prompts for this info on every line of the batch.
Does anyone know if this is possible?
__________________ Real programmers don’t document. If it was hard to write, it should be hard to understand.
Are the gifts also applied to either a recurring gift or a pledge? If so then the import file needs the ID of the pledge/recurring gift to apply it to. That is typically the most difficult part.
If not, you are entering them as individual gifts each time then either you or HR needs to put into the file they give you all fields you need to enter a gift (which includes but is not limited to Fund)
Depending on the size of your file, at some point it is either here nor there whether you do this in batch or in the excel file as it could take nearly the same amount of time (presuming you take advantage of defaults in batch etc.)
I could be wrong but I can't imagine it being quicker as someone (either you or someone in HR) is putting in the time to create an import file so it is probably more a matter of preference than anything.
Yes they're being applied to pledges (and some recurring gifts). We have almost 3,000 employees currently participating. So what I'm hearing is I'd need to first create an import (with the pledge/recurring gift ID), then match up the gift amounts from the HR file to our constituents in the import file and then finally import that data into RE. Hmm, I bet I could mess this up a bit.
I think you're right - this doesn't sound much easier than the batch.
__________________ Real programmers don’t document. If it was hard to write, it should be hard to understand.
Can't you do a recurring batch so that you would only have to manually update the changes?
If that doesn't work, I have set up DTS packages that take a text file and do all of the pre-processing to match it up for import. As long as you can get it in electronic format and the Employee ID is recorded somewhere in Raiser's Edge (or you have a table in another database that contains a mapping of Employee ID to RE ID), you can use the Employee ID to look up the Constituent Import ID, the Import ID of any open recurring gift or pledge, and the fund(s) for those recurring gifts/pledges and output those along with the amounts from HR. Split gifts make this more complicated, but not terribly so.
Of course, this all assumes that you have access to the back end of your RE database and that you have access to Enterprise Manager.
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
We started a 5 year employee pledge campaign a couple of years ago. I only had around 300 employees, not 3000, but what I did could apply to you. I got an Excel file of all the people who pledged and I made their constitutent id the same as their employee number from payroll, ex 1234. I had to move some people around in order to get them to match. From this excel file I imported these records as pledges. I get an excel file every three months of the employees and how much money they had given. My import file contains: ConsidName - FirstMiName - LastGFTamtGFTypeGfdateGFLinkFundIDCampID
I just import this file into the system every quarter. The GFLink is the same number as their Consid. I do not import the name fields, they are only there to help identify the employee.
Hey Drew, I know I'm going to be asked to this in the future and I was alreayd thinking SSIS (the new DTS) would be the solution, but my problem is I have employees with multiple recurring gifts/pledges that they are paying on... We don't use split gifts because it causes other problems that are worse for us...
How did you handle that?
Doug
Quote:
Originally Posted by DrewAllen
If that doesn't work, I have set up DTS packages that take a text file and do all of the pre-processing to match it up for import. As long as you can get it in electronic format and the Employee ID is recorded somewhere in Raiser's Edge (or you have a table in another database that contains a mapping of Employee ID to RE ID), you can use the Employee ID to look up the Constituent Import ID, the Import ID of any open recurring gift or pledge, and the fund(s) for those recurring gifts/pledges and output those along with the amounts from HR. Split gifts make this more complicated, but not terribly so.
__________________ ~~~~~~~~~~~~~~~~~~~
Doug Creek
RE Database Administrator
University of Alaska Foundation sndgc@email.alaska.edu
Hey Drew, I know I'm going to be asked to this in the future and I was alreayd thinking SSIS (the new DTS) would be the solution, but my problem is I have employees with multiple recurring gifts/pledges that they are paying on... We don't use split gifts because it causes other problems that are worse for us...
Well, my DTS solution was only for new pledges. We ran into other problems with the payments, mostly being that we could not import them into a batch, so I did not have to worry about matching up the payments to the pledges.
As to how to do it, it depends on your policies. How would you handle this if you were doing it manually? You could just apply it to the first one that it finds until that is paid off and then the next one until all of the monies received are distribute. Using this approach, you can order the pledges to force it to find certain pledges before other pledges. Another option is to divide up the payment based on the original amounts or current balances of all open pledges. It's possible to program either approach, it's just a little more complicated that way.
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
Unfortunately, I can't use the "pay off the first, then the second" approach. That would make it easy.
I might have to start pushing out the gift import IDs into the banner HR system somewhere because I have to know where each amount goes. For example, they can make a "$1 a day pledge" to KUAC (so 365/26 pay periods), then a $10 recurring gift to some other fund. They have to be paid concurrently...
Thanks for the ideas,
Doug
__________________ ~~~~~~~~~~~~~~~~~~~
Doug Creek
RE Database Administrator
University of Alaska Foundation sndgc@email.alaska.edu
Linear Mode
