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 not obvious is how to find out how many "outstanding" security deposits you have, either for past reservations or for current & future reservations.

Note that these examples assume 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

Importing the Sample Query to report unmatched security deposits

In recent versions, a sample query is available so all you have to do is import it, instead of creating it yourself as in the examples below.  This will report any reservations where the security deposit Charges don't cancel out (meaning that the deposit has not been reversed/refunded).

To import the query:

1. Go to Maintenance / Advanced Customizations / Queries

2. Click the Import Query button, and you'll get a typical Windows file-open dialog labeled "Import Query".

3. You need to locate the Samples folder, which is typically C:\Program Files (x86)\Campground Master\Samples (most likely you just need to double-click the "Samples" folder to get there, but you may have to navigate to the location first using the "Look In" drop down at the top).

4. Now select the appropriate file:  "Sample Query - Unmatched Security Deposits", and click Open.  You'll see one or more queries added to the list.  When done importing, click Close.

Now that query will be added to the Query tab view (that tab will be newly added in front of the Transactions tab if you didn't have any Queries before this).  Just go to the Queries tab and select it from the list.

Adding a "Security Deposit" column to the On Site or other tab view.

There is also a sample query you can import as an add-on column for the tab views.

First follow the steps in the previous example to import a sample query, but select the file "Sample Query - Tab view add-on for Security Deposit balance" instead.

Then go to the tab view of interest, e.g. On Site, and click the "Options" button at the top.  Select this query you just imported above in the "Query to add with additional info" list, and click Save.

The additional column should appear on the far right

For more details on adding queries to tab views:

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

Original method of finding unmatched deposits -- manually filtering in the Transactions tab

There was a previous article for reporting outstanding security deposits, that can help you find them but takes several steps each time.  It's fairly simple in comparison to the one below because it uses the normal filtering options, so it may be the best solution for you if you're not ready to try the Advanced Customization solution below.  See that article here:

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

If that previous solution is not sufficient for your needs, the best (fanciest) 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.

Manually creating a query to show unmatched deposits

Here's an example of creating a Query to do that, with various date-condition filtering if needed.  

(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.