Residential Development Tracker
General Process:
- Run report in New World
- Format data in Excel
- Input & Manage data in ArcGIS Pro
- Create Pivot Tables
- Update Polygons
- Manually Set Properties
- Run script to update fields
- 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
- Generate a pivot table for Overall Dev ID
- 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
- Under the Data Tab > Table, click on the field name Data Current To then click Calculate
- A popup should appear asking if you want to use Arcade or SQL, choose SQL
- Set DataCurrentTo = CURRENT_TIME()
- Click Calculate
- 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.
- Navigate to the
Residential Development Dashboard
- Click the small Edit button and change the page summary to reflect the new date update and when the data is current through.
- For Example:
- Updated March 1, 2023 with data through the end of February 2023.
- Click Save
- Click Edit Dashboard
- Mouse over the top "Residential Developments" title and click the gear icon to edit this pane
- Change the subtitle to indicate what month the data is current to.
- Click Done
- Click the save icon
8. Checking the Dashboard
- Sign out of ArcGIS Online (or use an incongnito window)
- Navigate to the Residential Development Dashboard
- Make sure that the dashboard loads and no sign-in prompts are displayed
- Click on an Under Construction development that you know has units from the month that was just entered
- In the popup that appears, make sure that the text below the bar chart displays the current month
- i.e. for "Built in previous 6 months from Mar 2023"
- Check that the bar representing last month has the correct number of units built
Success
Great job, the Residential Development Tracker is updated!