BBIS SQL Tables - can we find the date and timestamp at which a donation was entered

David Wanless

Hi folks,


We'd like to be able to analyse the times of day at which BBIS donations were made.  In BBCRM, we have only the date, with no timestamp.  The XML from the TRANSACTIONS table contains the full datetime value in <TransactionDate> and <GiftDate>, but how can I find these in an SQL table as standalone fields?  Is there a table which contains this information?  BBNCREVENUE has the Date Added but that's only when it was downloaded from BBIS.  If you know the name of any of the BBIS tables which contain this or similar information about transctions, I'd love to know.


Thanks,


David

david.wanless@wilderness.org.au.

Chris Kastner

Hi David,


Hope you're well!!  I've had a bit of a look at and looks like the timestamp does only appear within the XML for the transaction from what I could find.  The dbo.Transactions table does have a couple of additional date fields with time values (BeginDownload and EndDownload) however I believe that is when CRM actually downloaded the transaction, not when it was actually processed by the gateway, which may be some minutes out depending on the schedule timing.  Not sure if that would be sufficient... you could also potentially just add a Revenue Attribute to your donation forms and populate a time using JavaScript into the field (and hide it) so it appends a time to your transactions... that might be more elegant, depending on what you're needing it for I guess!


Cheers,

Chris