BEECN Journaling System

In order to help ensure BEECN caches are ready for deployment at a moment's notice, PBEM asks BEECN volunteer teams to:

• Once a month, do a visual check of their assigned BEECN cache (opening the box is not required);
• Once a quarter, do an inventory of the BEECN cache (opening the box and confirming contents are present and functional);
• Once a year, participate in a BEECN test. An inventory can be done concurrently with a test.

A BEECN Coordinator should not always complete these tasks. PBEM recommends a BEECN Coordinator assign these tasks to be regularly completed by their team members in rotation.

The purpose of the BEECN Journaling System is to centralize readiness monitoring of BEECN caches, which contributes to the BEECN Readiness Score and also helps PBEM address supply and security concerns. The BEECN Journaling System is powered by Smartsheet, and BEECN volunteers make inputs to it through the BEECN Journal Entry form.

...

...

BEECN Journal Entry Form

BEECN Journal Entry Form: Header

Figure 1: Screenshot of the BEECN Journal Entry Form.

The first screen of the Form appears simple but has logic built into the questions that open new sets of questions depending on the type of journal a volunteer inputs (reporting a visual check, an inventory, a test result, or reporting a cache theft).

#	Field Name	Required?	Field Type	Notes
	Journal Entry Header.
1	Journal Entry	Yes	Radio button	Use this section to indicate what type of report to make to PBEM: a visual check (Qs #5-6), an inventory (Qs #7-15), test results (Qs #16-17), an equipment theft (Q #18), or (if the report does not fit into any of those categories) making a note about the BEECN (Q #19).
2	Resource Name	Yes	Drop-down	Select the BEECN cache, fire station radio, or other resource from the drop-down menu. This is important because this step ties the journal entry to the specific BEECN resource.
3	Review Date	Yes	Date	The date of the observation, NOT the day the journal is inputted (unless those dates are the same).
4	Your name	Yes	Text	Name of the reporter in case a PBEM staffer needs to follow up with questions about the report.

...

BEECN Journal Entry Form: Visual Check

The first screen of the Form appears simple but has logic built into the questions that open new sets of questions depending on the type of journal a volunteer inputs (reporting a visual check, an inventory, a test result, or reporting a cache theft).

#	Field Name	Required?	Field Type	Notes
	Visual Check Questions When "Reporting a visual check" is selected on question 1, these are the questions that appear.
5	Equipment Visual Check	Yes	Radio button	"Looks good" or "There is a problem". If "Looks good" is selected, a message appears clearing the volunteer to submit the report.
6	Visual Check: report problem	Yes	Text	Selecting "There is a problem" for question Q5 opens a text box where the volunteer can type in the issue they detected and then click "Submit" to end the journal entry. If the problem is that the cache is missing, the volunteer should select "Reporting equipment theft" on Q1 and complete the form from there.

...

BEECN Journal Entry Form: Inventory Report

One of the benefits of using a Smart Sheet form for inventorying is that it is optimized for viewing on a smart device, like a cell phone. This means BEECN volunteers can inventory right from the field and submit their report.

#	Field Name	Required?	Field Type	Notes
	Inventory Report Questions When "Reporting an inventory" is selected on question 1, these are the questions that appear.
7	Inventory: Check the cache box	No	Checkbox
8	Inventory: Check the radio	No	Checkbox
9	Inventory: Check the canopy	No	Checkbox
10	Inventory: Supportive equipment	No	Checkbox
11	Inventory: Check the notebooks	No	Checkbox
12	Inventory: Small duffel bag	No	Checkbox
13	Inventory: Large duffel bag	No	Checkbox
14	Inventory: Cache tidiness	No	Checkbox
15	Inventory notes	No	Text	Please tell us if any cache equipment is missing and/or damaged. Please leave this field blank if there are no problems.

...

BEECN Journal Entry Form: Test Results

#	Field Name	Required?	Field Type	Notes
	Test Results Questions When "Reporting test results" is selected on question 1, these are the questions that appear.
16	BEECN Test Rating	Yes	Radio button	How strong was the signal coming in (or, how well could you hear incoming messages)? If you were at a fire station and taking in messages from several BEECNs, select the average from the list below. Indicate "1 - No signal" if you got all the equipment set up and the radio turned on, but you couldn't pick anyone up on the other end. Indicate "0 - Test Fail/Abort" if an equipment failure or some other circumstance prevented you from getting on the air.
17	Test notes	No	Text	For any issues/observations a volunteer would like to report about the test and/or cache.

...

BEECN Journal Entry Form: Reporting Theft

#	Field Name	Required?	Field Type	Notes
	Theft Report Questions When "Reporting equipment theft" is selected on question 1, these are the questions that appear.
18	Reporting possible equipment theft	Yes	Text	Text box to capture what the volunteer saw that indicates a theft of a cache or cache equipment.

...

BEECN Journal Entry Form: Submitting a Note

#	Field Name	Required?	Field Type	Notes
	Theft Report Questions When "Making a note" is selected on question 1, these are the questions that appear.
19	Note	Yes	Text	Note space to indicate anything with the BEECN that doesn't fall under the other categories (e.g. cache moved to a new location, new combination for a BEECN lock, etc).

...

Back-end of the BEECN Journaling System

This is not necessary reading for BEECN volunteers, and only PBEM staff have access to the back end of the BEECN Journaling System. The main purpose of this part of the article is to preserve institutional knowledge.

Inputs to the BEECN Journal Entry Form on SmartSheet are reflected in five PBEM-internal spreadsheets and a public-facing BEECN dashboard. The section following explains each of these sheets individually.

1.) BEECN Master Tracker

Only PBEM staff working on the BEECN program should have access to 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.

There are 29 unhidden columns, so different sections are color coded for easier scanning and review. Moving from left to right:

1. Resource basics and site info (color coded light green):
   1. # (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.
   2. 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".
   3. 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.
   4. Site: Location - The exact address of the parcel the BEECN site (not necessarily the cache) is located on.
   5. Site: Location Property - The type of property owner (school, park, private, etc) associated with the BEECN site. This should only be applied to BEECN caches and not orange kits or other resources.
2. Volunteer information (color coded light orange)
   1. 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")
   2. 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")
   3. 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)
3. Radio data (color coded light blue):
   1. 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".
   2. 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)
   3. 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".
   4. 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)
   5. 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"))
   6. 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)
   7. 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")
   8. 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})
4. Cache data (color coded light yellow):
   1. 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.
   2. Cache: Last Visual Check (locked) - 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"))
   3. Cache: Last Inventory (locked) - 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"))
   4. Cache: Location - This is the location of the cache (not the BEECN site).
   5. Cache: Location Property - The type of property owner where the cache is stored (school, park, private, etc).
   6. Cache: Contact - The name (and title, if applicable) of the person who oversees the property where the cache is stored.
   7. Cache: Contact Email - The email of the person who oversees the property where the cache is stored.
   8. Cache: Contact Phone - The phone number of the person who oversees the property where the cache is stored.
   9. Cache: Combination - The lock combination to the cache if a combination lock is applied.
   10. 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.
5. Cache tickets and notes (color coded in light purple):
   1. Tickets: n=active (locked) - 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)
   2. Tickets: n=history (locked) - 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)
   3. Resource Notes - Any special notes about this particular resource.