How to correct a large number of contributions within a closed batch
When a large number of contributions have been posted to the wrong date, the most efficient way to resolve this is to export the transactions to a csv file, modify the file, then import the updated transactions. This process is described below. Having a basic understanding of excel would be helpful when following these steps.
Note, if you have never imported contributions into the ParishSOFT Family Suite before, you may need to update your contribution settings. You also may need to update your ParishSOFT Family Suite Import settings.
Step 1 Export the Contributions List by batch
Go to Offering → Contributions List and in the field for Batch, enter the Batch name so only the contributions for that Batch are displayed. Make sure the date span includes the incorrect posting dates.
Choose the export icon.
The file will look like this.
Step 2 Copy and paste then update the transaction dates
This step allows you to re-add the original transactions under the correct date.
In the csv file, copy all of the items listed and paste them just below. Then, change the dates of the copied/pasted transactions to the correct posting date shown in blue below. The colors have been changed in the following image for demonstration purposes. You don't need to change the color but if it helps you may.
Step 3 Calculate and copy the values of the reversing transaction amounts
This step allows you to reverse those transactions which were entered on the wrong date
Add two new columns to the right, (columns Q and R) Q with the header 'calc amt' and R with the header 'offset amt.' You will use the calc amt column to calculate the negative amounts and the offset amt column to store the negative values (without the formula).
Add the formula to change the amount to a negative:
- Click into row 2 (first of the original transactions in green) in the calc amt column and type the equal sign (=).
- Then, click into row 2 of the Amount column.
- Finally, complete the formula by typing '*-1' (for times -1) and Enter. The formula should look like this: =K2*-1
- Drag the formula down all of the original transactions in that column (only the green items in the example). See the screenshot below for the formula highlighted in yellow and the results under the calc amt column.
Since you will need to import this spreadsheet and move around various columns, it is now best to copy the values you derived using the formula into their own column. To do so simply copy the entire column then paste only the values into the column titled 'offset amt.'
Step 4 Update and arrange the spreadsheet for import
Using the steps listed below, format your spreadsheet like this:
- Add a column: Right-click on column A and select Insert Columns.
- Fill all the rows that have transactions with the letter C as shown in column A below.
- Now, you have everything you need to manipulate this file for import into ParishSOFT Family Suite.
- Start by moving the columns to be in the exact order as the image below.
- Also, add a row to the top. (Right-Click on Row 1 and select Insert Row)
- In the original transactions only (shown in green in the image below), copy the offset amt values over the Amount values, so those transactions with the original incorrect posting dates have negative amounts in the amount column.
- Once you have moved all of the columns into the correct place, add the Header Row using the letter 'H' in column A, your own POL# (also called your Org ID) in column B, and the number 2 in column C.
- When you are done, your file will look similar to the image below.
- For the original entries (green), Column C should have the 'offset amt' values (not formulas); these will be negative numbers. And Column D should have the original incorrect dates.
- For the copied/pasted values (blue), Column C should have the original values (positive numbers) and Column D should have the corrected dates. For another more thorough explanation of formatting your spreadsheet, see this article.
- Save the file as a CSV type
- Import the file into ParishSOFT Family Suite.