Modifying the sample Rent Roll with specific categories |
Top Previous Next |
(From Newsletter #46, Sept 10, 2018)
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
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:
http://campgroundmaster.com/help/queriessetupdialog.html
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 for a column
A simple change would be to change the category name -- for instance if your Transaction Category (in the Pick Lists) has "Monthly Rent" instead of "Monthly Rate" as the Selection Name, or you want the column to total the payments for a different category. Select the "Monthly" line, and click Edit Column. You will see "Monthly Rate" in the 2nd line of the Field/Expression box -- click in that box to put the edit cursor there and carefully change the name as needed (using the Selection Name as shown in the Pick List for the category you want).
Example before the change:
LoopSum(1,NumTran(Resv()),"<i>",'-TranPmtAmtForCat(ResvTran(<i>), "Monthly Rate", .F., .T.)',
(I'm leaving out the rest for brevity)
After changing it will look like this -- the only changed part is in blue:
LoopSum(1,NumTran(Resv()),"<i>",'-TranPmtAmtForCat(ResvTran(<i>), "Monthly Rent", .F., .T.)',
Be sure to leave the quotation marks in place! Nothing else should be changed in the expression. You can also change the "Column Heading" field as needed. Click Save when done.
Total: When changing or adding a category column, you need to also modify the "Total" column expression to change/add the appropriate amounts. So you also must select the "Total" line (the last line) and click Edit Column, and edit the Field/Expression box just like above (locate the category selection name that you need to change, and change it to the new text as above). This will be a much larger expression that basically adds all of the categories together, so be careful not to get lost or change anything else by accident.
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 look like this (you can use copy/paste to copy the part of the expression shown above, insert a copy after it, and change the category name as needed -- the inserted part is in blue):
'-TranPmtAmtForCat(ResvTran(<i>), "Lot Rent", .F., .T.) - TranPmtAmtForCat(ResvTran(<i>), "Slip Rent", .F., .T.)'
Note that there must be a space on each side of the minus sign in the middle, between the 2 parts: ....T.) - Tran....
The only place there should not be a space around the minus sign is at the very beginning (after the single quote).
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).
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.
Total: As with changing a category, when you add a new category then that amount must also be added to the Total. You need to insert the new part of the expression for the 2nd category, just like you did above for the combined-category expression. Remember to keep spaces around the minus signs between the TranPmtAmtForCat(...) functions as mentioned above. For example the result would look like this (truncated after the changes -- the inserted part is in blue:
LoopSum(1,NumTran(Resv()),"<i>",'-TranPmtAmtForCat(ResvTran(<i>), "Daily Rate", .F., .T.) - TranPmtAmtForCat(ResvTran(<i>), "Weekly Rate", .F., .T.) - TranPmtAmtForCat(ResvTran(<i>), "Lot Rent", .F., .T.) - TranPmtAmtForCat(ResvTran(<i>), "Slip Rent", .F., .T.) - Tran......
The exact order of the categories isn't important, as long as the same format is followed.
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 for changing the category for a column.
Total: As with changing a category, when you add a new category then that amount must also be added to the Total. But instead of just a simple text change, now you need to insert code into the expression. This is best done using copy/paste. See the "Combining categories" example above for details.
Hopefully this will help you create the Rent Roll you need.