Solution Properties
Solution ID:
230607205846793
Last Modified Date:
Wed Jun 07 20:58:46 UTC 2023
Taxonomy Path:
Case category//How do I...//an integration
Author:
[email protected]
Office Connector's Write PM Lease Adjustment Tool via Credit Charge Type
Description
Cause
Resolution

The Write PM Lease Adjustment Tool via Credit Charge Type in Office Connector is a way to create adjustments in Property Management. You can reduce rents, by a percentage or flat amount, through a credit recurring charge, such as a concession or abatement. It can apply the changes to multiple leases on a property at the same time.

For example, several leases on a property have a concession (CONCES), or other credit charge type, as a recurring charge for a negative amount. This concession offsets a portion of the regular rent.

Use this tool to adjust the amount of the recurring credit. You can adjust based on a percentage of another recurring charge, such as RENT or CAM, or by a flat amount. The new amount is effective as of a specified date.

Use the Write PM Lease Charge Type Adjustment Tool to adjust the recurring charge for regular charges instead of using a separate credit recurring charge. For example, you want to adjust the recurring charge amount for RENT or CAM.

Required software

You need the following software to use the Office Connector templates.

  • Sage 300 Construction and Real Estate - Property Management application
  • Microsoft Excel 2010 or higher (32-bit)
  • Sage Office Connector - Query (designer or standard)
  • Sage Office Connector - Write (designer or standard)

If you don't already own Sage Office Connector, you can learn more about the product and contact sales.

Preliminary steps

We recommend completing the following steps before using the tool.

  1. Make a backup of your Sage 300 CRE company data folder.
  2. If you’re using Security in Sage 300 CRE, create a unique login to use for Office Connector. You can use the Operator Stamp to identify the records you create/update via Office Connector.
    • For example, user John Doe has a designated Office Connector login as OC-JohnD. John Doe uses the Office Connector templates to update records and logs in as OC-JohnD. The Operator Stamp of OC-JohnD shows John Doe updated or changed those records via Office Connector.
    • Minimum Sage Security access required:
      • Security Administrator
      • ODBC Read Access for:
        • PM Lease files
        • PM Tenant files
        • PM Property files
        • PM System files
      • ODBC Write Access for:
        • PM Lease files
        • PM Tenant files
        • PM Property files
        • PM System files
  3. You can test the tool with the PM Sample Data. You can also make a copy of your own data for testing.
▼ Use the Write PM Lease Adjustment Tool

Use the Write PM Lease Adjustment Tool

CAUTION:

Set up the Credit Charge Type you select in this tool as a recurring charge on the leases you change. Otherwise you’ll end up with orphaned PM Lease Recurring Chg Log records for those leases without Credit Charge Type as a recurring charge.

If you don’t have the credit charge type set up this way, set it up with a recurring amount of zero (0.00). Make sure the Effective Date for the zero amount is before the Effective Date of the new amount that you'll create through this tool.

Follow the steps below to use the Write PM Lease Adjustment Tool via Credit Charge Type.

  1. Download and install the Write PM templates from the Office Connector knowledgebase.
    1. Download the template
    2. Add the template to the Office Connector Desktop
  2. Open the Office Connector Desktop.
  3. Select the Reports tab. If you saved the template to another folder, select the tab with that folder name.
  4. Find the template named Write PM Lease Adjustment Tool via Credit Charge Type. Double-click the file to launch it.
  5. If it opens in Protected View, click the Enable Editing button.
  6. If it displays a Security Warning, click the Enable Content button.
  7. Select the company data folder to use then click OK.
  8. If you’re using Sage 300 CRE security, enter the Operator ID and Password for the correct user.
  9. Click the Information tab at the bottom of the Excel window. Read through the information about this tool.
  10. Click the Admin tab.
  11. Select the Property.
  12. Enter the Effective Date for the new recurring amount.
  13. Select the Charge Type on which to base the adjustment.
  14. Select the Credit Charge Type as the recurring charge type to adjust.
    • Example: You need to reduce the RENT recurring charges by 5%. You do this by changing the credit amount on the CONCES recurring charge. Select RENT as the Charge Type and select CONCES as the Credit Charge Type.
  15. Click Refresh Data.
  16. This will open the Adjustments tab. It shows the property's Current and Future leases with a recurring charge for the Charge Type selected.
    • Rows in red shows the lease doesn’t have the Credit Charge Type setup as a recurring charge. If you want to create an adjustment for one of these leases, set up the charge type first. (See Caution note above)
  17. For the leases you want to adjust, enter the adjustment as a negative. Enter it in either the % Amount column or the $ Amount column. Enter it as a negative percent or negative flat amount.
    • Example: To create a credit that is a 5% reduction, enter -0.05 in the % Amount column. Or to create a $25 credit, enter -25.00 in the $ Amount column enter.
    • For leases that you don’t want to change, leave the % Amount and $ Amount as 0/blank.
  18. The last column [Credit Charge Type] Amount shows what the new recurring credit amount will be.
  19. Select the Exceptions tab to view the exceptions.
    • Possible exception: Leases with the Credit Charge Type recurring charge and an existing record for the specified Effective Date. These leases don't update and appear on the exception list.
  20. When you have the values on the Adjustments tab as you like it, select the Admin tab again.
  21. Click Write Data.
  22. In the Approve Changes window, you can do one last review before committing to your changes.
  23. When ready to approve the changes, click Write. This creates a PM Lease Recurring Chg Log record. The record has the new Effective Date and Amount for the specified leases.
  24. Select No if you receive the message: "This workbook uses Query features. Would you like to refresh your queries and functions now?"
  25. When it completes, you'll see a report with a list of the changed leases. The report saves automatically in the C:\ProgramData\Event 1\OC2\WriteReports\[data folder name]\ directory.
    • Leases that were on the Exception list will appear on the report with the following error:
      • "Error -2147467259 : [Sage][SQLEngine] (31841) Update failed for table LEASE_PML_RECORD_15. Duplicate key value."
    • This means it couldn't add the record. There’s already a Lease Recurring Chg Log record for that Credit Charge Type and Effective Date.
  26. To confirm the changes in Property Management:
    1. Select Tasks, Manage Leases, Change Lease.
    2. Select one of the leases.
    3. Select the Recur Chgs tab.
    4. On the recurring charge for the Credit Charge Type that you used, click in the Amount column. This opens the recurring charge log window. Note the new entry for the new Effective Date and Amount.
  27. To adjust other properties, close the Write PM Lease Adjustment Tool via Credit Charge Type Excel window. Repeat steps 4 through 26. When you close Excel, select Don't Save at the prompt asking if you want to save changes.

NOTE:

If you need the adjustment to end as of a certain date, use the tool again with a new Effective Date. Enter the % Amount or $ Amount as opposite of what you entered before.

 

Steps to duplicate
Related Solutions