A Workflow for reconciling NMI Settlements and Rock Transactions

  • By Kevin Rutledge One Year Ago

The Problem:

Our church is rolling out Rock in a limited way, to begin with. We are starting with our External Website and by extension online giving. We opened an account with MyWell, but so far the experience of matching transactions in an NMI settlement with Transactions in Rock has been difficult, especially when ACH deposits are made when the time between the donation and the deposit can be longer. We also knew that given the sporadic times that fees can be withdrawn from the checking account, finding the exact fees for the various accounts that needed to cover their fees would be difficult. I knew that the transaction code in rock matched the transaction code in NMI and that in the NMI gateway, I could download a list of transaction codes in a settlement from NMI, but struggled to find a way to get the list of transactions to line up with from Rock. I developed a workflow that uses SQL and Lava to generate an email sent to our bookkeeper that looks like this.

Screenshot 2017-03-26 01.18.10(4).png

Laying the Foundation

Entity Attribute

I created a Fund Account Entity Attribute named Pays Fees which was a check box. Any account that has to cover its fees has this box checked.

The Workflow

Here is a list of the Workflow Attributes and their Types:
  • Deposit Date(Date) – User Input matching date of deposit
  • Transaction Codes(Text) – User input paste of TransactionID column of TNP report
  • Transaction Codes Transformed(Text) – Output of workflow
  • Transaction Output(HTML) – Storing the lava formatted SQL results.
  • Success Message(HTML) – Success or cancel message shown on the user input form when activity is restarted.
  • Command Attribute(Text) – storage of first User Entry Form(Process Transactions or Cancel)
  • Credit Card Fee(Decimal) - % to charge Credit Card Fees. I didn’t want to hard encode it into the SQL in case it ever changes.
  • ACH Transaction Fee(Decimal) – per transaction fee charged to each transactions.
  • Form2(Text) – storage of second user entry form command(Send Email or Cancel)

First Activity – User Input

User Entry From:

Deposit Date – Visible, Editable and Required

     Transaction Codes – Visible, Editable, and Required

Success Message – Visible and Hide Label

Screenshot 2017-03-26 01.18.10(2).png

Second Activity – Process Transaction Codes

This action processes the transaction Codes, Runs the SQL and Formats the Lava Output. The last step is to display the output and ask for verification before sending e-mail.

  1. Step 1: Clears the last success message when the user is doing multiple checks in a row. – Attribute Set Value(Success Message) to blank value
  2. Step 2: Processes the Pasted SQL code to replace column header (“id “) and replaces spaces with commas. SQL query needs a list of items separated by a "', '"
    {{ Workflow.TransactionCodes | Prepend: "'" | Append: "'" | Replace:'id ','' | Replace: ' ',"', '" }}
  3. Step 3: Runs the SQL that calculates fees based on the Credit Card Fee and ACH transaction fee workflow attributes. This SQL is more complex than anything I had ever written before. The Select is looking to return distinct Account Names, Gl Codes, Transaction Total broken down by Account, the transaction fees are calculated by the SQL in two subqueries using the workflow attributes.
     Select
       Distinct(FinancialAccount.PublicName) AS FundName,
       GlCodes.GlCode As GlCode,
       SUM(FinancialTransactionDetail.Amount) over (partition by FinancialAccount.PublicName) As FundTotal,
       (ISNULL(CCFeeSchedule.Fee,0) + ISNULL(AchFeeSchedule.Fee,0)) As Fees
    

    FROM FinancialTransactionDetail

    FULL OUTER JOIN FinancialTransaction ON FinancialTransactionDetail.TransactionId=FinancialTransaction.Id

    FULL OUTER JOIN FinancialAccount ON FinancialTransactionDetail.AccountId=FinancialAccount.Id

    FULL OUTER JOIN FinancialPaymentDetail ON FinancialTransaction.FinancialPaymentDetailId = FinancialPaymentDetail.Id

    FULL OUTER JOIN DefinedValue ON FinancialPaymentDetail.CurrencyTypeValueId=DefinedValue.Id

    <p>This next section of SQL calculates the ACH fees using the value in the workflow attribute. It only shows transactions where the type is ach, the transaction codes entered in the workflow, and the needsfees value is yes.</p>
    
    FULL OUTER JOIN
        ( Select
             FinancialAccount.PublicName As AccountName,
             Count(FinancialTransactionDetail.Amount)*{{ Workflow.ACHTransactionFee }} As Fee
    
              FROM
                FinancialTransactionDetail
    
              Left JOIN
                  FinancialTransaction ON FinancialTransactionDetail.TransactionId=FinancialTransaction.Id
    
              Left JOIN
                  FinancialPaymentDetail ON FinancialTransaction.FinancialPaymentDetailId = FinancialPaymentDetail.Id
    
              Left Join
                  DefinedValue ON FinancialPaymentDetail.CurrencyTypeValueId=DefinedValue.Id
    
              Left JOIN
                  FinancialAccount ON FinancialTransactionDetail.AccountId=FinancialAccount.Id
    
              FULL OUTER JOIN
        ( select FA.Name, AV.Value
            from FinancialAccount FA
            join AttributeValue AV on FA.ID = AV.EntityId
            where AV.AttributeId = 2944) As NeedsFees
            On  FinancialAccount.PublicName = NeedsFees.Name
    
              Where
                  DefinedValue.value = 'ACH' AND
                  FinancialTransaction.TransactionCode IN ({{ Workflow.TransactionCodesTransformed }}) AND
                  NeedsFees.Value = 'Yes'
    
                  Group By
                      FinancialAccount.PublicName) As AchFeeSchedule
    On  FinancialAccount.PublicName = AchFeeSchedule.AccountName
    

    This section of SQL calculates the Credit Card fees using the percentage in the workflow attribute and multiplies it to the sum of each fund.

    FULL OUTER JOIN
          ( Select
               FinancialAccount.PublicName As AccountName,
               Sum(FinancialTransactionDetail.Amount)*{{ Workflow.CreditCardFee }} As Fee
    
                FROM
                  FinancialTransactionDetail
                 Left JOIN
                    FinancialTransaction ON FinancialTransactionDetail.TransactionId=FinancialTransaction.Id
    
                Left JOIN
                    FinancialPaymentDetail ON FinancialTransaction.FinancialPaymentDetailId = FinancialPaymentDetail.Id
    
                Left Join
                    DefinedValue ON FinancialPaymentDetail.CurrencyTypeValueId=DefinedValue.Id
    
                Left JOIN
                    FinancialAccount ON FinancialTransactionDetail.AccountId=FinancialAccount.Id
    
                    FULL OUTER JOIN
        ( select FA.Name, AV.Value
            from FinancialAccount FA
            join AttributeValue AV on FA.ID = AV.EntityId
            where AV.AttributeId = 2944) As NeedsFees
            On  FinancialAccount.PublicName = NeedsFees.Name
    
                Where
                    DefinedValue.value = 'Credit Card' AND
                    FinancialTransaction.TransactionCode IN ({{ Workflow.TransactionCodesTransformed }}) AND
                  NeedsFees.Value = 'Yes'
    
                    Group By
                        FinancialAccount.PublicName) As CCFeeSchedule
            On  FinancialAccount.PublicName = CCFeeSchedule.AccountName
    
    FULL OUTER JOIN
                    (Select
                         FinancialAccount.PublicName As AccountName,
                         FinancialAccount.GlCode AS GlCode
                          FROM
                            FinancialAccount ) As GlCodes
        On  FinancialAccount.PublicName = GlCodes.AccountName
    
        Where
            FinancialTransaction.TransactionCode IN ({{ Workflow.TransactionCodesTransformed }})
    
        Group By
            FinancialTransactionDetail.Amount,
            GlCodes.GlCode,
            FinancialAccount.PublicName,
            CCFeeSchedule.Fee,
            AchFeeSchedule.Fee
    
        Order By
            FinancialAccount.PublicName
        <p>And the best part is this bit of SQL only available in server <a href="https://msdn.microsoft.com/en-us/library/dn921882.aspx" style="background-color: rgb(255, 255, 255);">This only works SQL Server(starting in 2016) and Azure SQL Database.</a><br><br>This allowed me to save the results into a text workflow attribute.</p>
         <pre>FOR JSON PATH, ROOT('Accounts')</pre>
       </li>This outputs the following JSON object. <pre>{"Accounts":[{"FundName":"Dinner Reservations","GlCode":"","FundTotal":60.00,"Fees":1.2000},{"FundName":"Easter Offering","GlCode":"","FundTotal":25.00,"Fees":0.0000},{"FundName":"Easter Plants and Mission","GlCode":"","FundTotal":27.50,"Fees":0.0000},{"FundName":"Regular Giving","GlCode":"12345","FundTotal":635.33,"Fees":0.0000}]}</pre>
       
       <li>Step 4: Formats the SQL results in Lava to show the table. You’ll notice a few things that you will want to change. The lava run does some basic variable assigning and math before outputting anything. The output is stored in the Transaction Output (HTML Type), so it can be used in the second User Entry Screen and the Email that will be sent later. First, it captures the SQL JSON object as jsonString <pre> {% capture jsonString %}
        {{ Workflow.TransactionCodesTransformed }}
    

    {% endcapture %} {% assign jsonObject = jsonString | FromJSON %}

    Then it creates a few variables that will be used through the output.
    {% assign creditTotal = 0%}
    

    {% assign debitTotal = 0%} {% assign depositTotal = 0 %} {% assign feeTotal = 0 %}

    It then loops through the JSON object once and calculates the deposit. As one additional check, I could collect the deposit from the user in the workflow and check it against this calculation and output an error if there is no match.
    {% for Account in jsonObject.Accounts %}
    {% assign transactionTotal = transactionTotal | Plus:Account.FundTotal %}
    {% assign depositTotal = depositTotal | Plus:Account.FundTotal %}
    {% endfor %}

    I then built the table, including placing the deposit date(user input) and deposit total(calculated).
    <table style="width:100%">
    

    <caption style="color:black;"><h4>Electronic Donations Deposit to Citizens Bank</h4> <h5>Deposit Date: {{ Workflow.DepositDate }}</h5> <h6>Deposit Total: {{ depositTotal | FormatAsCurrency }}</h6> </caption> <thead> <tr style="white-space: nowrap;"> <th style="padding-right: 20px;">GL Code</th> <th style="padding-right: 20px;">Fund Name</th> <th style="text-align: right;">Fund Credit</th> <th style="text-align:right;">Fund Debit:</th> <th style="padding-left: 20px;">Note:</th> </tr> </thead>

    I then built the table. I knew that I was going to be emailing these results, so I wanted to keep all the CSS inline to minimize formatting issues. Take note that the first account on the list is hard coded for the account that our funds are deposited into, this is entered as a debit, and the amount is added to the debitTotal variable.
    <tbody>
    <tr style="white-space: nowrap;">
    <td style="padding-right: 20px;">Citizens GL Code</td>
    <td style="padding-right: 20px;">Citizens E-Checking</td>
    <td style="text-align: right;"></td>
    <td style="text-align:right;">{{ depositTotal | FormatAsCurrency }} {% assign debitTotal = debitTotal | Plus: depositTotal %}</td>
    <td style="padding-left: 20px;"></td>

    Now we are going to loop through the jsonObject. First, a new row is created for each account that includes the GL code, the account name, and the fund total which is added to the creditTotal variable.
    {% for Account in jsonObject.Accounts %}
    

    <tr style="white-space: nowrap;"> <td style="padding-right: 20px;">{{ Account.GlCode }}</td> <td style="padding-right: 20px;">{{ Account.FundName }}</td> <td style="text-align: right;">{{ Account.FundTotal | FormatAsCurrency }}{% assign creditTotal = creditTotal | Plus: Account.FundTotal %}</td> <td style="text-align:right;"></td> <td style="padding-left: 20px;"></td> </tr>

    Then we do a quick check. If the fees calculated in the SQL Is >0, then it adds a new line, showing the same account GL and name but enters the fee total as a debit, adds the amount to the debitTotal and adds a note that it is for electronic fees. This also ends the For Loop. The fee is also added to the feeTotal.
    {% if Account.Fees > 0 %}
    <tr style="white-space: nowrap;">
    <td style="padding-right: 20px;">{{ Account.GlCode }}</td>
    <td style="padding-right: 20px;">{{ Account.FundName }}</td>
    <td style="text-align: right;"></td>
    <td style="text-align:right;">{{ Account.Fees | FormatAsCurrency }}{% assign debitTotal = debitTotal | Plus: Account.Fees %}</td>
    <td style="padding-left: 20px;">Electronic Transaction Fees</td>
    {% assign feeTotal = feeTotal | Plus: Account.Fees %}
    </tr>
    {% endif %}

    {% endfor %}

    The last account in the table is always the account where the electronic payments invoice will be paid from. The name and the GL code are hard encoded, and the feeTotal is shown and added to the creditTotal.
    <tr style="white-space: nowrap;">
    
    &lt;td style="padding-right: 20px;"&gt;ElectronicFeeGl&lt;/td&gt;
    &lt;td style="padding-right: 20px;"&gt;Miscelaneous Electronic Fees&lt;/td&gt;
    &lt;td style="text-align: right;"&gt;{{ feeTotal | FormatAsCurrency }}{% assign creditTotal = creditTotal | Plus: feeTotal %}&lt;/td&gt;
    &lt;td style="text-align:right;"&gt;&lt;/td&gt;
    &lt;td style="padding-left: 20px;"&gt;Electronic Transaction Fees&lt;/td&gt;
    

    </tr>



    I was having problems with border-top appearing in my email, so I created a row spanning all columns with an HR tag with 0 padding, margin, and top. The table foot has Fund Activity Encoded and then lists in creditTotal and debitTotal that was calculated through the for loop.
    <tr>
    <td colspan="5"<<hr style="padding:0px; margin:0px; top:0px;"></td>
    </tr>
    </tbody>
    <tfoot>
    <tr style="text-align: right; font-weight:bold; white-space: nowrap;">
    <td style="padding-right: 20px;"></td>
    <td style="padding-right: 20px; text-align:left;">Fund Activity Total:</td>
    <td style="text-align: right">{{ creditTotal | FormatAsCurrency }}</td<
    <td style="text-align: right;">{{ debitTotal | FormatAsCurrency }}</td>
    <td<</td>
    </tr>
    </tfoot>
    </table>
  4. The last step in the activity Shows the Lava Output as a second User Entry Form and gives the options to send an email or cancel. UEF Field Settings: Deposit Date Visible, Transaction Output Visible. Command Labels: Send Email and Cancel, both of which go to the next activity.

    Screenshot 2017-03-26 01.18.10(3).png

Third Activity – Send Email and Clean Up

Once confirmed, this activity performs the action.
  1. If the user selects Send Email on the last form, the email will be sent to people listed in the workflow using the same lava output. {{ Workflow.TransactionOutput }}
  2. Clears the User date so when the workflow restarts the user will need to select a new date.
  3. Clears the Transaction Codes so when the workflow restarts the user will need to enter new transaction codes.
  4. Clears the Transformed Codes which is after the header and spaces were removed.
  5. Clears the lava output of the SQL
  6. If the user selected Send an email a message is stored in a success message variable to show when the activity restarts.
  7. If the user selected cancel, a message is stored to show that the activity was canceled and no email or further work was done.
  8. Restarts the Workflow.

Fourth Activity – Delayed Delete

This starts when the activity is initiated. This will delete the workflow instance the next processing interval after the delay period has passed.

Step 1: Sets the delay for the workflow to delete. Currently set to 10 minutes.

Step 2: Deletes the workflow instance.

The Code

SQL

Select
   Distinct(FinancialAccount.PublicName) AS FundName,
   GlCodes.GlCode As GlCode,
   SUM(FinancialTransactionDetail.Amount) over (partition by FinancialAccount.PublicName) As FundTotal,
   (ISNULL(CCFeeSchedule.Fee,0) + ISNULL(AchFeeSchedule.Fee,0)) As Fees

FROM
  FinancialTransactionDetail

FULL OUTER JOIN
    FinancialTransaction ON FinancialTransactionDetail.TransactionId=FinancialTransaction.Id

FULL OUTER JOIN
    FinancialAccount ON FinancialTransactionDetail.AccountId=FinancialAccount.Id

FULL OUTER JOIN
    FinancialPaymentDetail ON FinancialTransaction.FinancialPaymentDetailId = FinancialPaymentDetail.Id

FULL OUTER JOIN
    DefinedValue ON FinancialPaymentDetail.CurrencyTypeValueId=DefinedValue.Id

FULL OUTER JOIN
    ( Select
         FinancialAccount.PublicName As AccountName,
         Count(FinancialTransactionDetail.Amount)*{{ Workflow.ACHTransactionFee }} As Fee

          FROM
            FinancialTransactionDetail

          Left JOIN
              FinancialTransaction ON FinancialTransactionDetail.TransactionId=FinancialTransaction.Id

          Left JOIN
              FinancialPaymentDetail ON FinancialTransaction.FinancialPaymentDetailId = FinancialPaymentDetail.Id

          Left Join
              DefinedValue ON FinancialPaymentDetail.CurrencyTypeValueId=DefinedValue.Id

          Left JOIN
              FinancialAccount ON FinancialTransactionDetail.AccountId=FinancialAccount.Id

          FULL OUTER JOIN
    ( select FA.Name, AV.Value
        from FinancialAccount FA
        join AttributeValue AV on FA.ID = AV.EntityId
        where AV.AttributeId = 2944) As NeedsFees
        On  FinancialAccount.PublicName = NeedsFees.Name

          Where
              DefinedValue.value = 'ACH' AND
              FinancialTransaction.TransactionCode IN ({{ Workflow.TransactionCodesTransformed }}) AND
              NeedsFees.Value = 'Yes'

              Group By
                  FinancialAccount.PublicName) As AchFeeSchedule
On  FinancialAccount.PublicName = AchFeeSchedule.AccountName


FULL OUTER JOIN
      ( Select
           FinancialAccount.PublicName As AccountName,
          Sum(FinancialTransactionDetail.Amount)*{{ Workflow.CreditCardFee }} As Fee

            FROM
              FinancialTransactionDetail



            Left JOIN
                FinancialTransaction ON FinancialTransactionDetail.TransactionId=FinancialTransaction.Id

            Left JOIN
                FinancialPaymentDetail ON FinancialTransaction.FinancialPaymentDetailId = FinancialPaymentDetail.Id

            Left Join
                DefinedValue ON FinancialPaymentDetail.CurrencyTypeValueId=DefinedValue.Id

            Left JOIN
                FinancialAccount ON FinancialTransactionDetail.AccountId=FinancialAccount.Id

                FULL OUTER JOIN
    ( select FA.Name, AV.Value
        from FinancialAccount FA
        join AttributeValue AV on FA.ID = AV.EntityId
        where AV.AttributeId = 2944) As NeedsFees
        On  FinancialAccount.PublicName = NeedsFees.Name

            Where
                DefinedValue.value = 'Credit Card' AND
                FinancialTransaction.TransactionCode IN ({{ Workflow.TransactionCodesTransformed }}) AND
              NeedsFees.Value = 'Yes'

                Group By
                    FinancialAccount.PublicName) As CCFeeSchedule
        On  FinancialAccount.PublicName = CCFeeSchedule.AccountName
FULL OUTER JOIN
                (Select
                     FinancialAccount.PublicName As AccountName,
                     FinancialAccount.GlCode AS GlCode

                      FROM
                        FinancialAccount )

                       As GlCodes

    On  FinancialAccount.PublicName = GlCodes.AccountName

    Where
        FinancialTransaction.TransactionCode IN ({{ Workflow.TransactionCodesTransformed }})

    Group By
        FinancialTransactionDetail.Amount,
        GlCodes.GlCode,
        FinancialAccount.PublicName,
        CCFeeSchedule.Fee,
        AchFeeSchedule.Fee

    Order By
        FinancialAccount.PublicName
FOR JSON PATH, ROOT('Accounts')


HTML/Lava

{% capture jsonString %}
        {{ Workflow.TransactionCodesTransformed }}
{% endcapture %}

{% assign jsonObject = jsonString | FromJSON %}
{% assign creditTotal = 0%}
{% assign debitTotal = 0%}
{% assign depositTotal = 0 %}
{% assign feeTotal = 0 %}

{% for Account in jsonObject.Accounts %}
{% assign transactionTotal = transactionTotal | Plus:Account.FundTotal %}
{% assign depositTotal = depositTotal | Plus:Account.FundTotal %}
{% endfor %}



<table style="width:100%">
<caption style="color:black;"><h4>Electronic Donations Deposit to Citizens Bank</h4>
<h5>Deposit Date: {{ Workflow.DepositDate }}</h5>
<h6>Deposit Total: {{ depositTotal | FormatAsCurrency }}</h6>
</caption>
<thead>
<tr style="white-space: nowrap;">
   <th style="padding-right: 20px;">GL Code</th>
   <th style="padding-right: 20px;">Fund Name</th>
   <th style="text-align: right;">Fund Credit</th>
   <th style="text-align:right;">Fund Debit:</th>
   <th style="padding-left: 20px;">Note:</th>
</tr>
</thead>
<tbody>
<tr style="white-space: nowrap;">
    <td style="padding-right: 20px;">Citizens GL Code</td>
    <td style="padding-right: 20px;">Citizens E-Checking</td>
    <td style="text-align: right;"></td>
    <td style="text-align:right;">{{ depositTotal | FormatAsCurrency }} {% assign debitTotal = debitTotal | Plus: depositTotal %}</td>
    <td style="padding-left: 20px;"></td>
{% for Account in jsonObject.Accounts %}
<tr style="white-space: nowrap;">
    <td style="padding-right: 20px;">{{ Account.GlCode }}</td>
    <td style="padding-right: 20px;">{{ Account.FundName }}</td>
    <td style="text-align: right;">{{ Account.FundTotal | FormatAsCurrency }}{% assign creditTotal = creditTotal | Plus: Account.FundTotal %}</td>
    <td style="text-align:right;"></td>
    <td style="padding-left: 20px;"></td>
</tr>
{% if Account.Fees > 0 %}
<tr style="white-space: nowrap;">
    <td style="padding-right: 20px;">{{ Account.GlCode }}</td>
    <td style="padding-right: 20px;">{{ Account.FundName }}</td>
    <td style="text-align: right;"></td>
    <td style="text-align:right;">{{ Account.Fees | FormatAsCurrency }}{% assign debitTotal = debitTotal | Plus: Account.Fees %}</td>
    <td style="padding-left: 20px;">Electronic Transaction Fees</td>
       {% assign feeTotal = feeTotal | Plus: Account.Fees %}
</tr>
{% endif %}

   {% endfor %}
<tr style="white-space: nowrap;">
    <td style="padding-right: 20px;">ElectronicFeeGl</td>
    <td style="padding-right: 20px;">Miscelaneous Electronic Fees</td>
    <td style="text-align: right;">{{ feeTotal | FormatAsCurrency }}{% assign creditTotal = creditTotal | Plus: feeTotal %}</td>
    <td style="text-align:right;"></td>
    <td style="padding-left: 20px;">Electronic Transaction Fees</td>
</tr>
<tr>
   <td colspan="5"><hr style="padding:0px; margin:0px; top:0px;"></td>
</tr>
</tbody>
<tfoot>
   <tr style="text-align: right; font-weight:bold; white-space: nowrap;">
   <td style="padding-right: 20px;"></td>
   <td style="padding-right: 20px; text-align:left;">Fund Activity Total:</td>
   <td style="text-align: right">{{ creditTotal | FormatAsCurrency }}</td>
   <td style="text-align: right;">{{ debitTotal | FormatAsCurrency }}</td>
   <td></td>
   </tr>
</tfoot>
</table>

I hope this workflow helps you out. I know it will save our financial secretary a lot of time. In fact, here is a video that shows the workflow in use and it took 1 minute and 10 seconds from going to the website to having an email in my box with the output.



@rutledgek

I am an associate pastor at a 150 Avg. Attendance sized United Methodist Church in Eastern Pennsylvania. I am more of an accidental IT person because I have a passion for learning where technology is going and how the church can leverage it. I am eager to find ways that I can contribute to how our church is leveraging Rock and how other churches can as well.

Kevin