5,013
edits
BEECN Journaling System: Difference between revisions
Jump to navigation
Jump to search
m
→1.) BEECN Master Tracker
| Line 235: | Line 235: | ||
=== 1.) BEECN Master Tracker === | === 1.) BEECN Master Tracker === | ||
The Master Tracker is the universal almanac of all BEECN resources: caches, radios, the UHF repeater, and the radio room. There is infrequent reason to change anything on this sheet; the need would emerge only if a new resource is added or the location of a resource changes. Many columns are locked because formulas govern the values in the cells and/or the data displayed is pulled from a different sheet. Any column not locked contains data that can be changed on this sheet. | The Master Tracker is the universal almanac of all BEECN resources since 2013: caches, radios, the UHF repeater, and the radio room. There is infrequent reason to change anything on this sheet; the need would emerge only if a new resource is added or the location of a resource changes. Many columns are locked because formulas govern the values in the cells and/or the data displayed is pulled from a different sheet. Any column not locked contains data that can be changed on this sheet. '''Only PBEM staff working on the BEECN program should have access to this sheet.''' | ||
There are 29 unhidden columns, so different sections are color coded for easier scanning and review. Moving from left to right: | There are 29 unhidden columns, so different sections are color coded for easier scanning and review. Moving from left to right: | ||
# Resource basics and site info (color coded light green): | # '''<big>Resource basics and site info (color coded light green):</big>''' | ||
## '''#''' ''(locked) -'' The number column is there to make it easy to sort the resources. By right clicking on the column and selecting "Sort Ascending", BEECN caches appear on top, followed by fire station, followed by anything else. | ##'''#''' ''(locked) -'' The number column is there to make it easy to sort the resources. By right clicking on the column and selecting "Sort Ascending", BEECN caches appear on top, followed by fire station, followed by anything else. | ||
## '''Resource Name -''' The name of the BEECN resource. If adding a resource, it is ''very'' important to follow the common naming conventions. For example, if you wanted to add the fifty-first BEECN and it was located at Pier Park in St. Johns, its name would be "BEECN N-51 Pier Park". | ## '''Resource Name -''' The name of the BEECN resource. If adding a resource, it is ''very'' important to follow the common naming conventions. For example, if you wanted to add the fifty-first BEECN and it was located at Pier Park in St. Johns, its name would be "BEECN N-51 Pier Park". | ||
## '''Site: Hexad''' - Indicates Northwest, Northeast, Southeast, Southwest, or North Portland. If a cache site is east of 82nd Avenue, a PBEM staffer can also indicate "East" in addition to Southeast or Northeast. | ## '''Site: Hexad''' - Indicates Northwest, Northeast, Southeast, Southwest, or North Portland. If a cache site is east of 82nd Avenue, a PBEM staffer can also indicate "East" in addition to Southeast or Northeast. | ||
## '''Site: Location -''' The exact address of the parcel the BEECN site (not necessarily the cache) is located on. | ## '''Site: Location -''' The exact address of the parcel the BEECN site (not necessarily the cache) is located on. | ||
## '''Site: Location Property''' - The type of site property owner (school, park, private, etc). This should only be applied to BEECN caches and ''not'' orange kits or other resources. | ## '''Site: Location Property''' - The type of site property owner (school, park, private, etc). This should only be applied to BEECN caches and ''not'' orange kits or other resources. | ||
# Volunteer information (color coded light orange) | # '''<big>Volunteer information (color coded light orange)</big>''' | ||
## '''Vol: Coordinator''' ''(locked)'' - Provides the name of the Coordinator for that BEECN crew. Vacancies appear highlighted in bright orange. This column pulls data from sheet "3.) BEECN Volunteers" and the individual cells cannot be changed. When volunteer records need updating, that should '''''ALWAYS''''' be done in the BEECN Volunteers Sheet. Formula is: =IFERROR(INDEX({VolSscontactX}, MATCH([*Vol: CoordID]@row, {VolTypeID}, 0), 1), "Vacant") | ##'''Vol: Coordinator''' ''(locked)'' - Provides the name of the Coordinator for that BEECN crew. Vacancies appear highlighted in bright orange. This column pulls data from sheet "3.) BEECN Volunteers" and the individual cells cannot be changed. When volunteer records need updating, that should '''''ALWAYS''''' be done in the BEECN Volunteers Sheet. Formula is: ''=IFERROR(INDEX({VolSscontactX}, MATCH([*Vol: CoordID]@row, {VolTypeID}, 0), 1), "Vacant")'' | ||
## '''Vol: Asst Coordinator''' ''(locked)'' - Provides the name of the Assistant Coordinator for that BEECN crew. Vacancies appear highlighted in bright orange. This column pulls data from sheet "3.) BEECN Volunteers" and the individual cells cannot be changed. When volunteer records need updating, that should '''''ALWAYS''''' be done in the BEECN Volunteers Sheet. Formula is: =IFERROR(INDEX({VolSscontactX}, MATCH([*Vol: AsstCoordID]@row, {VolTypeID}, 0), 1), "Vacant") | ##'''Vol: Asst Coordinator''' ''(locked)'' - Provides the name of the Assistant Coordinator for that BEECN crew. Vacancies appear highlighted in bright orange. This column pulls data from sheet "3.) BEECN Volunteers" and the individual cells cannot be changed. When volunteer records need updating, that should '''''ALWAYS''''' be done in the BEECN Volunteers Sheet. Formula is: ''=IFERROR(INDEX({VolSscontactX}, MATCH([*Vol: AsstCoordID]@row, {VolTypeID}, 0), 1), "Vacant")'' | ||
## '''Vol: Assigned''' ''(locked)'' - The number of BEECN volunteers assigned to that resource, as summed from "3.) BEECN Volunteers". Formula is: =COUNTIF({VolNameX}, [Resource Name]@row) | ##'''Vol: Assigned''' ''(locked)'' - The number of BEECN volunteers assigned to that resource, as summed from "3.) BEECN Volunteers". Formula is: ''=COUNTIF({VolNameX}, [Resource Name]@row)'' | ||
# Radio data (color coded light blue): | # '''<big>Radio data (color coded light blue):</big>''' | ||
## '''Radio: Primary Relay''' - The priority resource that this BEECN resource is trying to communicate with. For BEECN caches, this is always a fire station. For fire stations, it will always be the ECC radio room. For all other resources, it will be "N/A". | ##'''Radio: Primary Relay''' - The priority resource that this BEECN resource is trying to communicate with. For BEECN caches, this is always a fire station. For fire stations, it will always be the ECC radio room. For all other resources, it will be "N/A". | ||
## '''Radio: Primary Relay Vol''' ''(locked)'' - This is the number of volunteers assigned to the resource's Primary Relay. For example, NW-01's | ##'''Radio: Primary Relay Vol''' ''(locked)'' - This is the number of volunteers assigned to the resource's Primary Relay. For example, NW-01's Primary Relay is FS-22. FS-22 has two assigned volunteers, so the integer appearing here is "2". This data is pulled from sheet "3.) BEECN Volunteers" and should never be changed on this sheet. Formula is: ''=COUNTIF({VolNameX}, [Radio: Primary Relay]@row)'' | ||
## | ## '''Radio: Secondary Relay''' - The resource that this BEECN resource is trying to communicate with if operators cannot reach the Primary Relay. For BEECN caches, this is almost always a fire station. For fire stations and all other resources, it will be "N/A". | ||
## '''Radio: Secondary Relay Vol''' ''(locked)'' - This is the number of volunteers assigned to the resource's Secondary Relay. For example, NW-01's Primary Relay is FS-26. FS-26 has one assigned volunteers, so the integer appearing here is "1". This data is pulled from sheet "3.) BEECN Volunteers" and should never be changed on this sheet. Formula is: ''=COUNTIF({VolNameX}, [Radio: Secondary Relay]@row)'' | |||
## '''Radio: Last Test''' ''(locked)'' - The date this resource was last tested. This information is pulled from "2.) BEECN Journal". Formula is: ''=MAX(COLLECT({InspDateX}, {BEECNassetX}, [Resource Name]@row, {JournalTypeX}, "Reporting test results"))'' | |||
## '''Radio: Test Avg Two Years''' ''(locked)'' - The average of all radio test scores for this resource on a scale from 0.00 to 5.00 over the past two years. This data is pulled from "2.) BEECN Journal". Formula is: ''=IFERROR(SUM(SUMIFS({JournalBeecnScores}, {BEECNassetX}, [Resource Name]@row, {InspDateX}, >TODAY(-730))) / COUNTIFS({JournalBeecnScores}, >=1, {BEECNassetX}, [Resource Name]@row, {InspDateX}, >TODAY(-730)), 0)'' | |||
## '''Radio: # of tests''' ''(locked)'' - This indicates the number of times the resource has ''ever'' been tested since it was added to the system (in almost all cases, that will be early 2013). This data is pulled from "2.) BEECN Journal". Formula is: ''=COUNTIFS({BEECNassetX}, [Resource Name]@row, {JournalTypeX}, "Reporting test results")'' | |||
## '''Radio: Test Avg History''' ''(locked)'' - Average of all radio test scores for this resource on a scale from 0.00 to 5.00 for all time since the resource was added to the system (in almost all cases, that will be early 2013). This data is pulled from "2.) BEECN Journal". Formula is: ''=AVERAGEIF({BEECNassetX}, [Resource Name]@row, {JournalBeecnScores})'' | |||
# '''<big>Cache data (color coded light yellow):</big>''' | |||
## | |||
### | |||