Modifying the sample Rent Roll with specific categories

Top  Previous  Next

(From Newsletter #46, Sept 10, 2018)

Go to Newsletter Index


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:




Many people ask for a "rent roll", but we have yet to implement a standard one because everyone seems to have slightly different needs, which would make implementation terribly complex to accommodate everyone.  Usually this requires customization, but there are some Sample Queries of rent rolls which might work or be good starting points for many users.


The "Sample Query - Rent Roll (new, filtered categories)" is currently the most advanced option available (added as a sample in version 9.1), which reports like Receipts by Category (older samples simply reports Charges, not Receipts by Category), and also adds columns for Electric and Water.  However, this uses specific Transaction Category names which may not match your setup.  You may also need to add additional category columns.


First of course, you need to Import the sample mentioned.  See the documentation for details:


Once you've imported the sample, you can Edit the query.  You will see a list of columns, including things like "Daily", "Weekly", "Monthly", "Water", and "Electricity".  Those are the ones that are category-specific.  For instance, the "Monthly" column will use the category named "Monthly Rate".


Changing the category name: A simple change would be to change the category name -- for instance it you use "Monthly Rent" instead of "Monthly Rate".  Select the "Monthly" line, and click Edit Column.  You will see "Monthly Rate" in the 2nd line if the Field/Expression box -- click there to put the edit cursor there and carefully change the name as needed.  Be sure to leave the quotation marks in place!  Nothing else should be changed.  Click Save when done.


Adding a new category:  To add a new column, select an existing one, e.g. "Daily", and click Copy Column.  A new copy will appear at the bottom.  Select it and click Move Up to get it where you want it.  Then follow the instructions above to change the category name.  Also change the "Column Heading" field as needed.


Combining categories:  This is where it gets tricky.  Say for instance that you have categories "Lot Rent" and "Slip Rent" that you want to both include in the "Monthly" column.  Now you need to have it do some math to add the 2 categories.  Hopefully you're good at "copy/paste" operations to copy text, so you don't have to type so much.   So, here's the beginning of the initial expression for the "Monthly" column:


 LoopSum(1,NumTran(Resv()),"<i>",'-TranPmtAmtForCat(ResvTran(<i>), "Monthly Rate", .F., .T.)',


(I'm leaving out the rest for brevity)


The important part is:  


 '-TranPmtAmtForCat(ResvTran(<i>), "Monthly Rate", .F., .T.)'


You need to add 2 different categories (but actually each must be negative), so with the new category names that will change to:


 '-TranPmtAmtForCat(ResvTran(<i>), "Lot Rent", .F., .T.) - TranPmtAmtForCat(ResvTran(<i>), "Slip Rent", .F., .T.)'


Important!  The single-quote characters must appear only once, at the start and end of that segment.  Also, if these are showing more than one line, don't actually press Enter anywhere in the expression. And of course, that's not the entire expression.  The rest should remain unchanged.


You can add any number of categories the same way.  Just make sure you're inserting each part inside the single-quote segment, separated by " - " (a minus sign with spaces on each side).


Hopefully this will help you create the Rent Roll you need.


Page URL

Campground Master Home