Skip to content

Residential Development Tracker

General Process:

  1. Run report in New World
  2. Format data in Excel
  3. Input & Manage data in ArcGIS Pro
  4. Create Pivot Tables
  5. Update Polygons
  6. Manually Set Properties
  7. Run script to update fields
  8. Update ArcGIS Online

1. Running Report in New World

Log-In to New World ERP System

Click "Community Development" tab

  • Reports
  • Permits
    • Permit Event Report

Set From Date and To Date for the month of interest

Select Available Permit Types:

  • 101 Res New SF
  • 102 Res Dup_Tri
  • 103 Town Home
  • 301 Com New

Select Event Type:

  • Final Certificate Printed

Click Print to generate report

Once report has generated, save as CSV file

2. Formatting Data in Excel

Open CSV in Excel

Remove all field except:

  • EventDate
  • PermitTypeDescription
  • PermitNumber
  • FullAddress

Save as new Excel Workbook (.xlsx) formatted as FinalPermit_Month_Year (example: FinalPermit_Feb_2023.xlsx)

Add Fields for:

  • Overall Dev ID
  • Field to input the ObjectID for the Overall Development that the address resides in
  • Sub Dev ID
  • Field to input the ObjectID for the Sub Development that the address resides in
  • Address Updated
  • Field to input if address point has it status updated to "Closed"

3. Input to ArcGIS Pro

Open up ArcGIS Pro, but keep Excel open as well

Bring the following hosted layers into ArcGIS Pro:

  • Residential Development Addresses
  • This is a point layer with addresses for each residential unit
  • ResidentialDevelopment_LessInfo
  • This is the overall development layer
  • ResDev_MonthlyBuilds
  • This is the sub development layer

Use Layer Search function to find each address and update the Excel table

Example 1:

For example, say the Excel table shows the following:

EventDate PermitNumber FullAddress
2/2/2023 2022-00010272 45 WILDLIFE PKWY

In ArcGIS Pro:

Layer Search: 45 WILDLIFE PKWY

Brings up point feature:

OBJECTID 1703
FULL_ADDRE 45 WILDLIFE PKWY
LOT 2
ClosedOut No
Permit 2022-00010272
Development Gordon Park 8
Home Type Single Family
Permit Complete Date \
FinalCertPrint \

Update the following fields in the feature:

ClosedOut: Yes

FinalCertPrint: 2/2/2023

Now, update the Excel document with the following:

Overall Dev ID Sub Dev ID Address Updated
697 290 Yes

Example 2:

If Address exists and already has a "Closed" status set the Excel fields as follows:

Overall Dev ID Sub Dev ID Address Updated
- - Already Closed

Example 3:

Apartment Buildings:

EventDate PermitTypeDescription PermitNumber FullAddress
2/20/2023 Commercial New Building Structu 2022-00030080 803 N O'NEIL ST

In ArcGIS Pro:

Layer Search: 803 N O'NEIL ST doesn't return any results

This is because Apartment Unit addresses in the Residential Addresses layer are structured differently than others:

803 N O'NEIL ST has 11 aparment units, each with its own address point:

  • 803-101 N O'NEIL ST
  • 803-102 N O'NEIL ST
  • 803-103 N O'NEIL ST
  • 803-200 N O'NEIL ST
  • 803-201 N O'NEIL ST
  • 803-202 N O'NEIL ST
  • 803-203 N O'NEIL ST
  • 803-300 N O'NEIL ST
  • 803-301 N O'NEIL ST
  • 803-302 N O'NEIL ST
  • 803-303 N O'NEIL ST

Given this, either need to change layer search to 803- and then find the correct points, or locate the address manually or using another layer

Select these 11 points and update them all at once with:

ClosedOut: Yes

FinalCertPrint: 2/20/2023

Now, update the Excel document with the following (make sure to include the number of units in the Address Updated field:

Overall Dev ID Sub Dev ID Address Updated
846 629 Yes (11)

Misc:

  • If there are duplicate entries in the Excel document, double check that both have the same permit number and then just randomly delete one of the duplicates.
  • If an addresss cannot be found in the Residential Addresses layer, query the main addresses layer and see if the point is inside or outside of a development. If it is in a development that is under construction (or approved), find the plans for that development and add all the units for that development into the Residential Addresses layer, then update those points.

4. Pivot Table in Excel

In this step, we will generate 2 pivot tables to display units by development polygon

  1. Generate a pivot table for Overall Dev ID
  2. Generate a pivot table for Sub Dev ID

Copy sheet to new sheet, name it PivotData

In this new sheet, remove all fields except: - Overall Dev ID - Sub Dev ID - Address Updated

Create a new field named Units

  • Add units from pivot table to Units field based on Address Updated field
  • After Units field is filled out, delete Address Updated field

Create Pivot Table for Overall Dev ID

  • Rows: Overall Dev ID
  • Sum Values: Sum of Units

Create Pivot Table for Sub Dev ID

  • Rows: Sub Dev ID
  • Sum Values: Sum of Units

Create & Populate Monthly CloseOuts field

  • Add a field at the top of each Pivot Table named MonthCOs
  • This is where you will create a string that the ArcGIS Dashboard will use to determine how many units have been closed out for a development for a given month and display that on a bar chart in a popup
  • This is formatted YearMonth_Units
  • So a development with 4 units in Feb 2023 would get: 202302_4 in this field
  • Manually fill this out for both Pivot Tables created in the previous steps

5. Update Development Polygons

In ArcGIS Pro,

  • Starting with the Overall Development polygon layer, find the record that matches the Overall Dev ID that needs to be modified
  • Add the number of units from Excel for that feature with the value in the Units field
  • Append the string from MonthCOs to the end of the Month Close Outs field with a semicolon in between the previous value and the new one
    • For example: 202301_1;202302_4

Warning

Don't add a semicolon at the end of the Month Close Outs field, doing so will cause an error in the script that runs in later steps

  • Repeat the above steps for each Overall Dev ID that needs to be updated
  • Repeat the entire process using the SubPivot table for the Sub Dev IDs that need to be updated
  • Once these steps are completed, make sure that you have saved your edits!

Run Script to update fields

In ArcGIS Pro, run custom model "ResDevRecalculation" on both polygon layers

This script will calculate: - Units Remaining - Percent Complete - Change status to Built if Percent Complete = 100%

6. Manually Setting Properties

There are Several fields that must be manually adjusted each month.

Display Status

In order to have more control over what developments are displayed in the dashboard, a view filter is inacted on the web side of the data that only shows units that have a value of 1 in the Display field.

This will work for any Status, however, it is designed to make it so that developments with a Status of Under Construction or Built will have the same symbology and pop-ups in the dashboard. Therefore, make sure that only features with a Status of Under Construction or Built actually have a 1 in the Display field! (The view filter could be updated to explicitly restrict it to Under Construction or Built if this becomes an issue in the future)

This method allows for easily keeping built developments shown for however long after they are built is deemed appropriate by the Planning Department. To remove a built development from view, simply set the value in the Display field to 0

Current To Date

In ArcGIS Online, navigate to the hosted layer for ResidentialDevelopment_LessInfo

  1. Under the Data Tab > Table, click on the field name Data Current To then click Calculate
  2. A popup should appear asking if you want to use Arcade or SQL, choose SQL
  3. Set DataCurrentTo = CURRENT_TIME()
  4. Click Calculate
  5. Repeat this process for ResDev_MonthlyBuilds

Note

If time is set at 12:00am on the first of the month, ArcGIS Online will interepret this as the previous day (due to some sort of error parsing local time to UTC)

7. Updating the Dashboard

Now that the hosted data is updated with permits from the previous month, we need to update the Dashboard to reflect this.

  1. Navigate to the Residential Development Dashboard
  2. Click the small Edit button and change the page summary to reflect the new date update and when the data is current through.
  3. For Example:
    • Updated March 1, 2023 with data through the end of February 2023.
  4. Click Save
  5. Click Edit Dashboard
  6. Mouse over the top "Residential Developments" title and click the gear icon to edit this pane
  7. Change the subtitle to indicate what month the data is current to.
  8. Click Done
  9. Click the save icon

8. Checking the Dashboard

  1. Sign out of ArcGIS Online (or use an incongnito window)
  2. Navigate to the Residential Development Dashboard
  3. Make sure that the dashboard loads and no sign-in prompts are displayed
  4. Click on an Under Construction development that you know has units from the month that was just entered
  5. In the popup that appears, make sure that the text below the bar chart displays the current month
  6. i.e. for "Built in previous 6 months from Mar 2023"
  7. Check that the bar representing last month has the correct number of units built

Success

Great job, the Residential Development Tracker is updated!