Question

Photo of Michael Garrison

0

Need to fix financial transactions without a date- how dangerous is this?

2

When our financial team scans checks into Rock, it associates the scan date with the transaction. However, when our team manually inputs Credit/Debit transactions, they have been putting the date in the batch definition and therefore leave each transaction's date field blank.

So that's a training issue. But now we've got 5 months of transactions where none of the Credit/Debit transactions have any dates associated with them- which is presently messing up my custom "first time givers" report, and is certainly wreak havoc with our end-of-year statements.

I know not every church works this way, but we've been putting the data in in single-day batches, rather than (say) week-long batches. So it's easy enough to know that the date of the transaction IS the date of the batch.

So here's my question- I'd like to fix this issue by writing a SQL routine which will find all transactions without a date and which are in a batch that's marked as "Closed" (let's not tempt fate by trying to force-edit transactions which are in an incomplete batch), then assign them the DateTime which is recorded in the batch it's within. Is editing the data directly like that going to mess up anything in Rock? Maybe there are multiple places that needs to be updated, maybe some consistency check that Rock performs on transactions, or any other number of things that I can imagine going wrong...

So I wanted to throw it out to the developers/community and see whether this would be a safe or dangerous thing to do, regularly if necessary, or if any warning flags go up in anyone's mind, before I start developing the routine.

  • Photo of Nick Airdo

    0

    Michael, in general, editing the data directly via SQL should not cause any problem (since we do this sort of thing regularly in Rock updates) -- however a malformed SQL update command would mess things up very badly.  So, as always 1) take a backup first, 2) consider running your query inside a "begin transaction" [your commands] "rollback transaction" first so you can verify things such as the number of rows your expecting to change, a verification query, etc.  If that goes well, change the "rollback" to "commit" (or just remove the transaction wrapping) 3) consider posting your query here first so the community eyeball it with you.

     

    • Michael Garrison

      Michael Garrison

      Thanks Nick. I wasn't worried about editing data directly per se, I was just concerned that I didn't know all of the PLACES to do so in this case - if there were checksums to be updated, corresponding (redundant) cross-check locations, etc. The financial stuff is more intimidating to me than the rest of the database ;-)


      But thanks for your suggestions- I'll get working on the query and post it for review.

      • Dillan Cagnetta

        Dillan Cagnetta

        Hi Michael. How did this eventually turn out in your church. We are facing s similar situation in our church.


        Would you mind posting your query to help point us in the right direction? Thanks

      • Photo of Michael Garrison

        0

        Dillan,

        It's worked well. I ended up creating a Dynamic Data block on a "First time givers" report page I created- that way this query runs every time they load the page which relies on this information being correct. You could also omit the second query and all of the "Formatted Output" and run the query on a schedule with a Workflow.

        Query:

        UPDATE f SET [TransactionDateTime] = b.[BatchStartDateTime]
FROM [FinancialTransaction] f
INNER JOIN [FinancialBatch] b ON b.[Id] = f.[BatchId]
WHERE f.[TransactionDateTime] IS NULL
AND b.[Status] = 2;

SELECT @@ROWCOUNT AS 'count';

         

        Formatted Output:

        <div style="margin-bottom:2em;border-bottom:1px solid black;"><b>Success!</b><br />By loading this block, {% for row in rows %}{{ row.count }}{% endfor %} transactions with blank dates (usually credit card transactions) have now been assigned dates based on the batch date.</div>