Note: If creating a report on a batch with a specific field layout, and then a new batch with a different field order is created, the original report will no longer work.
The following is the only documentation available for those tables and we are unable to provide additional assistance.
The majority of the gift information that batch stores mostly mirrors the gift tables. There is a batch gift table that stores the main batch gift information as well as several batch gift child tables (like soft credits and splits). Gift Notes and Attributes are stored in a different manner. All gift attributes and notes (from the batch gift grid) are stored in a separate table called BatchData. This table joins to BatchGift on batchgiftid like all of the other child tables. There is also a column field that designates what column each row goes with.
There will be an issue trying to translate the column # into a header for what the column contains. For example, if adding a batch with notepad type, notepad date, and notepad notes, then the BatchData table would contain the following:
If you know what each column for the batch is, you can use sub selects to retrieve and translate the data from this table. For example using the following would return all of the batchgift fields along with notepad type and notepad notes:
select batchgift.*, (select tableentries.longdescription from batchdata left outer join tableentries on batchdata.data = tableentries.tableentriesid where batchdata.batchgiftid = batchgift.batchgiftid and col = 9) as 'notepad type', (select data from batchdata where batchdata.batchgiftid = batchgift.batchgiftid and col = 11) as 'notepad notes' from batchgift
An alernative SQL query to just pull all gift and credit card information from a specific batch number would be:
BH.BATCHNUMBER as 'BATCH_NUMBER',
BH.description as 'BATCH_DESCRIPTION',
inner join dbo.BATCHHEADER BH on BH.BATCHID=BG.BatchHeaderID
BH.BATCHNUMBER = '7777' -- here the batch number = 7777
Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.