BEECN Journaling System Back-end: Difference between revisions
From Portland NET Wiki
| Line 20: | Line 20: | ||
# '''<big>Radio data (color coded light blue):</big>''' | # '''<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''' '' | ##'''Radio: Primary Relay Vol''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - 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''' - 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''' '' | ## '''Radio: Secondary Relay Vol''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - 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''' '' | ## '''Radio: Last Test''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - 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''' '' | ## '''Radio: Test Avg Two Years''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - 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''' '' | ## '''Radio: # of tests''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - 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''' '' | ## '''Radio: Test Avg History''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - 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>''' | # '''<big>Cache data (color coded light yellow):</big>''' | ||
##'''Cache: Deployment Status''' - This is a drop down with three possible choices: deployed, missing/stolen, or stored. Any status other than "deployed" will highlight in a darker yellow. | ##'''Cache: Deployment Status''' - This is a drop down with three possible choices: deployed, missing/stolen, or stored. Any status other than "deployed" will highlight in a darker yellow. | ||
## '''Cache: Last Visual Check''' '' | ## '''Cache: Last Visual Check''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - This pulls the report date of the last time anyone reported a Visual Check of the cache. This data is pulled from "2.) BEECN Journal". Formula is: ''=MAX(COLLECT({InspDateX}, {BEECNassetX}, [Resource Name]@row, {JournalTypeX}, "Reporting a visual check"))'' | ||
## '''Cache: Last Inventory''' '' | ## '''Cache: Last Inventory''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - This pulls the report date of the last time anyone reported an Inventory of the cache. This data is pulled from "2.) BEECN Journal". Formula is: ''=MAX(COLLECT({InspDateX}, {BEECNassetX}, [Resource Name]@row, {JournalTypeX}, "Reporting an inventory"))'' | ||
## '''Cache: Location''' - This is the location of the cache (not the BEECN site). | ## '''Cache: Location''' - This is the location of the cache (not the BEECN site). | ||
## '''Cache: Location Property''' - The type of property owner where the cache is stored (school, park, private, etc). | ## '''Cache: Location Property''' - The type of property owner where the cache is stored (school, park, private, etc). | ||
| Line 39: | Line 39: | ||
## '''Cache: Radio Serial Number''' - The serial number of the handheld UHF radio in the cache. The cell will highlight dark yellow if this data is still needed. | ## '''Cache: Radio Serial Number''' - The serial number of the handheld UHF radio in the cache. The cell will highlight dark yellow if this data is still needed. | ||
# <big>'''Cache tickets and notes (color coded in light purple):'''</big> | # <big>'''Cache tickets and notes (color coded in light purple):'''</big> | ||
##'''Tickets: n=active''' '' | ##'''Tickets: n=active''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span>'' - The number of currently active "trouble tickets". This cell will turn dark purple if number of tickets is more than zero. This data is pulled from "4.) BEECN Trouble Tickets". Formula is: ''=COUNTIFS({TicketsCloseDateX}, ISBLANK(@cell), {TicketsResourceNameX}, [Resource Name]@row)'' | ||
## '''Tickets: n=history''' '' | ## '''Tickets: n=history''' ''<span style="background:firebrick; color:firebrick">'''_'''</span><span style="background:firebrick; color:white">'''locked'''</span><span style="background:firebrick; color:firebrick">'''_'''</span> -'' The number of tickets issued on this resource for all time, including currently active ones. This data is pulled from "4.) BEECN Trouble Tickets". Formula is: ''=COUNTIF({TicketsResourceNameX}, [Resource Name]@row)'' | ||
## '''Resource Notes''' - Any special notes about this particular resource. | ## '''Resource Notes''' - Any special notes about this particular resource. | ||
### | ### | ||
