Identifying and Fixing Transactions Without A Batch

  • By Michael Garrison One Year Ago

Depending on your online giving provider, you may find from time to time that your transactions get pushed into Rock without being assigned to a batch. This can make things really mysterious- it's hard to find those transactions!

This post is about both identifying when that happens, and having a tool to reliably fix it when it does.

Part 1: Identifying Troublesome Transactions

I find the best way to notify our team when a transaction has come in, is to put big red text at the top of a page they access frequently when they're dealing with financial transactions. I chose the "Batch" page.

So to begin, click "Batches" on the "Financial" menu.

Click the "Zones" button on the admin toolbar and on the "Main Zone" flyout, click the "Blocks" button. (Read up on the "Building Websites in Rock" manual if I've already lost you; it'll come in handy to be familiar with adding blocks to pages).

Add a new block of type "Dynamic Data". (Call it whatever you like; I use the name "BatchId Check"). Once you've created the block, drag it in the menu up above the batch list block.

Now go into the settings for the block and use the following query:

SELECT
    ft.[Id]
    ,ft.[Summary]
    ,p.[Id] AS 'PersonId'
    ,p.[NickName] + ' ' + p.[LastName] AS 'Name'
FROM
    [FinancialTransaction] ft
    LEFT JOIN [PersonAlias] pa ON pa.[Id]=ft.[AuthorizedPersonAliasId]
    LEFT JOIN [Person] p ON pa.[PersonId] = p.[Id]
WHERE
    [BatchId] IS NULL

Then scroll down and use the following as the Formatted Output:

{% for row in rows %}
    <h3 style="margin:0px;"><div class="label label-lg label-danger">Transaction found without a batch: <a style="color:#bbf;" href="/Transaction/{{ row.Id }}">{{ row.Summary }}</a> (a contribution by <a style="color:#bbf;" href="/Person/{{ row.PersonId }}">{{ row.Name }}</a>)</div></h3>
{% endfor %}

Now you can hit save and you'll be back on the batch list page. If all is good, it will look the same as before. If it finds that you have transactions which aren't in a batch, it will look more like this:

batch-check.jpg

That'll get their attention! The transaction ID is a link to the transaction itself, and their name is also displayed, as a link to the person profile.

So, now that they know there's a problem, what do they do about it?

Part 2: Fixing the Transactions

The most direct way I've figured out to make the tool to fix the troublesome transactions requires two pages. So let's start at the Rock internal homepage.

Now click on the "Child Pages" button on the admin toolbar, and then click on the "Financial" entry to go to that menu page.

Click on "Child Pages" again, and decide where in the menu you want to put your tool. Click on that heading ("Administration" is probably more logical than "Functions", unless you have your own custom heading to keep your custom tools). Finally, click on "Child pages" one more time and this time, click the (+) button to create a new page. I called it "Fix Batch-less Transactions", but you can name it as you see fit. I found the left sidebar layout worked pretty well, so select that in the dropdown. Click Save and once the menu reappears and lists your new page, click on the page name to go to it.

We're going to need two blocks of type "Dynamic Data"- one in the left sidebar zone and the other in the main zone. Create those as above.

The first block will list all of the dates with batch-less transactions, so I called it "Transaction List".

The second block will allow you to pick any "pending" batch you've created to hold these transactions, so I called it "Batch list".


Let's take a moment and talk about the functionality of the tool we're going to make.

I wanted our users to be able to choose, not only which batch the transactions went into, but also to be able to either move ALL batch-less transactions at once, or else just all from a single day. Some churches do a week's worth of batches, others do a batch for each day, so this serves both equally well. The best way I've found to store user preferences like this when interacting with Dynamic Data blocks, is by using page parameters. So I wanted the dates listed to be links that would isolate just the transactions from that day which had no batch. If the current page had an address like /page/123, the link might take you to /page/123?date=2017-01-31 . That way, we can use SQL to figure out which date the user wants to modify.


OK, so the query for our "Transaction List" block is as follows:

SELECT
    cast([TransactionDateTime] AS Date) "Date",
    count(*) "Transactions"
FROM
    [FinancialTransaction]
WHERE
    [BatchId] IS NULL
    AND (
        cast([TransactionDateTime] AS Date)=@date
        OR @date='1980-01-01'
    )
GROUP BY
    cast([TransactionDateTime] AS Date)

Scroll on down to "Formatted Output" now and use the following:

<h3>Transactions to move into a batch:</h3><p>(click on a date to only move that date's transactions)<br /> <br />{% for row in rows %}
    <a href="/page/{{ PageParameter.PageId }}?date={{ row.Date | Date:'yyyy-MM-dd' }}">{{ row.Date | Date:'M/d/yyyy' }}</a>: {{ row.Transactions }} transactions<br />
{% endfor %}

Finally, use @date=1980-01-01 in the Parameters field - this is just an arbitrary date I picked as an identifiable placeholder for when the user hasn't limited the action to a specific date.

Click save, and you should see the first block populate with the offending dates and number of transactions for each date. If you click on the date link it provides, you'll see the page reload with only that date's transactions counted.

On to our second block- the batch picker.

Query:

SELECT @date AS "Date";
SELECT [Id],[Name] FROM [FinancialBatch] WHERE [Status]=0

Formatted Output:

<h3>Batch to move transactions into:</h3><p>(Batch status must be "pending")</p>
    <input type="hidden" id="seldate" name="seldate" value="{% for row in table1.rows %}{{ row.Date }}{% endfor %}">
    <select id="batchId" name="batchId">
        {% for row in table2.rows %}<option value="{{ row.Id }}">{{ row.Name }}</option>{% endfor %}
    </select>
    <p style="margin-top:1em;"><input type="button" value="Move all of the transactions listed to the left now!" onClick='window.location.href="/page/537?date=" + seldate.value + "&batchId=" + batchId.value' /></p>

(Note that you'll have to change the page ID (537) that I used above in a moment)

Parameters: @date=1980-01-01

So both of our blocks will check for the Date parameter and respond accordingly. This second block creates a button that directs you to the page that actually reassigns the batch though, so let's make that child page now. Click on "Child Pages" again and create a new page- I called this one "Assign Batch". Full width template will be fine for this one

Before you visit your new page, take note of the Id of your current page (it's the numeral after /page/ in your address bar). Got it? OK, now click on your second page's name in the menu once you've created it, and now we'll add one final block to the "Main Zone" on this page - another Dynamic Data block.

Query:

UPDATE
    [FinancialTransaction]
SET
    [BatchId]=@batchId
WHERE
    [BatchId] IS NULL
    AND (
        cast([TransactionDateTime] AS Date)=@date
        OR @date='1980-01-01'
    );
SELECT @@ROWCOUNT "count", [Name] FROM [FinancialBatch] WHERE [Id]=@batchId;

So this first statement hardcodes that it will only affect transaction records which have "null" set as their BatchId. It also will use the @date parameter - for every batch-less transaction found, it will only move the transaction into your selected batch IF the date matches the date you chose, OR if the date it thinks you chose is January 1, 1980 (the date we set arbitrarily as a default on the first page). The second statement gets the number of affected rows and the name of the batch- just to display a confirmation to the user. So now we need to tell it how to find and set the two parameters it uses:

@batchId=null;@date=1980-01-01

Now, I'm pretty sure this is invalid - I don't think you can set a parameter to null as a default, and I KNOW that SQL can't convert that to an integer. The reason that we do that, is because this routine is going to run every time you load the page. If you're not trying to move transactions, we need something to stop it from moving transactions to an arbitrary batch like it would if we assigned the default to 0 or another integer. Also, we're using the Formatted Output to direct the user back to the first page, so it's beneficial that this throws an error- it allows us to only redirect the user IF the query executed properly.

Enough justification of my sorry SQL. The final piece of the puzzle is the Formatted Output:

{% for row in rows %}<h3>{{ row.count }} transactions moved to {{ row.Name }}.</h3>{% endfor %}Taking you back...<script>window.location.href='/page/536';</script>

Like I said, this will only run if the SQL is passed valid data and executes without errors. Oh, we do need to replace the /536/ with the PageId of your first page that you took note of before coming here - this redirects you to the first page, so it wouoldn't do any good to redirect the user to the wrong URL!

Once you've done that, click Save and you'll be back on the second page you created. With a big red error, keeping the page from redirecting. Perfect, lol.

Use the breadcrumbs to go back up a level to your first page, and we've got one more item to tweak. Your second page is almost certainly not PageId 537, so get the ACTUAL PageId of your second page (open up the Child Pages menu again and it's listed there) and edit the Formatted Output of your batch selection block. Replace where I direct to /page/537 with your actual second page ID.

So what happens when you click the big "Move transactions" button, is it checks your selected date (January 1, 1980, if you haven't selected a date), and gets the BatchId that you've selected, and passes these two parameters to your second page in the URL parameters like /page/536?date=1980-01-01&batchId=123. Your second page gets those parameters and if there's no error executing the UPDATE command, it displays the formatted output which has some Javascript designed to take you right back to where you started.

It's a little hairy to set up, but it's fairly slick once it's done.

Happy accounting!

batch-move.JPG


@mikejed
Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!