BEECN Journaling System Back-end

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:

...

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.

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)

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})

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.

Cache tickets and notes (color coded 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.

Master Tracker Sheet Filters

A number of filters are built into this sheet to help find information quickly:

• Geographic filters: Will filter by Portland sextant (East, Northeast, Northwest, North, Southwest, or Southeast).
• Site Location filters: Depending on filter selected, will filter for BEECN sites that are located at schools or at parks.
• Status filters: Will filter for caches not deployed (stored or missing/stolen).

...

2.) BEECN Journal

This back-end sheet collects data inputs from the front-end and routes that data to the other BEECN sheets, and ultimately the data is reflected in the BEECN Dashboard. That data is collected from BEECN volunteers through a SmartSheet form linked to this sheet, and the columns in this sheet correspond to the questions asked on the form. When a form is submitted, the input is added as a new row to the top of this sheet.

...

The BEECN Journal Form

The BEECN Journal form is intended to act as a catch-all for volunteer reports of any kind: confirming a visual check of the cache, confirming an inventory of the cache, confirming a cache that is stolen or missing, and making a note about a cache. The form is constructed with question logic that revelas questions based on the type of report the volunteer is making. For example, at the start of the form, inventory questions only will be shown if the volunteer is reporting an inventory. A complete breakdown of the form is at: BEECN Journaling System.

Unhidden Columns

1. Reference Number _locked_ - This column automatically gives a unique reference number to every new row added. This can make tracing an individual submitted form easier through the rest of the sheets that data is routed to. It is not governed by a formula but is automated.
2. Created _locked_ - Like the Reference Number column, this column is automated. It date stamps each new row as it comes in.
3. Resource Name - Name of the BEECN resource, under the same naming convention found in 1.) BEECN Master Tracker.
4. Journal Type - The type of report submitted: visual check, inventory, test results, theft, or a note.
5. Review Date - The date that the BEECN resource was reviewed (e.g. date a visual check was made or an inventory taken). This is not the same as when the report was submitted, which is the "Created" date mentioned earlier.
6. Reporter Name - Name of the volunteer making the report.
7. VC Report - Binary choice for a Visual Check, either "Looks Good" or "There is a problem". If "There is a problem" is selected, a text box opens on the form and the volunteer can provide details.
8. VC Problem - Details on any problems a volunteer observed concerning the BEECN resource while doing a visual check.
9. INV: Notes - Notes field for a volunteer to report any inventory problems with the cache.
10. INV: Rolling Cart, INV: Radio, INV: Support, INV: Notebooks, INV: Small Duffel, INV: Large Duffel, and INV: Clean are all checkboxes that guide a volunteer through completing a cache inventory.
11. TEST Signal - The quality of radio reception (on a one to five scale) received by the radio during a test.
12. TEST Issues - Any problems of note that a volunteer ran into during a BEECN test.
13. Stolen - If a volunteer is making a theft or missing equipment report, this input field opens so they can describe what they saw and why they think the cache or cache equipment is missing or stolen.
14. Note - If someone wants to note something about a BEECN resource that is not a visual check, inventory, test result, or theft report, they can use this input field. It's like a "miscellaneous" field.
15. Submit Trouble Ticket? - This is not a column that appears in the form used by volunteers. But if a volunteer writes a Note that warrants further action or investigation, the BEECN Coordinator can click the flag icon and the row will be copied to 4.) BEECN Trouble Tickets.

BEECN Journal Automations

The BEECN Journal sheet not only collects data inputs from volunteers, but uses automations to distribute that information. How the sheet distributes information depends on the report type a volunteer is submitting.

• Visual Check to Trouble Ticket Sheet: If a volunteer uses the "VC Problem" field, the row is copied over to 4.) BEECN Trouble Tickets, prompting an email to beecn@portlandoregon.gov.
• Inventory to Trouble Ticket Sheet: If a volunteer uses the "INV: Notes" field, the row is copied over to 4.) BEECN Trouble Tickets, prompting an email to beecn@portlandoregon.gov.
• Low Test Score to Trouble Ticket Sheet: If a volunteer reports a test score of 0, 1, or 2, the row is copied over to 4.) BEECN Trouble Tickets, prompting an email to beecn@portlandoregon.gov.
• BEECN Theft Report to Trouble Ticket Sheet: If a volunteer selects and submits for "Reporting possible equipment theft", the row is copied over to 4.) BEECN Trouble Tickets, prompting an email to beecn@portlandoregon.gov. Additionally, PBEM Leadership Team members are notified.
• Trouble Ticket Checkbox to Trouble Ticket Sheet: If a back-end user selects the trouble ticket flag on a row and then hits "save", the row is copied over to 4.) BEECN Trouble Tickets, prompting an email to beecn@portlandoregon.gov.

BEECN Journal Sheet Filters

This sheet has filters built in based on the type of report submitted:

• Visual Check Reports: Filters for all visual check reports submitted.
• Inventories Only: Filters for all BEECN inventories.
• Test Results: Filters for all inventory reports.
• Theft Reports: Filters for all cache and equipment theft reports.

...

3.) BEECN Volunteers

This is the sheet where all BEECN volunteers are assigned to BEECN assignments. From here, a PBEM staff can add volunteers to a resource, reassign them to a different resource, change their contact information, or remove them from BEECN assignments. This is the only sheet in the journaling system where volunteers are managed; all other sheets displaying volunteer info draw it from this sheet.

...

Unhidden Columns

1. Last Change _locked_ - Date/time stamp of when the information in that row was last changed by someone. This is a locked and automated column.
2. Resource Name - Name of the BEECN resource as a drop-down menu, under the same naming convention found in 1.) BEECN Master Tracker.
3. Type - The type of BEECN volunteer the individual is: BEECN Volunteer, BEECN Coordinator (highlighted in dark green), BEECN Assistant Coordinator (highlighted in light green), Fire Station Volunteer (highlighted in purple), or a Fire Station ARO (highlighted in blue).
4. Last Name, First Name, Email, Primary Phone, and Cell Phone - Relevant information about the volunteer.
5. SmSh Contact - The volunteer's profile name in SmartSheet. Having a profile in SmartSheet means the volunteer can be subject to SmartSheet automations.
6. Call Sign - FCC issued radio call sign, if the volunteer has one.
7. Notes - Any special notes that the BEECN Coordinator cares to add about this volunteer (e.g. "Speaks Spanish").

BEECN Volunteers Sheet Filters

Filters on this sheet will filter for volunteer types:

• BEECN Coordinators: Coordinators only.
• BEECN Coordinators/Assistants: Both Coordinators and Assistants.
• Fire Station AROs: FS AROs only.
• Fire Station Crews: Both AROs and Fire Station Volunteers.

4.) BEECN Trouble Tickets

The Trouble Ticket part of the BEECN Journaling System helps PBEM staff keep on top of maintenance of a complicated system with many discrete resources. It also effectively serves as the BEECN Improvement Plan ("IP"). This sheet receives inputs from 2.) BEECN Journal:

• When a volunteer submits a completed report form, it becomes an added row at the top of 2.) BEECN Journal.
• If that newly added row includes any triggers, the row is automatically copied over to 4.) BEECN Trouble Tickets. Those triggers are:
  • A problem reported with a Visual Check;
  • A problem reported with an Inventory;
  • A BEECN test score of 0, 1, or 2;
  • A reported cache theft.
• An email is automatically sent to BEECN@portlandoregon.gov when a new row appears in this sheet, alerting PBEM to the issue.

A row is copied from 2.) BEECN Journal to 4.) BEECN Trouble Tickets also if, in the former sheet, the BEECN Coordinator clicks the flag icon in the column labeled "Submit Trouble Ticket?"

A Trouble Ticket remains open until the BEECN Coordinator closes it. Tickets are closed by placing a date in the "Close Date" column.

Unhidden Columns

1. Resource Name - Name of the BEECN resource as a drop-down menu, under the same naming convention found in 1.) BEECN Master Tracker.
2. Review Date - When the volunteer observed the resource (not when they reported it).
3. Close Date - Date the BEECN Coordinator closed the ticket.
4. Duration _locked_ - The number of days between Review Date and Close Date (and therefore, it only appears after a close date is inputted). Formula is: =IFERROR(NETDAYS([Review Date]@row, [Close Date]@row), " - ")
5. Type - This is the type of problem associated with the trouble ticket. The BEECN Coordinator must select this. It is a drop-down with the following options:
   • Cache box - lid/lock
   • Cache box - contents
   • Cache box - relocation
   • Fire Station - facility issue
   • Fire Station - antenna/mast
   • Handheld UHF - flashing
   • Handheld UHF - hardware
   • Handheld UHF - reception
   • Handheld UHF - missing (not stolen)
   • Orange kit - equipment/hardware
   • Orange kit - flashing
   • Stolen Equipment
   • OTHER
6. Journal Type - The type of report made by the volunteer (e.g. Visual Check, Inventory, etc).
7. Reporter Name and Reporter Email - Contact info about the volunteer who submitted the report.
8. VC Problem, INV: Notes, TEST Signal, TEST Issues, Stolen, and Note are all data copied over from 2.) BEECN Journal and make up the content of the trouble ticket. Blank cells should simply be ignored

BEECN Trouble Tickets Automations

• The sheet is programmed to automatically send an email to BEECN@portlandoregon.gov any time a new row is added to this sheet.
• A row will lock and grey out when a date is added to "Close Date".