OT-PACU Asset Manager
Introduction
This Excel-based utility is designed to manage non-consumable devices in Operating Theatres and Post-Op Recovery. It replaces a ledger-based approach to tracking devices. Asset Manager extends Excel through forms and code written in Visual-Basic for Applications, which is part of the Microsoft Excel product.
Each device is entered as a record on the DEVDATA spreadsheet in AssetMgr. The information for this device will include: the SAID number, the name of the device, serial and model numbers, as well as original cost and date put into service. Additionally, the record should contain the device’s status (In Service, Repair, Stored, and so forth), classification, Test-n-Tag date and associated image file, if one exists.
Devices in Asset Manager are identified by a number assigned to that device by the organisation: the SAID number. Searches for a device in the spreadsheet use this unique number.
Asset Manager tracks devices throughout their “life” in the organisation as a series of events. An event can be “In Service”, “To Repair”, “Decommissioned”, “On Loan” and “Returned”, and “Stored”. Device repair information is recorded to help build a history on a device.
Asset Manager creates a number of reports about devices and their status.
How to Best Use Help
While Asset Manager’s use seems pretty straight-forward to the developer, he realises that his approach to managing equipment may seem less than intuitive to other inhabitants of the solar system: hence, this Help file.
As Microsoft in their wisdom didn’t see fit to include a means to provide a very flexible custom help documentation system without installing questionable add-ons - something most security-conscious IT departments of organisations such as Queensland Health would definitely never permit - an approach had to be come up with that would provide the information quickly and accurately. Therefore, I have chosen HTML (think: Web) pages to display documentation in, using Sphinx to provide the framework. The approach to finding help in these pages depends on how the user generally searches for help, but the following is the suggested approach.
When the user clicks on the Help button, that user will be offered this help file to read. It is suggested to make use of the Quick Search feature first, entering the name of the button or control on the form the user has a question about. Help will display a number of context-specific help options.
For example, let’s say help is needed on the purpose of this button:
Moving the mouse cursor over the button will display the name of the control. Sometimes the tool-tip text will appear beneath the control instead of next to the cursor:
Make a note of this name, click on the Help button, and then, in the Quick Search field:
…type the name of the button or control and press <Enter>.
If Quick Search does not produce any results, please notify the developer.
Conventions and Glossary of Terms
This help file assumes certain conventions:
Physical keys you type on
<Enter> - the Enter key, at the right side of the keyboard
<Tab> - the Tab key, above the CapsLock key, to move from field to field
Representations in the text of screen objects to click on
[ Close ] - represents a clickable button
(_o_) - represents a selected radio button
(___) - represents a deselected radio button
[_X_] - represents a selected tick box
[___] - represents a deselected tick box
Names of objects in a form
Textbox - allows entry of free text. Some text boxes automatically convert text to upper-case. Example shown: the SAID field on the “Add or Edit Equipment” form.
Dropdown - Also known as a “Combobox”, it appears to be a textbox with a down-arrow on the right-hand side. Contains a listbox of selectable items, but will also allow keyboard entry of text. Example shown: the DeviceType dropdown on the “Add or Edit Equipment” form.
Tickbox - acts as a toggle to turn a condition on or off. Example shown: the New Event tickbox on the “Add or Edit Equipment” form.
Radio button - used to select one - and only one - of several choices. Example shown: the Device Status radio-buttons on the “Add or Edit Equipment” form.
Toggle - turns a condition on or off. In this example from the “Equipment Management” form, the “All” toggle, when depressed, displays devices from all locations whilst displaying a Location column in the Devices List, with the Location/Owner dropdown empty and disabled. When the “All” toggle is not depressed, the Owner/Location dropdown is enabled, and a Location displays in the dropdown, with all devices of that type and in that location displaying in the Devices listbox.
Listbox - contains a list of items or list of information on an item. Example shown: the Devices List on the “Equipment Management” form.
Button - also known as a “command button”, used to invoke actions, such as closing the form, opening forms, and saving information, as the above button does when clicked. Example shown: the Save button on the “Add or Edit Equipment” form.
History, Caveats and How to Contact Me
History
Asset Manager was developed as a response to my accepting the Device Manager portfolio in PACU. When I asked what means were provided to track devices, I was given a ledger with paper in it: lined paper.
Um, no. That wasn’t going to work.
Right about the time I was given that portfolio, inventory on non-consumables was being conducted in our Operating Theatres and PACU/Day Surgery. As that inventory process was nearing completion, I asked those responsible whether I could obtain a copy of the data. I was sent a CSV file, which I imported into Excel. This provided the framework for the main DEVDATA worksheet.
Caveats
Asset Manager is all contained in a stand-alone Excel workbook. Although I would have preferred a proper database, Excel was all I had available within the organisation I was developing in, so I commandeered it to act as a flat-file database. Excel as a database is not ideal, but through the quirky magic of Visual Basic for Applications, it generally works as expected. No question it is definitely an improvement over a physical ledger.
VBA itself is not the stellar language to write solutions in one could hope or wish for, and my knowledge on how it works is a bit less than comprehensive. Therefore, someone following me in trying to maintain/fix the code may wonder at some of the kludges I employed. As I learn more on how to do this all properly, the kludges are being removed/replaced, but whilst I try to be as professional as I could developing this tool, Asset Manager is pedestrian code at best.
The developer of this tool makes no claims as to the accuracy or reliability of the data, nor are there any guarantees, implied or real, as to the document’s fitness for purpose. I.e., it is presented and delivered as-is. Please keep this in mind when using this tool. I ask the user’s patience with the code… it is what it is.
The code supporting the use of this document - i.e., the “software” - is FOSS: free/open-source software. Please see the GNU General Public Licence v3 page for the licence agreement associated with this software.
If you manage to break it, you get to keep both pieces.
How to Contact the Developer
Whilst software developers endeavour to write clean, robust software with consistent, predictable behaviour, bugs do happen. The CallSheet developer is committed to supporting CallSheet, hence this document and a web presence on github.
A number of means exist to contact the developer:
To find the most current documentation - a (probably) updated version of this document - go to:
Getting Started
System Requirements
“Asset Manager” (also known as AssetMgr) is an Excel workbook using Visual Basic for Applications (a scripting language) to facilitate managing non-consumable equipment for Operating Theatres and Post-Op Recovery. As the software was written in and runs in Excel, there is no installation required except for Excel itself, which the organisation has provided. This workbook can be opened anywhere Excel is available. This file best serves the Operating Theatre when stored on a local drive.
The software makes use of images to help the user identify the device. The folder of device images (interestingly named “images”) must accompany - as in, be in the same folder - as the AssetMgrxxx file, or it will not open. The user can launch the AssetMgr file anywhere - on a local drive, on a memory stick, or on the Data8 server - but a copy of the images folder must be in that folder with it.
Help, on the other hand, can be opened as long as the user opens Asset Manager on a drive that can see the Metro South network.
Starting Asset Manager
When you open Asset Manager in Excel 2016, security settings (set by the Metro South IT service) may issue a security warning:
Some active content has been disabled. Click for more details.
Next to this warning is a button labelled [Enable Content]. This button must be clicked in order for the software to work. When you click this button, another security warning may appear, asking whether you wish to make this file a Trusted Document. If you wish to no longer see these warnings, click [Yes]. If you wish to disable warnings for networked files (any files that live on the server instead of on the PC you’re working on), tick the box in the lower left-hand corner.
The Asset Manager workbook will resize itself as it displays a splash screen. The workbook defaults to the “Top” worksheet:
…which has one large button on it. This button brings up the form that facilitates managing devices.
Equipment Management - The Forms
Overview - What Asset Manager Does
Asset Manager was developed to manage information on assetted devices in the Operating Theatres and post-op recovery.
Devices are entered into Asset Manager when they first arrive in the department.
- Note:
It is important to confirm the device has been assetted by Materiel Management and tested and tagged by BTS prior to putting the device into service. If a yellow bar-code sticker is missing or the device has no SAID number affixed to it, it is likely that the device has not been assetted.
Asset Manager was designed to provide a means to manage non-consumables - those devices identified with yellow or white bar-code stickers. These non-consumables are termed “Devices” in Asset Manager. Devices should have been assigned a yellow (or sometimes white) bar-code sticker by the Materiel Management Department, although those stickers sometimes go walkabout. In addition to a bar-code, the sticker also displays an 8-digit number: the SAID number.
These numbers identify devices in Asset Manager, as SAID numbers are unique for each device. When conducting a search for a specific device in Asset Manager, use this number.
When a new device arrives, an envelope containing device information should accompany the device, containing crucial information for identification and trouble-shooting purposes. It is strongly suggested to take the time to enter key information such as model and serial number into Asset Manager as that information will help identify devices further down their service life. Device-specific information such as name, model number and serial number need to be recorded on the DevData worksheet.
To enter the device into Asset Manageris a simple process: conduct a search for the device by SAID by entering the SAID into the search field and pressing <ENTER>. As a new device will have an SAID that does not exist in DEVDATA, the user simply follows the prompts to enter device information and save the information into a new record.
The Main Equipment Form
The main form in Asset Manager is launched via the large button on the “Top” sheet.
The main Asset Manager form…
has six basic areas:
The Edit Equipment Form
This form is launched by the main form when adding new equipment to the DevData sheet or editing existing equipment information on the DevData and EventData sheets. It can be launched from two buttons - to either add or edit a device - from the Button Bar or from the “Manage Devices” section of The Menu.
The Edit Equipment form has five basic areas:
Equipment Management
Device Search
An SAID number is made up of eight numeric digits. To find a device in the system, type the number into the search field:
and either click on the Search button:
or simply press <Enter> on the keyboard.
Note:
the entry into the search field is checked for non-numeric (alphabet letters) characters, and then that the entry is eight digits long. If the entry does not meet those conditions, the user is notified; otherwise, a search is performed.
If the number was entered correctly and the corresponding SAID exists in the DevData worksheet (a record exists for the device in DEVDATA) in Asset Manager, the software will find it and display it in a list of similar devices. If the SAID number cannot not be found, the user will be prompted via a dialogue to add a new device using the entered SAID number:
It’s a good idea to double-check that the SAID number was typed in correctly. If so, the user has a choice: select [ Cancel ] to cancel adding a device with that SAID number or [ Ok ] to open the Edit Device Information form to add a new device to Asset Manager.
Device Types and Locations
Next to the Search field and button is a dropdown box with a list of device types. The main devices list-box displays a list of devices that are part of a device type group. The device type dropdown box looks like a textbox, except that it has a down-pointing arrow at the right of the field:
Select from this dropdown to display devices that correspond to the selected device type in the list below. Possible types include:
Antithrombotic
Gases-Medical
Medication Delivery
Patient Warming
Monitoring
and so on.
Button Bar
The button bar’s buttons provide a quick and easy way to do the most common tasks.
NewDevice - The New Device button opens the Edit Equipment form to allow entry of a new device into Asset Manager.
EditDevice - The Edit Device button opens the Edit Equipment form to allow entry of information on an existing device in Asset Manager. Note: if an entry in the Events Listbox is selected, that entry will be opened for editing; otherwise, it is assumed that the user wishes to enter a new Event for the selected device.
Reports - The Reports button opens the Reports dialogue offering a number of device reports which print on to the BYTYPE (by type) and DEVHIST (device history) worksheets.
ShowSheet - The Show Sheet button closes the Manage Equipment form, makes the DEVDATA worksheet the active worksheet and drops the user onto the record in that sheet corresponding to the last selected device in the Device List.
Help - Opens Firefox and displays this document.
Close - Closes the Manage Equipment form. Note: this button and Menu –> File –> Exit are the only methods to close this form. Clicking on the red ‘X’ in the upper right-hand corner of the form does nothing. This is by design.
Device List
The list of devices in the upper listbox show all devices that correspond to the device type selected in the Device Type dropdown:
The currently selected device is the one that is highlighted. The device’s SAID number will display in the Search field.
The columns in that listbox are:
Column |
Description |
|---|---|
SAID |
A unique identifying number issued by Materiel Management |
Owner |
Location: OT - AN - PA [1] |
Status |
“In Service”, “To Repairs”, and so forth |
Description |
The name of the device, conforming to Materiel Management / BTS |
Test-n-Tag Date |
Date of last BTS Test-n-Tag |
Located-Sighted |
Where (or if) the device was last sighted |
The last two columns were added to help identify devices that had not been sighted - and thus may have disappeared from the unit or have through some other means gone missing. This is necessary for yearly inventory purposes.
As the user can see from the above table, one of the approaches to managing devices is by assigning the device a status. No device is infinitely useful: thus, it has a lifespan and a status within that lifespan: for example, “In Service”, “To Repairs”, “Decommissioned” or “On Loan”.
The second column - Owner - refers more to the most likely location the device would occupy. The view can be modified to list only devices with a specific owner. Above the list, to the right of the Device Type dropdown, is a Owner toggle button. When set to [On] (the default setting):
the list view will display all devices of type. [All] toggled [On] also clears and disables the Owners dropdown. Toggling [All] to [Off]:
enables the Owners dropdown: the user can then select which owner’s devices to display in the Devices List (and the owner column will no longer display in the list).
The Devices List only displays devices that are active in the system and does not display devices with a status of “Decommissioned”, so as to minimise clutter. In order to see those devices, from the Filter Menu, select “All Devices” from the bottom of that dropdown. To remove “Decommissioned” devices from view, select “Active” from the Filter dropdown.
Events List
In the course of its lifespan, a device will see a number of events. By definition, when a device changes status, it is considered a new event. Recording a change of device status is done in the “Add or Edit Equipment” form.
This listbox displays all events for the currently selected device in the Devices List. Changing which device is selected will change the events displayed.
Edit Equipment Information
‘Edit Equipment’ is launched from the ‘Equipment Management’ form. The information on this form and the controls will reflect the context in which the form was launched. For example, if the user launches ‘Edit Equipment’ as a result of following the prompt to add new equipment when searching for an item not currently in the database, the SAID number will be the one typed into the search field. If the form was launched from the New Equipment button, no SAID number will be in the SAID field: the user will need to enter this or the record cannot be saved.
Device Name and Type
The name of a device
The main devices list-box displays a list of devices that are part of a device type group. The device types are in a special field called a “dropdown”, characterised by a textbox with a down-pointing arrow at the right of the field:
Selecting from this dropdown will display all devices in the list below that correspond to the selected device type. Some of the possible types include: “Antithrombotic”, “Gases-Medical”, “Medication Delivery”, “Patient Warming”, “Monitoring” and so on.
Device Status
The list of devices in the upper listbox show all devices that correspond to the device type selected in the Device Type dropdown:
The currently selected device is the one that is highlighted. The device’s SAID number will display in the Search field.
The columns in that listbox are:
Column |
Description |
|---|---|
SAID |
A unique identifying number issued by Materiel Mgmt |
Owner |
Location: OT (Operating), AN (Anaesthetics), PA (PACU) - this column will not display if the [Owner Toggle] is toggled off and the dropdown next to it displays an Owner |
Status |
“In Service”, “To Repairs”, etc |
Description |
The name of the device, as recognised by Mat Mgmt and BTS |
Test-n-Tag Date |
Date of last BTS Test-n-Tag |
Located-Sighted |
Where (or if) the device was last sighted |
The last two columns were added to help identify devices that had not been sighted - and thus may have disappeared from the unit or have through some other means gone missing. This is necessary for yearly inventory purposes.
As the user can see from the above table, one of the approaches to managing devices is by assigning the device a status. No device is infinitely useful: thus, it has a lifespan and a status within that lifespan: i.e., “In Service”, “To Repairs”, “Decommissioned” or “On Loan”, for example.
The second column - Owner - refers more to the most likely location the device would occupy. The view can be modified to list only devices with a specific owner. Above the list, to the right of the Device Type dropdown, is a Owner toggle button. When set to [On] (the default setting):
the list view will display all devices of type. [All] toggled [On] also clears and disables the Owners dropdown. Toggling [All] to [Off]:
enables the Owners dropdown: the user can then select which owner’s devices to display in the Devices List (and the owner column will no longer display in the list).
The Devices List only displays devices that are active in the system and does not display devices with a status of “Decommissioned”, so as to minimise clutter. In order to see those devices, from the Filter Menu, select “All Devices” from the bottom of that dropdown. To remove “Decommissioned” devices from view, select “Active” from the Filter dropdown.
Device Details
In the course of its lifespan, a device will see a number of events. By definition, when a device changes status, it is considered a new event. Recording a change of device status is done in the “Add or Edit Equipment” form.
This listbox displays all events for the currently selected device in the Devices List. Changing which device is selected will change the events displayed.
Button Bar
There is some duplication of button functions in the traditional menu at the top of the main form.
The button bar’s buttons provide a quick and easy way to do the most common tasks.
Save - The Save button saves the event information to the EventData worksheet. If this was an edit of existing information, it will save over that record.
ShowSheet - The Show Sheet button closes the Manage Equipment form, makes the DEVDATA worksheet the active worksheet and drops the user onto the record in that sheet corresponding to the last selected device in the Device List.
Help - Opens Firefox and displays this document.
Close - Closes the Manage Equipment form. Note: this button and Menu –> File –> Exit are the only methods to close this form. Clicking on the red ‘X’ in the upper right-hand corner of the form does nothing. This is by design.
Device Image
When available, the image displayed corresponds to the currently selected device in the Device List. If no item is selected, or no picture exists for the selected item, the image is just a question mark, the default image.
Unlike on the Equipment Management form, the image is clickable, allowing the user to select a new image from the images folder. [8]
If a record for the device exists in the workbook, it will appear in …
Footnotes
Additional notes
Main Worksheets
Asset Manager is an Excel workbook comprised of several worksheets.
Two worksheets - DEVDATA and EVENTDATA - contain the actual data. The records are “linked” by a column (field) both sheets share: the SAID number field. In DEVDATA, the SAID Number field contains unique records: each SAID represents one device. While in EVENTDATA, the SAID references the device in DEVDATA, each record is a unique event for the devices, so as a device can see numerous events, the SAID number will not be a unique identifier for records in EVENTDATA.
Unfortunately, Excel doesn’t support SQL, so there is no way to set up data relationships as a proper relational database would, with key fields and SELECT statements, so relationships have to be done somewhat manually with code. Which mostly works, albeit much slower.
Device Data
DEVDATA contains the device information originally supplied by Materiel Management. For that reason, several of the columns have little relevance for our purposes. The columns that are used by the program are the one in italics in the following list:
Name |
Description |
|---|---|
SAIDNumber |
Unique asset number assigned by BTS/Mat Management |
DeviceDescription |
Name of the device |
Status |
In Service, To Repairs, Decommissioned, Stored, etc |
Location |
Most recent site device was sighted |
Owner |
Used if on loan from another site |
Asset |
–Not used– |
FuncLocation |
–Not used– |
FuncLocationDesc |
–Not used– |
CostCentre |
Unreliable, not used |
Manufacturer |
All caps for consistency |
ModelNumber |
Inconsistently documented in BTS, helps identify device |
SerialNumber |
Inconsistently documented in BTS, helps identify device |
AcquisitionDate |
–Not used– |
AcquisitionValue |
–Not used– |
CurrentAge |
–Not used– |
EquipmentType |
Classification, used to group devices within the software |
DeviceImage |
Optional: image of the device, displayed in the software |
Test-n-Tag |
Date of last BTS Test-and-Tag |
Event Data
EVENTDATA contains event information for devices, i.e., things that have happened, like “put into service”, “sent to repair”, “retired” and so forth. This data is original to Asset Manager and is not readily available through other information outlets, such as Materiel Management or BTS. Thus, all columns have total relevance. The columns are:
Name |
Description |
|---|---|
SAIDNumber |
Number assigned by BTS/Mat Management, also the key field |
EditedBy |
Name of the person preforming the documentation |
ChangedDate |
Event date |
DatedStatus |
Device status on this date: In Service, To Repairs, Decommissioned, etc. |
Notes |
Details regarding that status |
EventID |
Unique identifier for that event |
Supporting Worksheets
The rest of the tabs at the bottom of the workbook represent support worksheets:
Name |
Description |
|---|---|
Meta |
Data lists used by the software - generally, not to be edited directly |
Contacts |
Vendors, suppliers and other contacts (sort-of a virtual Rolodex) |
ByType |
Report |
DevHist |
Report |
Repair |
Temporary report sheet |
Images |
Image list and location - its future is in doubt |
In short, the worksheets each serve a purpose. The main ones are summarised below:
Top: – serves only one purpose: to launch the form.
DevData: – A set of records of non-consummable (assetable) Devices
EventData: – Records of events for those devices.
Meta: – contains names and associated abbreviations. It also stores cross-session information, like last opened device record and details, last user and whether the form is running in debug mode or not.
Contacts: – is a vendor Rolodex of sorts.
ByType: – is a report sheet. Useful for collecting test-n-tag information.
DevHist: – is a report sheet, listing of a specific item type, with a history for each device.
Licence Agreement
GNU General Public Licence v3
Terms of General Public Licence, version 3:
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public Licence as published by the Free Software Foundation, either version 3 of the Licence, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public Licence for more details.
You should have received a copy of the GNU General Public Licence along with this programme. If not, see:
Full GNU GPL (need online access to view this document)