I'll use the term "OSG-owned" to indicate the device Paul cares about. We haven't exactly figured out how this will be delimited in the database, but I assume we'll be able to SELECT those lines as part of the SQL query.

Summary

• Remove all references to MBARI_ID (auto-increment key used in instru table) and replace with SSDS_ID (some sort of key from the merged table). Note: Access assumes a relatively short MBARI_ID (4 digits), not the full SSDS UUID.

  Are we sure about this? I am not sure we need to necessarily get rid of the MBARI_ID. Yes, we would have two auto-incrementing fields, but that may not be all that big of a deal and the Access application expects an MBARI_ID and that would minimize our code changes to the Access app.

  We need to make it so that the SSDS_ID is not editable in the Access application

• Several panes in the Access app use a series of drop-downs (Manufacturer, Model, Serial Number, Type, etc) to select one-or-more devices. Whenever possible, an "OSG-owned" checkbox could be added to limit/unlimit the search.

  Done through view In theory, this should be accomplished by presenting a DB view that is keyed on the OSG view flag in the database.

  Now that the Technician and Type are mapped through a view to the Person.username and DeviceType.name fields, those become editable in the current access application. It would be better if these could be specified as drop-downs always and then were edited through the SSDS appropriate pages.

• There are a few panels which display all of the fields of the instru database, and a few which also allow editing of those fields. These need a clear and concise way of indicating and editing the "OSG-ownership"

  Also done through view In theory, this should be accomplished by presenting a DB view that is keyed on the OSG view flag in the database.

  Overall impact to the Access app would be surprisingly light. The only truly obstrusive change would be to the "Instrumentation Data Entry" pane which is primarily for new instrument entry. At present it's just a flat pane with fields for data entry (some drop-downs to normalize answers), but we're proposing changing it to a two-step process where some key information is entered, then checked for an existing records, before proceeding.

There will also be the introduction of a number of checkboxes which select if a particular search should be limited OSG-owned devices (the default) or expanded to the full SSDS database.

I think we can ignore this I think we can safely ignore this and just assume that OSG will only want to search/work on entries that have the OSG flag enabled and we will just use the view for that. If they want to expose the device through to the OSG app, they will have to use another interface to flip the bit to make it visible.

There's likely to be more impacts in changes to the meaning and function of some of the database fields.

There are also some loose ends in data normalization and field mapping (i.e. ensuring the FullSerial is always filled out, even if the user has only entered a Serial). That sort of data verification and manipulation could be encoded in Access or pushed off to the SQL server as a trigger/job.

More detail

From the front index page, there are nine sub-pages:

Transactions Data Entry

• Add checkbox to filter list on "OSG-owned" – affects drop-down selections
• I'm a little confused by the usage of this pane – it's a combination of some instrumentation entries and the transaction entries.

Instruments Data Entry

• Replace MBARI_ID with SSDS_ID.
• Add a way to indicate "OSG-owned"
• Need to put in an explicit step where the first few key fields are filled out and the database is check for duplicates before filling out the remaining fields. This function should probably ignore the "OSG-owned" field. This window may expand to include both the add and edit functions.

Current Info

• Add checkbox to filter list on "OSG-owned" – affects drop-down selections
• Replace MBARI_ID with SSDS_ID

Transaction History

• Add checkbox to filter list on "OSG-owned" – affects drop-down selections
• Replace MBARI_ID with SSDS_ID

Find Location of Calibration

• Add checkbox to filter list on "OSG-owned" – affects drop-down selections
• Replace MBARI_ID with SSDS_ID (takes information from trans table?)

Query by Location

• Add checkbox to filter list on "OSG-owned" – affects drop-down selections
• Replace MBARI_ID with SSDS_ID on list.
• Selecting "details" brings up a tertiary window. Remove MBARI_ID. Add entry on window for "OSG-owned" (may be same detail window used in "Find Model")

Find Model

• Add checkbox to filter list on "OSG-owned" – affects drop-down selections.
• Replace MBARI_ID with SSDS_ID on list
• Selecting "details" brings up a tertiary window. Remove MBARI_ID. Add entry on window for "OSG-owned"

Deployment Update

(this feature may not be functional or as functional as Paul would like.)

• At checkbox to filter list on "OSG-owned" – affects list of instruments
• Change MBARI_ID to SSDS_ID

Edit Instruments and Transactions Table

• Add checkbox in top pane to filter out OSG-owned devices – affects drop-down selections.
• Add entry box in lower pane to toggle "OSG-owned"
• Remove references to MBARI_ID in text boxes and transaction table. Replace with SSDS ID.