Reporting Outstanding Security Deposits

DISCLAIMER: This article involves Advanced Customizations, which can be technically challenging to get working and is not part of standard support.  This is programming and must be done precisely or the results can be unpredictable.  This information is provided as a service for those who have the technical skills to work through it -- we cannot help you solve any issues with getting it working.  For more information about Advanced Customizations, see the full documentation:

https://campgroundmaster.com/help/overview32.html

The current method of entering Security Deposits (as a Charge transaction, and then adding a negating transaction when refunding it) is sufficient for most situations.  However one thing that's difficult to do is find out how many "outstanding" security deposits you have, either for past reservations or for current & future reservations.

The best way to handle this is by creating a Query that shows the total for Security Deposit transactions (since it should be zero once the original deposit is credited/refunded).  Optionally you can use a filter condition that shows any reservation with a non-zero total for Security Deposit transactions, to show only those with outstanding balances.

Here's an example of creating a Query to do that, with various date-condition filtering if needed.  Note that this example assumes you have a Transaction Category named "Security Deposit" that you've been using for this purpose, as described in the documentation here:

http://campgroundmaster.com/help/securitydeposits.html

(As with any Advanced Customization function, Queries can get pretty technical. We recommend reading through the documentation if you want to understand more about making custom queries.  Normal support does not include help with Queries or other Advanced Customization functions.)

Follow these steps:

1. Go to Maintenance / Advanced Customizations / Queries.

2. Click "Add Query".

3. You'll be given a choice of List or Cross-table -- select "List Query".

4. Enter a name for the query -- e.g. "Unmatched Security Deposits"

5. Select "Reservations" for the Base Table.

6. Click "Quick-Add Fields". This is an easy way to select the fields (columns) you want to be shown in the report.

7. Double-click on each data field you need to see, changing the Data Table if needed to find appropriate fields -- e.g. First Night, Last Night, Site Name, Status, etc. for Reservations, then change to Customers table to select Last Name, etc.  As you double-click each one in the left-hand column, it will move to the right-hand "Selected" column. If you have trouble double-clicking, you can click on the field and then click the "-->" button to move it over. Click "Done" when finished adding fields.

If you want to filer by a date or other criteria (as opposed to checking every reservation ever made):

8. Click "Edit Filtering Conditions...", then click "Quick-Filter Auto-Builder".  This will open the Reservation Selection Filter.  For an "On Site" type query to show current reservations only, you would select Filter by date, and select "Start-to-end-inclusive".  The option "Using the Query's from and to dates" will be selected by default, which is what you want.  You can also select any other criteria (e.g. to include "Checked In" only).  When done here, click OK.  A prompt may ask whether to use text comparison for pick lists -- go ahead and click Yes.

9. If you want to only show those with Security Deposit balances, click "Edit Filtering Conditions..." (unless you did step 8, in which case you're already there).  Click "Add New Condition".  Enter this expression precisely:

LoopSum(1,NumTran(Resv()),"<i>",'-TranBalAmount(ResvTran(<i>))','FieldText(ResvTran(<i>),"Tran_Cat") = "Security Deposit"') != 0

Pay attention to spacing, whether quotes are single or double, etc. -- it must be exact.  If you're viewing this in a format where the line wraps, do not start a new line -- there should be no hard line breaks.   If possible, use Copy/Paste instead of typing it.

10. Click Done to get back to Edit List Query Definition.

11. Click "Add Column", and enter the same expression from step 9.  Then enter a Column heading (e.g. Security Deposits).  Check the box "Show group totals", with "Sum" selected after that.  Click Save.

12. You can click "Save & Test" to make sure it works OK if you like.

13. Save & Close, back to the main screen.

If this is your first query, you'll notice that a new "Queries" tab view has appeared between Payments Due and Transactions. Go to that tab view and select the new query from the list at the top.   If you created a filter in step 8 to filter by date, set the From and To dates as needed.

Note that if you're filtering by date (step 8), you want to skip step 9 so it will include all filtered reservations even if they don't have a security deposit balance -- then you can verify that their balance is $0 instead of wondering whether they're being excluded for the wrong reason.