OverviewThe way Kapala manages the CDI has changed many times over the passed few years. The current version attempts to solve much of the issues found in previous iterations. This includes simple but comprehensive tracking of each document indexed. The main goal was to create a process that is cut down to the essentials, leaving no room for redundant data or complexity to arise. Due to this and other goals Kapala arrived at the simple version we have now, consisting of two parts. First, the main tool allows admin Kapala users to index new documents as well as manage currently indexed documents. The second allows general users inside or outside the Kapala team to search for and access documents. Both are located and handled differently due to security concerns, please continue reading to understand how each part of the tool works and how you can make changes to it if necessary.Required Files and SoftwarePython (2.7.x) - This tool was developed using Python 2.7.13, however any 2.7.x version should work.PySide - A Python module that does not come as part of the default install. PySide is used to create a GUI for the tool.gui.py - The launching file for the index management portion of the tool. Handles the graphical user interface and interaction with the admin user.cdi.py - The database handler. Handles requests from the admin front-end (gui.py) and interaction with the database.index_search.py - The launching file for the index search portion of the tool. Handles the graphical user interface and interaction with the public user.search_core.py - Handles search requests from both the admin (gui.py) and public (index_search.py) front-ends to avoid duplication of code.cdi.sqlite - Holds index on the Oregon and Washington Index as well as some specific LR2000 Information used for indexing and searching purposes.Useful ResourcesPython - https://docs.python.org/2/SQLite3 - https://docs.python.org/2/library/sqlite3.htmlSQLite - https://www.sqlite.org/docs.htmlPySide - https://srinikom.github.io/pyside-docs/PEP 8 - https://www.python.org/dev/peps/pep-0008/CSS - https://developer.mozilla.org/en-US/docs/Web/CSSAdmin Index Management PortionIn this section we'll discuss the responsibilities of the admin portion of the tool and give a few examples of ways to change or add functionality to the index management tool.Responsibilities of this section include:Rename documents to the new standard.Index documents.Remove documents from the index.Retrieve metrics or entire table exports from the database.Search indexed and unindexed documents (admin-only search).Any changes made to this section of the tool will be done in the gui.py (front-end changes), cdi.py (back-end changes), or search_core.py (searching changes) files.Front-EndChanges that you make to the end may include any of the following:Changing the colors, fonts, or other purely aesthetic elements of the GUIAdding or updating elements on the GUIAdding a new page to the toolChanging Colors, Fonts, Etc.These kinds of display options are handled via CSS when using the PySide module. All CSS files are located in the assets folder, you can find the paths to these files in the search_core.py file. Unless a class specifically sets its style sheet, it will use the style sheet of its parent. In general, use the following guidelines:STYLE_SHEET - Styling for most elements located below the header and above the footer. Main styling file.HEADER_SHEET - Styling for most, if not all, elements located in the header of the tool.FOOTER_SHEET - Styling for most, if not all, elements located in the footer of the tool.These are the constant variable names for each sheet. Again, to see which actual files these point to look for their definitions near the top of the search_core.py file.First, understand which element you are editing and which style sheet it is currently using or inheriting. Next, understand the syntax of CSS. Finally, make changes to the CSS file where needed and then restart the tool to see if changes took effect. It may be necessary to explicitly state which style sheet an element should use if changes are not being reflected (See setStyleSheet method for PySide widgets).Adding or Updating Elements on the GUIBefore making any changes it is very important that you understand the structure of the Python code as well as the PySide module, please read the source code and related resources linked in the section \ref{182811}. The basic process for adding a GUI element is as follows:Choose the widget you wish to add (see PySide documentation for widget definitions).Optional: If you plan to use this widget multiple times with specififc default settings, you may want to create a subclass of the widget and set the defaults there (See Default12Label, InfoEntry, or Default12Button in search_core.py).Create and place widgets within the init_ui function of the relevant class. For example, if you want to add a widget to the Rename Kapala Images page, you would add the widget in the init_ui function of the RenameKapalaImages class. With PySide you have to create the element/widget and then place it in a valid layout. make sure you understand the PySide method for doing these things (see source code examples and PySide documentation).If the functionality you wish to add requires interfacing with the database, create a function within the back-end file (cdi.py) that does the database work and returns the necessary data to your function located in the front-end file (gui.py). The front-end file should not be directly accessing the database.Updating can simply be done by finding the element you wish to update and changing the code as you need. If the element is defined in another class (Default12Label, etc) you should make changes to the class definition rather than changing it after you create an instance of it, otherwise the changes will only apply to the single instance you changed.Adding a New Page to the ToolIf you need to add a new set of functions to the tool that require their own page (A page is defined as a layout that you can switch to via the navigation bar in the header) you can do so using the following steps.Define a new class (See RenameKapalaImages for a full example). The following should be considered when defining the class:Define a function called __init__. See source code examples and PySide documentation for ideas of what this should look like.If you need to access the database on this page, make sure to pass in the cdi variable when initializing the class.Define a function called init_ui and call it at the end of your __init__ function. Here you will create instances of widgets, create a layout, place widgets on the layout, and connect events (button presses, etc.) to functions you define later. Define functions to perform operations when connected events trigger. These functions should call other functions located in the back-end file if the database needs to be accessed.Initialize a variable for your new class in the __init__ function by setting it to none, follow the existing pattern (lowercase class name with underscores between words).Create an instance of the new class in the init_ui function of the Dashboard class (under comment label Create widgets for the StackedLayout).Add the new widget to the stacked layout. Ordering is important so make sure to add it after all existing widgets (under comment 'Add widgets to the StackedLayout' make sure to place it after the rest of the widgets).Update the change_layout function in the Dashboard class but adding the new page to the layout_dict. Follow the existing pattern, the number in the list is the index at which the layout was added. This is why the ordering is important.Update the __init__ function of the NavMenuButton class by adding your new page to the menu. Follow the existing pattern and add your code directly below the last page to be added before the separator. Keep in mind, the first string here is what the menu will display, however the string in the set_current function call should be the same as the string you added in step 5 so that it will find the key in the dictionary when called.You should now be able to navigate to the new page.Back-EndThe back-end will likely not be updated unless you are making a change to the front-end. This is because all back-end functions exist to handle requests from the front-end. These requests are first handled by a function in the CDIApp class, then by the DatabaseManager class. This was done to keep data preparation and document management separate from database interaction. Any data prep or document management should be done within the CDIApp class and its functions, while interaction with the database should be done in the DatabaseManager class and its functions. If your function doesn't require either of these to be done, it can simply call the DatabaseManager class function and return whatever it returns.In most cases, changes to the back end will follow this format:Some function from the front-end is requesting to read or write data to the database.Create a function in the CDIApp class that can be called by the front end. In this function, perform any data cleanup or formatting needed on passed in arguments. Depending on the return value of the DatabaseManager class's function you may also want to make changes to documents. For example moving documents into the index, removing documents from the index, renaming documents, etc. See source code for examples.Create a function in the DatabaseManager class that can be called by the function in the CDIApp class. In this function, perform the read/write on the database and return values as needed.Public Index Search PortionThis portion of the tool is very similar to the Admin portion, with a few small differences. You will follow the same guidelines listed above for making changes, however the front-end changes will be made to the index_search.py and search_core.py files, while the back-end changes will still be made to the cdi.py file. It is very important that you realize changing the search_core.py and cdi.py files will directly impact the admin portion of the tool! Be very careful when making changes to the public index search as to not create unwanted changes to the admin search. However, it is usually not a problem as both searches should behave identically.SQLite3 Database StructureThe database has a very simple structure. We have two index tables, one for Oregon and one for Washington, as well as two LR2000 tables, one for Status and one for Case Recordation. These LR2000 tables are limited to fields that we need, but can be updated if additional fields are needed in the future. It is also important to note that these tables pull from Frank Lahm's monthly LR2000 export tables, and should be updated monthly directly after them.CaseRecContains selected fields for all cases in Case Recordation. Used to search by serial number and action date, and to display additional data within the search results.CaseRec Table DefinitionName SQLite3 Type Description CaseId TEXT LR2000 Case Id SerialNumber TEXT LR2000 Serial Number Full CaseGroup TEXT LR2000 Case Group SystemId TEXT LR2000 System Id (ST or CR) ActionDate TEXT LR2000 Action Date for the documentStatusContains selected fields for all cases in Status. Used to search by serial number and action date, and to display additional data within the search results.Status Table DefinitionName SQLite3 Type Description CaseId TEXT LR2000 Case Id SerialNumber TEXT LR2000 Serial Number Full CaseGroup TEXT LR2000 Case Group SystemId TEXT LR2000 System Id (ST or CR) ActionDate TEXT LR2000 Action Date for the documentOregonIndexHolds image indexing data for Oregon as well as the linking field Case Id in order to link to the LR2000 tables and pull additional information.OregonIndex Table DefinitionName SQLite3 Type Description ImageName TEXT Name of the indexed image CaseId TEXT LR2000 Case Id linked to the document IndexDate TEXT Date on which the document was indexedWashingtonIndexHolds image indexing data for Washington as well as the linking field Case Id in order to link to the LR2000 tables and pull additional information.WashingtonIndex Table DefinitionName SQLite3 Type Description ImageName TEXT Name of the indexed image CaseId TEXT LR2000 Case Id linked to the document IndexDate TEXT Date on which the document was indexedOregonUnindexedHolds the image name for all unindexed images in OregonOregonUnindexedName SQLite3 Type Description ImageName TEXT Name of the unindexed imageWashingtonUnindexedHolds the image name for all unindexed images in WashingtonWashingtonUnindexed Table DefinitionName SQLite3 Type Description ImageName TEXT Name of the unindexed imageTroubleshootingDuring regular use of the tool users may run into errors or crashes (outside of the errors displayed in the export text file). When these errors occur, it is very likely that an error message will be printed out to the console. Make it clear to the users that if they want to report an issue they will need to include this error message as it is very useful in determining where in the code something went wrong. It is also very useful to have both a production and development version of the tool, that way you can test solutions to bugs in a development environment before releasing updates to the user.

General Issues/FeedbackFix database locking.Get IDLE working again.Tool RequestsCombine multiple geometry scripts into a single script (tools meeting with BLM).Incorporating WARP images into CDI search.CDI Index and Search ToolDifferentiate state buttons in search tool. Switch to vertical alignment (Oregon on top).Fix renaming page terminology.Flag duplicates or other issues in the search form.Include Casetype Text in search results.Speed up searching.Open .jpg faster.Kapala Process TrackerWhen QC is finished, export the metrics on that specific case automatically.Set up queries that prompt for specific case data (PLSSID, Job Id).Time tracker shows Twp Identifier instead of Job Id.Time tracker shows the amount of time currently submitted on the selected case (Sectioned by stage).Time tracker allows time submitted on cases already completed.Time tracker only allows time to be submitted for the stage that it is in.Metrics results page show unique PLSSIDs in queries involving the PLSSID.Metrics should be case-insensitiveSlow load times when switching pages.Add a stage before ready to check out so that the case can be entered in before actually being able to be worked on.Streamline adding new MTPs.Validate against township table, PLSSID generator.HI Upload and Word TemplateUnique PLSSIDs text file generated upon GIS export.Access help message on file dialogs.Export directly to access database. Auto export csv for word option.Prompt during word template upload to select survey status (Surveyed, Partially Surveyed, Unsurveyed). MTPTWP?HI Conversion Tracker (Milwaukie Tracker)A way to update flags after submitting. (DONE)Update table after checking in a page. (DONE)Upload single pages that have been re-scanned and sent back to us.Wyoming Upload DatabaseUpload database tool, speed up preparation of pages for upload. (DONE)Show page number on edit form. (DONE)Uploading multiple pages at once. (DONE)Kapala In-Line QCPopulate the PLSSID if it is null. Check the HI, if all PLSSIDs are the same then pull it from there. If there is more than one PLSSID then do not populate the PLSSID and raise an error.Re-conveyance mtpsurfrts are null causing error when it shouldn't. IF srtrt1 is All (930) then mtpsurfrts should be null.M&B comparing to non-base rows is bringing in false errors. Update Sub vs Surf check to exclude sub-specific or surf-specific features.

OverviewThe Status to Case Rec Conversion Tool is a small program that takes legal descriptions from the LR2000 Status database and converts them to an acceptable format for upload into the LR2000 Case Rec database. The tool has not been packaged into an executable or installer due to restrictions on such software on the BLM network, therefore some additional software is required to run the tool.Required Files and SoftwarePython (2.7.x) - This tool was developed using python 2.7.13 but any 2.7.x version should work. Only standard python modules were used in development.sttocrV1.py (Current Version: 1.0.0) - The tool itself is entirely contained within this file (minus the sqlite3 database).convert.sqlite - While this file is required, it will be created and formatted by the python file if not found when the tool starts.Useful ResourcesPython - https://docs.python.org/2/SQLite3 - https://docs.python.org/2/library/sqlite3.htmlSQLite - https://www.sqlite.org/docs.htmlTkinter - https://docs.python.org/2/library/tkinter.htmlPEP 8 - https://www.python.org/dev/peps/pep-0008/Python File StructureThe python file is split into three distinct parts, the first being the header section containing file encoding data, file description, and module imports. Next, in the second section we have the database handler code that does most of the actual work and interacts with the convert.sqlite database file. This section is entirely contained in the ConvertDB class. The last section of the code includes a few simple classes that define the default behavior of certain GUI elements and the main GUI handler class ConvertGUI which handles all interaction with the user. We'll refer to these last two sections as the back-end and front-end respectively.Back-EndIn this section we'll discuss the responsibilities of the back-end and give a few examples of ways to change or add functionality to the conversion process.The responsibilities of this section are as follows:Handle requests from the front-end.Interact with the sqlite database (uploads, exports, updates, etc.).Follow conversion rules to convert Status legal descriptions to the Case Rec format.For specific information on which functions handle which requests as well as detailed explanations of code blocks, see the source code. However, we will go over a few of the more important changes you may want/need to make with the tool here.Adding or Updating Conversion RulesThis work is done completely within the getexportdata function within the ConvertDB class. This function takes a serial number as its only parameter (besides the implicitly passed self parameter). Given this serial number, the function queries the database and pulls all legal descriptions for that serial number from the database. Then we run each legal description through a set of rules to convert it to Case Rec format. These rules are all applied within the main for loop, to add a rule simply add a new block of code in this section that runs the rule on whichever survey types necessary. Each rule is identified and described using a short block comment directly above the block of code that implements the rule. You should add this block whenever you add a new rule. To update a rule, find the rule that applies to the survey type you wish to update, then carefully read the block to understand what the rule is doing. Once you understand how the rule works, simply make the necessary changes or additions to the block.Front-EndThe responsibilities of this section are as follows:Display an interface for users to request specific functions be performed.Request data from the back-end to display to the user.Take the result of the conversion process and export it into a properly formatted text file (SFTA accepted format).Again, much of this functionality is well documented in the source code for you to inspect and update. We'll talk about some of the more important changes or updates you may want/need to make to the user interface here.Adding or Updating Elements on the GUIThe GUI is built using a simple (somewhat limited) default Python module called Tkinter. See the documentation linked in the Useful Resources section of this document for more information on how to properly use the module. The basic process for adding a GUI element is as follows:Choose the widget you wish to add.Optional: If you plan to use this widget multiple times with specific default settings, you may want to create a subclass of the widget and set the defaults there (See ConLabel, ConButton, ConEntry classes).Create and place the widgets within the initgui function of the ConvertGUI class. If necessary connect these widgets to a function here as well.If the functionality you wish to add requires interfacing with the database, create a function within the ConvertDB class that does the database work and returns the necessary data to your function located in the ConvertGUI class. The function within the ConvertGUI class should not be accessing the database.SQLite3 Database StructureThe database has a very simple structure. The only table we use in the database is called Status. This table contains the legal descriptions that are initially uploaded from the Status LR2000 export file. We simply use this database to pull legal descriptions from during the conversion process. The table definition can be found below.Status Table DefinitionName SQLite3 Type Description LegalID TEXT Auto-generated unique identifier.  AdminSt TEXT 2-character Admin State GeoSt TEXT 2-character Geo State SerialNum TEXT Full Serial Number MeridianCd TEXT 2-digit Meridian Code Township TEXT 5-character Township (ex. 0010S) Range TEXT 5-character Range (ex. 0150E) Section TEXT 3-digit Section SurType TEXT Survey Type SurNum TEXT Survey Number SurSuff TEXT Survey Suffix Aliquot TEXT Aliquot DescriptionTroubleshootingDuring regular use of the tool users may run into errors or crashes (outside of the errors displayed in the export text file). When these errors occur, it is very likely that an error message will be printed out to the console. Make it clear to the users that if they want to report an issue they will need to include this error message as it is very useful in determining where in the code something went wrong. It is also very useful to have both a production and development version of the tool, that way you can test solutions to bugs in a development environment before releasing updates to the user.

OverviewThe BLM needs specific data to be able to Quality Assure our index. To provide them with this data we need to pull data from both our index and LR2000. This short guide will cover this data gathering and database setup process.Required FilesAll the files required in this process are currently located on the G drive in the following folder - G:\oso\Realty\CDI\KapalaPrep\08_BLM Ready for QA\99_BLM QA Toolscdi_table_for_qa.py - Creates and populates the intermediary database with data from the Kapala Index and LR2000 tables. Joins this data and exports it to a .csv file.qa_db.sqlite - Intermediary database used for the purposes of gathering and joining the Index and LR2000 data.image_data.csv - Export file containing the joined data needed for BLM QA.IndexQA.accdb - The Index QA database where BLM will perform their Quality Assurance.Updating the Data PullThe data being pulled from the Index and LR2000 tables is going to be different every time as we need to only pull data for the newest images to be indexed. To update how this data is pulled we need to make a quick change to the cdi_table_for_qa.py file.In the CONSTANTS section of the file we need to update the DATE_CONSTRAINT constant. The value we want to place in this string is the date on which we last created CDI data for BLM QA. The last time we created this data we pulled information on all images that were indexed after our previous DATE_CONSTRAINT. Since we do not want to pull these images again we need to set the DATE_CONSTRAINT to that same day we pulled that data so that only newly indexed images will be included. Remember, this date must be in the YYYY-MM-DD format or it will not pull the correct data.Updating the QA DatabaseOnce you have updated the data pull, you can run the cdi_table_for_qa.py file (using PyScripter or Python IDLE through citrix) and it will produce the image_data.csv file (WARNING: this process may take a minute or two to pull all the necessary data together. Do not interrupt this process by closing PyScripter or Python IDLE or you may end up with incomplete data. Read the PyScripter console or Python IDLE shell to determine when the tool is finished). Once you have the image_data.csv file you will want to upload it into the IndexQA.accdb Access Database.To do so, follow the steps below:Run the Reset Image Data query.Open the IndexQA.accdb file, click on the External Data tab near the top left, and within the Import & Link section select Text File.On the first page of the wizard, press Browse. Navigate to and select the image_data.csv file. Make sure Append a copy of the records to the table is selected and the table listed in the drop down is Image Data. Press Next.On the second page, select Delimited. Press Next.On the third page, select Comma and make sure the Text Qualifier is the quote character ("). Press Next.On the fourth page, press Finish.Now that the data is imported, we need to make a couple more updates before we can pass it on for BLM QA. Follow the secondary steps below:Run the GetUniqueCases query. This will populate the UniqueCases table and allow cases to be selected via the drop-down on the QA form.Run the UpdatePaths query. This will create hyperlinks to every image so they can be easily opened.TroubleshootingDuring regular use, the user may run into certain issues. Below is a list of commonly experienced problems and how to go about fixing them.Form Buttons Don't Appear to be WorkingThis is usually a trust center issue. Follow the steps below to solve it.Using the Microsoft Access Menu go to File > Options.The pop-up options window will have a menu on the left side. At the bottom of this menu is the label Trust Center. Click this.On this page, press the Trust Center Settings button.The Trust Center window will now open. On this window’s menu, click on the Trusted Locations label.On this page, press the Add new location button.On the new window, press the Browse button.Browse to the folder in which the HIUD Access Database is located and select it. To be safe, you can also check the ‘Subfolders of this location are also trusted’ checkbox before pressing the OK button.Press OK on the remaining two windows and then close and reopen the entire database to refresh the trust settings.The form buttons should now work.

IntroductionThe Historical Index Conversion Tracker (HICT) was developed to help track the conversion of scanned Historical Index pages into readable and editable electronic copies. During early work on this project Kapala IT utilized several time-consuming and error prone processes. As the project continued we were able to identify the causes of these issues being heavily related to the distributing and tracking of work among multiple employees. The HICT helps to alleviate these problems by presenting an easy to use and secure application that tracks every useful piece of information related to the project.System RequirementsPython 2.7 - Any version 2.7.x is acceptable. This tool has not been tested with Python 3 and therefore cannot be guaranteed to work with it. (must be added to PATH)Flask - Any version 0.12.x is acceptable.Node.js - Necessary to run the startup commands from the console. (must be added to PATH)Electron - Allows us to build a desktop application using web languages.Useful DocumentsNode.js - https://nodejs.org/api/Electron - https://electron.atom.io/docs/HTML - https://www.w3schools.com/Tags/Javascript - https://www.w3schools.com/Jsref/Python - https://docs.python.org/2/SQLite3 - https://docs.python.org/2/library/sqlite3.htmlSQLite - https://www.sqlite.org/docs.htmlPEP 8 - https://www.python.org/dev/peps/pep-0008/Flask - http://flask.pocoo.org/docs/0.12/OverviewThe HICT is a desktop application was built using Electron for the front-end and Python for the back-end.The front-end consists of a main process and five different renderer processes. The main process runs behind the scenes to keep the application functioning, while the renderer processes display a user interface and handle interactions between the user and the back-end. Before updating any files make sure to understand what each file type is used for:.html – Static contents of the web page..js – Handles events triggered by the user (button clicks, key presses, etc.), updates the html contents, communicates with the back-end,.css – Styling information, how the page looks, what fonts, font sizes, colors, margins, etc. are used.There are other files within the project that are not one of the above types, but it is unlikely that you will ever need to update these files. You should have a decent grasp on the basics of each language and the main source code before attempting to make any changes.The back-end is coded purely in python using the sqlite3 module for database operations..py – Receives requests from the front-end via flask module, interacts with the database via sqlite3 module, returns requested data or errors to the front-end..sqlite – Database file, operated on using python files, this file is not used directly.You should never have to edit the SQLite file directly, and to do so would require a third-party program that can read and edit SQLite database files. If you need to make changes to the database, make sure to carefully examine the structure of the database and python code before doing so. Once you have, research the sqlite3 library and the SQLite language in general to understand how to use queries, transactions, etc.Front-EndIn this section, we’ll go over in detail the structure of each element of the front-end. The main process is contained within one file, while each of the renderer processes is split between two files.Main ProcessFiles: main.jsResponsibilities: Handles the life-cycle of the application. How to start, when to clean up. Provides methods to the renderer processes.Login Renderer ProcessFiles: login.html, login.jsResponsibilities: Displays the login page and sends log in requests.Corrections Renderer ProcessFiles: index.html, index.jsResponsibilities: Displays the corrections page and sends check-in and check-out requests.Quality Control Renderer ProcessFiles: quality_control.html, quality_control.jsResponsibilities: Displays the Quality Control page, sends qc approval requests, and allows for the package files to be viewed and updated.Admin Renderer ProcessFiles: admin.html, admin.jsResponsibilities: Displays the admin page and sends requests to import packages and create new notifications for the corrections page.Back-EndThe back end consists of two files, a python file that uses the flask module to handle requests from the front-end, and an SQLite3 database file that the python file accesses to read or write data.PythonFiles: database_handler.pyResponsibilities: Handles all requests to read and write data from the front end. Handles all interactions with the database.Required Non-Standard Python Modules: flaskFor an in depth look at which python function handles which request and how, take a look at the source code and its documentation. In this section we'll quickly go over the general structure in this file.Each python function in this file that handles a request from the front end has a decorator above the function declaration. This decorator will look something like this:@app.route("/some_name/<some_variable>/<path:some_path>"def some_name(some_variable, some_path):Here is a quick breakdown of this [email protected] - Flask routes the requests from the front end to a specific python function using this decorator.some_name - This is the first part of the request, it acts as an identifier for this function, it can be anything you want but generally it should be the name of the python function or something similar.some_variable/path:some_path - After you've added the name to the request you may want to also pass some data, using this syntax you can easily pass this data using python parameters. For example, if you have <some_variable> in your decorator string you can then have some_variable as a parameter with the value that was passed in during the request. These parameters are ordered based on the ordering in the request. That means in our above example we first get some_variable and then some_path so our python function would order the parameters respectively.Knowing this, finding which function handles a specific request is quite simple. All you need to do is search for the request name (some_name in our example above).The general structure of each python function is the same. They follow the following format:receive request and any parameters.interface with files or the database.return response in JSON string format (usually).SQLite3Files: hict.sqliteResponsibilities: Stores all data related to the tracking of the conversion process.General formatting rules:Dates use the 'yyyy-mm-dd hh:mm:ss' format. ex. '2017-01-27 12:05:38'If multiple flags apply to a single page, they will be seperated by a vertical bar character. ex. 'Difficult to read|Wyoming verify data'Below you will find detailed explanations of the tables included in this database.correctionsHolds information related to the correction status of each package.corrections Table DefinitionName SQLite3 Type Description PackageId TEXT The unique identifier attached to each package. Username TEXT The username of the user who checked out the package. CheckOutDate TEXT The date on which the user checked out the package. CheckInDate TEXT The date on which the user checked in the package.credentialsHolds user credentials and other important information.credentials Table DefinitionName SQLite3 Type Description username TEXT The user's username. firstname TEXT The user's first name. lastname TEXT The user's last name. email TEXT The user's email address. userclass INTEGER The user's user class. Determines permissions. password TEXT The user's password. Stored as a hashed string.Restricted fieldsuserclass: follows the guidelines below. userclass ValuesValue User Level Page Rights 0 Corrections Corrections 1 QC Corrections and QC 2 Admin Corrections, QC, and Adminpassword: stored as a hashed value. See the login function within the python source code for more details.error_pagesHolds page data for all pages with errors that stop them from going any further in the conversion process.error_pages Table DefinitionName SQLite3 Type Description PageId TEXT The unique identifier attached to each page. PackageId TEXT The unique identifier attached to each package. Flags TEXT The error flags attached to the page.Restricted fieldsFlags: Any combination of the following flags separated by the vertical bar character.Wyoming verify dataDifficult to readError on PDFPage already in excelPage needs rescanimportsHolds information related to the import of each package.imports Table DefinitionName SQLite3 Type Description PackageId TEXT The unique identifier attached to each package. Username TEXT The username of the admin user that uploaded the package. Date TEXT The date on which the admin user uploaded the package.notificationsHolds information on all notifications.notifications Table DefinitionName SQLite3 Type Description NotificationId INTEGER Auto-generated integer used to uniquely identify a notification. Message TEXT The message displayed in the notification. Username TEXT The username of the admin user who added the notification. Date TEXT The date on which the admin user added the notification.packagesHolds general tracking information on each package not related to a specific stage.packages Table DefinitionName SQLite3 Type Description PackageId TEXT The unique identifier attached to each package. PLSSID TEXT The PLSSID attached to the package. NumPages INTEGER The number of pages in the package.pagesHolds page data for all pages besides those that have been removed from the conversion process due to severe errors.pages Table DefinitionName SQLite3 Type Description PageId TEXT The unique identifier attached to each page. PackageId TEXT The unique identifier attached to each package. Flags TEXT The error flags, if any, attached to the pageRestricted fieldsFlags: Any combination of the following flags separated by the vertical bar character.Wyoming verify dataDifficult to readError on PDFPage already in excelPage needs rescanqcHolds information related to the QC status of each package.qc Table DefinitionName SQLite3 Type Description PackageId TEXT The unique identifier attached to each package. Username TEXT The username of the QC or admin user who QC'd and approved the package. Date TEXT The date on which the QC or admin user approved the package.sqlite_sequenceAuto-generated and updated table that keeps track of the ASSIGNEDIDS auto increment variable.sqlite_sequence Table DefinitionName SQLite3 Type Description name TEXT The name of the table whose sequence is being tracked. seq INTEGER The current value of the sequence.Restricted fieldsDo not edit this table. It is automatically handled by SQLite.

BackgroundThe OCR conversion process was started with the end goal of having all historical index data in a single location that could be easily manipulated in order to accomplish many goals. These goals include mass data cleanup or standard updates, quick and easy formatting of data into a presentable product, and semi-automated updating of Case Recordation legal descriptions. It is also important to note that this process eliminates the need for paper copies of every historical index page. Thus, we developed the Historical Index Upload Database (HIUD) to take over the responsibility of these goals after the pages have been converted and gone through the Historical Index Conversion Tracker process.OverviewThe HIUD is a desktop application was built using Microsoft Access (2007-2010).While the HICT is a tool that can be utilized for any state work, the upload database must have specific elements tailored to the state whose data is being worked with. Therefore, we have slightly different versions of the HIUD for different states. Because of this I will cover the basic structure of the HIUD, what you can expect to stay the same and what you can expect to change between each states version.Common ElementsIn this section, we’ll take a look at the main components in the HIUD that will not change between any version. Please make yourself familiar with Microsoft Access, VBA, and the structure of this database before attempting to make any changes. All of the forms in this database use VBA, not macros, to handle user interaction.Main Menu (Form)When you open the tool, you will be greeted with the Main Menu. This simple menu allows the user three options. Upload Township Table, Export Township HI, and Edit Master HI. Let’s take a look at what is going on behind the scenes with this menu.Upload Township Table Prompts the user to select a formatted text file containing HI data (see the user guide if you are interested in what the formatted data should look like). Validates that it is the correct file type, if so passes the file path to the Get Upload Info form (See the section on this form for more information).VBA Handler: UploadTable_Click()Source: Form_Main Menu, line 346Export Township HI Prompts the user to select a text file containing the PLSSIDs (one per line) of the townships that they wish to export. Then prompts the user to select a directory to save the exported .csv files to. Attempts to find symbols that will not properly be displayed in the word document and replace them with plain text (see the word template technical documentation for more information).VBA Handler: PrintHI_Click()Source: Form_Main Menu, line 94Edit Master HI Minimizes the main menu and opens the Edit Master HI form.VBA Handler: EditMasterHI_Click()Source: Form_Main Menu, line 51Get Upload Info (Form)The Get Upload Info form is opened after the user has pressed the Upload Township Table button on the main menu and selected a properly formatted text file to upload. The purpose of this intermediary form is the gather some extra data regarding the data being uploaded.The user is prompted to enter in the PLSSID (A PLSSID generator is available to make sure no mistakes are made) and the page number of the document they are uploading. Once they have done so, the only real functionality on this form we need to talk about is the OK button.OK (Submit Form) Validates that the PLSSID is 15 characters and possibly has added validation based on the state (see the State Specific Elements section for more information). Validates that the page number entered in is an integer. If both are validated, calls the UploadSelection function (See the Standard Module header in this section). passing the PLSSID and page number as well as the path to the selected file.VBA Handler: OKButton_Click()Source: Form_Get Upload Info, line 9HI Entry Form (Form)This form will display the uploaded data in a familiar historical index formatted form. The user can make changes to the data in this form, and they form validates and updates data per rules set in place. To update validation rules (cells turn red if validation fails) see the Standard Module functions that begin with Validate. To update auto-update rules (fields like acres update from 160 to 160.00) see the Form_Edit Found Entries Form functions that end with _Exit.This form provides us with three useful buttons, Show Errors, Show All, and Submit Entries.Show ErrorsChanges the HI Entry Form’s record source to the query named FindInvalidRows. This query runs each row through the ValidateAll function in the Standard Module. This function attempts to validate all fields with validation rules and returns true if any field fails validation. This means that the query will only show records that have failed at least one validation.VBA Handler: ShowErrors_Click()Source: Form_HI Entry Form, line 94Show All Changes the HI Entry Form’s record source back to the original record source, 01_SelectedHIEntries. This can be used after the Show Errors button has been pressed to make the form display all uploaded records again.VBA Handler: ShowAll_Click()Source: Form_HI Entry Form, line 88Submit Entries Deletes all records in the Master HI where the PLSSID and page match the upload data. This is to avoid two versions of an HI page existing in the database. The CreateLinks function from the Standard Module is called to populate the linking fields (ACTDATE_ALL, KOE_ALL, MTPNUM_ALL) thus linking base rows to the non-base rows above them. Then, the SendToMaster query is called to append the uploaded data to the Master HI table (99_MasterHI). Finally, the FixLinks function from the Standard Module is called to update all link fields in the Master HI.VBA Handler: SubmitEntriesButton_Click()Source: Form_HI Entry Form, line 100Edit Master HI (Form)This form is where much of the action takes place. In this form, you can search, edit, and export data. Unlike the main menu’s export function, this form can export legal descriptions for any serial number in the data. Most of this forms VBA back-end consists of legal description export code, but we’ll focus on the user interaction code that is most subject to small changes.Filter Records Filters the form’s record source based on the data entered into the fields above this button (Serial Number, PLSSID, Section, Dates). This function is automatically called whenever one of the fields is changed. Importantly, while the form may only display some records, it will keep a list of all records within the affected townships. For example, if a serial number is selected, it may only show one or two records within the form. However, for each PLSSID that serial number is in, this function will also remember to keep all records in each of these PLSSIDs in the query Select for Editing. The reason this is important is that when we go to save edits to the master, an entire PLSSIDs data must be deleted and replaced to avoid primary key conflicts. To do this, we need to save the data for the entire township (or townships) we are editing in the background, even if we only display a few rows.VBA Handler: FilterButton_Click()Source: Form_Edit Master HI, line 1375Clear Filter Clears all filters on the edit form and returns it to the starting state.VBA Handler: ClearFilterButton_Click()Source: Form_Edit Master HI, line 1305Insert Row Above Inserts some number of rows (based on the amount in the row count field right below the button) above the selected row into the 99_EditMasterHI table (the sub form’s record source).VBA Handler: InsertRowButton_Click()Source: Form_Edit Master HI, line 1599Save EditsMakes sure a PLSSID is entered in for every record being edited, then saves the changes by deleting all effected townships from the Master HI and then calling the Save Edits query to append the updated data for these townships to the Master HI.MISSING VBA HANDLERSource: Form_Edit Master HI, line 1688Calculate and Export Legal DescriptionCalculates the current standing legal description for a case using data from the Master HI. This data is then exported into a formatted text file (SFTA Upload Format) that can be used to upload legal descriptions into Case Recordation. The code is lengthy and complex. For a better understanding of how legal calculation works take a look at the source code and comments.MISSING VBA HANDLERSource: Form_Edit Master HI, line 30Standard ModuleThis module holds functions that do not directly relate to any form but can be called from any of the forms. Think of these as global functions.Default Directory (Constant)A constant variable that holds a default directory to open at when using file dialogues.VBA Handler: DEFAULT_DIRSource: Standard Module, line 2Delete Table Records Deletes all records from the table whose name matches the passed in string.VBA Handler: DeleteRecords(tableName)Source: Standard Module, line 3Upload Text File to Table Validates and transfers the text file data to a temporary table 00_UploadedHIEntries using the import specification “HI Import Specification” and then takes the required fields and appends them to the 01_SelectedHIEntries table. During this append, many fields are formatted using various format functions (see later in this section). Replaces common fractions with their symbols for a better viewing experience while editing. This function may also perform further cleanup operations via queries depending on State preferences (see the State Specific Elements section for more information).VBA Handler: UploadSelection(PLSSID, pageNum, selectedFile)Source: Standard Module, line 11Create Base Row Links Traverses backwards through the 01_SelectedHIEntries table (the upload table) and attempts to find base rows. Base rows are defined as rows where at least one of the ACTDATE, KOE, or MTPNUM fields is populated. Non-base rows are defined as rows where all the above fields are not populated. Once a base row is found, we save the values for these fields into temporary variables. As we continue our search upwards, if we find a non-base row we will save the most recently visited base row’s data for these fields into the corresponding ACTDATE_ALL, KOE_ALL, and MTPNUM_ALL fields. This will create a link between the base row and the non-base rows above it. Once we find another base row we update the temporary variables with values from this new base row and continue upward through the table until all non-base rows are linked with a base row.VBA Handler: CreateLinks()Source: Standard Module, line 105Fix Base Row Links Works similarly to the CreateLinks function but is used after editing has been done to the master HI. This attempts to update the links for all townships that were updating during the latest edit. For a more detailed description about what these links are and how they are create see the CreateLinks function above.VBA Handler: FixLinks()Source: Standard Module, line 200Validate All FieldsWrapper function used by the FindInvalidRows query that calls all the specific validation functions with arguments passed in from the query. Returns 1 if any of the validation functions fail and 0 if they all pass.VBA Handler: ValidateAll(lotStr, aliquotStr, dateStr, acresStr, secStr)Source: Standard Module, line 310Validate Lots Passes if the lots value is left blank or contains only numbers, spaces, commas, and dashes. Fails if any other characters are found.VBA Handler: ValidateLots(lotstring)Source: Standard Module, line 330Validate Aliquots Passes if all aliquot fields contain either a capital X or are left blank. Fails if any other characters are found.VBA Handler: ValidateAliquot(aliquotString)Source: Standard Module, line 363Validate Date Passes if the date is left blank or is determined valid using the IsDate VBA function. Fails otherwise.VBA Handler: ValidateDate(actdateString)Source: Standard Module, line 379Validate Acres Passes if the acres value is left blank or is numeric AND does not contain dashes or commas. Fails otherwise.VBA Handler: ValidateAcres(acresString)Source: Standard Module, line 406Validate Section Passes if the section value is left blank or contains an integer from 1 to 36, inclusive. Fails otherwise.VBA Handler: ValidateSection(sectionString)Source: Standard Module, line 428Format Input Runs the query FormatEntries to automatically apply formatting rules to uploaded data.VBA Handler: FormatInput()Source: Standard Module, line 464Format Date Attempts to format a date using the mm/dd/yyyy pattern.VBA Handler: FormatDate(dateString)Source: Standard Module, line 474Format Aliquot Changes any lower case x to an uppercase X which is the standard for aliquot values.VBA Handler: FormatAliquot(aliquotString)Source: Standard Module, line 509Format Acres Attempts to format an acres value using two decimal places (unless more already exist).VBA Handler: FormatAcres(acresString)Source: Standard Module, line 525State Specific ElementsDifferent states sometimes have different standards, validation rules, and obviously different data. This means that each upload database must be slightly tailored to match these needs. In this section, we will talk about where these differences can arise and what they would entail changing.Township ValidationCertain states want to make sure that the PLSSID typed in on the Get Upload Info form matches an existing township in their state. This requires a full list of townships from that state. This data is usually saved in an optional table. For example, California’s upload database has a table named 99_CaliforniaTownships that is used to validate the PLSSIDs entered in. This data must be comprehensive enough to recreate the entire PLSSID. This validation is usually done within the Get Upload Info form’s VBA back-end.Cleanup QueriesEach state will likely have their own set of cleanup queries. These are queries that attempt to find common errors in the Master HI and fix them in mass. Common examples include fixing spacing around semicolons, removing semicolons at the end of certain fields, etc. Some of these queries do not make changes themselves, but instead show the user rows that are in error according to specific standards. These queries help to identify errors that cannot be fixed in mass. For example, showing rows in which the Action Date is out of order, or showing rows with invalid or missing LR2000 serial numbers.These queries will often have accompanying functions located in the Standard Module.TroubleshootingDuring regular use, the user may run into certain issues. Below is a list of commonly experienced problems and how to go about fixing them.Form Buttons Don't Appear to be WorkingThis is usually a trust center issue. Follow the steps below to solve it.Using the Microsoft Access Menu go to File > Options.The pop-up options window will have a menu on the left side. At the bottom of this menu is the label Trust Center. Click this.On this page, press the Trust Center Settings button.The Trust Center window will now open. On this window’s menu, click on the Trusted Locations label.On this page, press the Add new location button.On the new window, press the Browse button.Browse to the folder in which the HIUD Access Database is located and select it. To be safe, you can also check the ‘Subfolders of this location are also trusted’ checkbox before pressing the OK button.Press OK on the remaining two windows and then close and reopen the entire database to refresh the trust settings.The form buttons should now work.

BackgroundWhile the main goal of the historical index conversion to electronic data was a central database with all HI pages, we still wanted to provide a way to present this data in a more readable format. The HI database provides a way to export a historical index for an entire township at once, however this data is saved in a .csv format which is not easily readable nor does it comply with BLM regulations for a final product. Therefore, the need for a word template arose. The goal of this template is to have a bare bones document that follows formatting restrictions for historical indices listed in the 1275 manual. This document would allow for data from the exported .csv files to be uploaded into it, updating the formatting of the data and creating a publicly viewable version in word or pdf.OverviewThe HIET is a word template built using Microsoft Word (2007-2010). The main function used to upload data into the template is a mail merge. This tool has its limitations; however, it was determined to be the best possible way of quickly and easily uploading data into the template without needing to make further changes.Like the HICT, this export template must have specific elements tailored to the state whose data is being worked with. Therefore, we have slightly different versions of the HIET for different states. Because of this I will cover the basic structure of the HIET, what you can expect to stay the same and what you can expect to change between each states version.Common ElementsIn this section, we’ll look at the main components in the HIET that will not change between any version. Please make yourself familiar with Microsoft Word and VBA before attempting to make any changes.Template FormattingThis section may be subject to change in the future, however, currently all states who are participating in the HI conversion process have opted to use the Oregon HI format, and therefore all templates will have the same formatting rules as Oregon’s historical indices.Mail Merge Macro (VBA)All states will use a macro that points to VBA code to run the mail merge function for importing data into the template. Much of this process will not change between states, I’ll talk about those elements here.Initiating MacroThis is the sub that is called when you first run the macro. Its job is to prompt the user for a list of .csv files that they wish to convert into word documents following the template formatting rules. Once at least one file has been selected, the ConvertData function is called.VBA Handler: ConvertToWord()Source: ThisDocument, line 99Convert DataThis sub procedure was creating using the Record Macro option within Microsoft Word’s developer tab. The output has been slightly altered to allow additional work to be done. After running the Mail Merge, we also call another sub FixData before saving and closing the merged document.VBA Handler: ConvertData(filePath)Source: ThisDocument, line 1Fixing and Updating DataThe cleanup done in this function is limited to issues with the Mail Merge as well as updating page headers with the township information. This function does not update data according to standards or fix common issues found in data. These updates should be made in the Access Database so that this part of the process can be as simple, streamlined, and universal as possible. Instead this function performs operations like cleaning up fractions and div tags which occur due to the Access export process. To update the header, the function GetHeader (see the state specific elements section) is called.VBA Handler: FixData(filePath)Source: ThisDocument, line 27Generating a New File nameUses the upload file name (which should be the PLSSID if the file was exported from the HIUD) to generate the new file name (which should also be the PLSSID with lowercase township and range direction letters). Uses the FolderFromPath and GetFilenameFromPath functions that were found online.VBA Handler: GetFileName(filePath)Source: ThisDocument, line 214State Specific ElementsIn this section, we will cover any elements which are subject to change based on the state whose data we are processing.Generating Page Header TextUsing the uploaded file name, attempts to generate the Historical Index header text. Currently, states participating in the HI conversion process have different versions of this sub procedure as the state and meridian values are handled lazily. In the future, this function will be updated to pull this information from the upload file name as well. At that point this would no longer be a state specific element.VBA Handler: GetHeader(filePath)Source: ThisDocument, line 133TroubleshootingDuring regular use, the user may run into certain issues. Below is a list of commonly experienced problems and how to go about fixing them.Macro doesn't appear to do anythingThis is usually a trust center issue. Follow the steps below to solve it.Using the Microsoft Word Menu go to File > Options.The pop-up options window will have a menu on the left side. At the bottom of this menu is the label Trust Center. Click this.On this page, press the Trust Center Settings button.The Trust Center window will now open. On this window’s menu, click on the Trusted Locations label.On this page, press the Add new location button.On the new window, press the Browse button.Browse to the folder in which the HIUD Access Database is located and select it. To be safe, you can also check the ‘Subfolders of this location are also trusted’ checkbox before pressing the OK button.Press OK on the remaining two windows and then close and reopen the entire database to refresh the trust settings.The form buttons should now work.

IntroductionThe Kapala Process Tracker is a tool, developed by Kapala IT, to help increase project efficiency by introducing automated tracking for various types of work. Tracking all work in a single system helps our cartographers keep track of what they have done, how long it took them, and what they have available to do next. It also provides useful and easy to produce metrics for various contacts, making cost and time estimations much more accurate for future contracts. The tool was made flexible so that as new contracts are acquired, new types of work can easily be added to the Kapala Process Tracker without adding much more complexity to the system. This document will take a more technical look at the tool and as such is a useful reference if you plan on making any changes or updates.System RequirementsPython 2.7 - Any version 2.7.x is acceptable. This tool has not been tested with Python 3 and therefore cannot be guaranteed to work with it.PySide - A Python module that does not come as part of the default install. PySide is used to create a GUI for the tool. Required Filesgui.py - The launch file. Controls the GUI and interactions between the user and the back-end.kpt.py - The database handler. Handles requests from the front-end by reading and writing data to or from the database.kptdb.sqlite - The database file. Holds all tracking information as well as user credentials.Important Note: All files must be in the same directory for the tool to run properly. Relative paths are used to avoid hard-cording paths which rely on network drives to be mapped a specific way.Useful DocumentsPython - https://docs.python.org/2/SQLite3 - https://docs.python.org/2/library/sqlite3.htmlSQLite - https://www.sqlite.org/docs.htmlPySide - https://srinikom.github.io/pyside-docs/PEP 8 - https://www.python.org/dev/peps/pep-0008/Front-End GUI File (gui.py)gui.py is the central file in our application. It is used to launch the tool, display a user interface, and handle interactions between the user and the back-end. If you need to make changes to the interface, pass data from the user to the back-end, or display data from the back-end to the user, this is the file you will edit. Keep in mind that nothing added to this file should have specific knowledge of the back-end structure, as that will cause issues if the database being used is ever changed. Any work that requires specific knowledge of the database structure should be done in the back-end database handler file.Below we will go into all sections of the file and what responsibilities each one has.File HeaderThis section contains environment and encoding data, module imports, file descriptions, and global constants. Here you can import new packages, update the file description, change constant file paths, or add new global constants that can be used throughout the file.Source: gui.py, lines 1-28General Use WidgetsIn this section we define many PySide widgets that are not specific to any other specific section of the tool. These widgets may be used in multiple different sections throughout the tool, or may be abstract base classes that be can inherit from to create more defined widgets. Each widget is defined using a class and may contain its own data, functions, and widgets. If you can't find a widget you would expect to find in another section, this is where you will find it.Source: gui.py, lines 29-1186Navigation BarThis short section defines the navigation related widgets within the tool.Source: gui.py, lines 1187-1278Login PageHere we have defined widgets that make up the small log in form upon opening the tool.Source: gui.py, lines 1279-1440Job Notation PageAnything related to the display and functionality of the Job Notation page will be in this section.Source: gui.py, lines 1441-1848MTP Construction PageAnything related to the display and functionality of the MTP Construction page will be in this section.Source: gui.py, lines 1849-2025On Hold PageAnything related to the display and functionality of the On Hold page will be in this section.Source: gui.py, lines 2026-2289Metrics PageIn this section we define all widgets used on the Metrics page.Source: gui.py, lines 2290-3708Time TrackerAnything related to the display and functionality of the Time Tracker will be in this section.Source: gui.py, lines 3709-4028Report IssueThis section is currently unused as issues are usually emailed directly to IT staff  with an accompanying screenshot. Within this section are widgets related to reporting an issue from within the tool itself.Source: gui.py, lines 4029-4081Dashboard and MainThis section contains the main Dashboard widget that is displayed upon logging in. It also defines the main python process that is run when the tool is started.Source: gui.py, lines 4082-4182Back-End Database Handler File (kpt.py)kpt.py serves the purpose of handling requests to read or write information to the database from the front-end. Similar to the gui.py file not know the inner workings of this file, kpt.py does not know the structure of the gui.py file. This is to ensure that if needed, the front end could be switched out without needed to change much code within this file. If you want to read from or write to the database, this is where you will do it.Again, we will go into the different sections of this file and their responsibilities below.File HeaderThis section contains environment and encoding data, module imports, file descriptions, and the start of the Database Manager class definition. Here you can import new packages or update the file description.Source: kpt.py, lines 1-38General Use FunctionsThis section contains functions that handle requests from the front-end that don't fall into a specific category.Source: kpt.py, lines 39-199Case Work FunctionsIn this section we have many functions that handle requests related to the case work cycle. This includes adding, checking out, releasing, and completing cases.Source: kpt.py, lines 200-423Hold FunctionsHere we have all functions that handle requests related to the hold status of a case.Source: kpt.py, lines 424-533Rework FunctionsHere we have all functions that handle requests related to the rework status of a case.Source: kpt.py, lines 534-600Job Specific FunctionsThis section holds all functions that handle requests specifically related to Job Notations.Source: kpt.py, lines 601-672MTP Specific FunctionsThis section holds all functions that handle requests specifically related to MTP Constructions.Source: kpt.py, lines 673-728User Related FunctionsIn this section we have all functions that handle requests related to the user.Source: kpt.py, lines 729-767Database File (kptdb.sqlite)AGGINFOThis table tracks Job Notation specific data on jobs that are part of an aggregate. This is necessary because the AGGTRACKING table only tracks data related to the entire aggregate, and therefore does not track things like Job Id, Serial Number, etc.AGGINFO table definitionName SQLite3 Type Description AGGID TEXT Auto-generated id that uniquely identifies an aggregate. JOBID TEXT The Job Notation's Job id SERIAL TEXT The Job Notation's Serial Number PLSSIDS TEXT The PLSSID(s) that the Job Notation affects. JOBTYPE TEXT The type of Job Notation. CORPFLAG TEXT Any Corporate Flag attached to the Job Notation.AGGTRACKINGThis table tracks important information related to the overall tracking of an aggregate job notation.AGGTracking Table DefinitionName SQLite3 Type Description AGGID TEXT Auto-generated id that uniquely identifies an aggregate. PRODUCTIONCARTO TEXT The last name of the cartographer who completed the production work. PRODUCTIONDATE TEXT The date on which the production work was completed. QCDATE TEXT The date on which the QC was completed. QADATE TEXT The date on which the QA was completed. LOADDATE TEXT The date on which the GIS data was uploaded into the master. PUBLISHDATE TEXT The date on which the Job Notation was published.ASSIGNEDIDSThis table generates unique integers that are used to create an AGGID (Aggregate Id).ASSIGNEDIDS Table DefinitionName SQLite3 Type Description ID INTEGER, PRIMARY KEY Auto-generated integer that is used to create an AGGID NAME TEXT The type of case we are assigning this ID to.CREDENTIALSThis table holds information on all users that are registered to use the Kapala Process Tracker.CREDENTIALS Table DefinitionName SQLite3 Type Description USERNAME TEXT The user's username. PASSWORD TEXTThe user's password. Stored as a hashed string EMAIL TEXT The user's email address. FULLNAME TEXT The user's full name. USERRIGHTS TEXT The rights level of a user. Determines what they can do within the tool.JOBONHOLDThis table holds information related to the hold status of any job notation put on hold.JOBONHOLD Table DefinitionName SQLite3 Type Description JOBID TEXT The Job Notation's Job ID INITCARTO TEXT The username of the cartographer who put the case on hold. INITSTAGE TEXT The stage during which the case was put on hold. INITDATE TEXT The date on which the case was put on hold. HOLDMESSAGE TEXT The message explaining why the case was put on hold. ASSIGNEDCARTO TEXT Deprecated. FIXMESSAGE TEXT The message explaining why the case was taken off hold. COMPDATE TEXT The date on which the case was taken off hold.JOBREWORKThis table holds information related to the rework status of any job notation sent back for rework.JOBREWORK Table DefinitionNAME SQLite3 Type Description JOBID TEXT The Job Notation's Job Id ORIGCARTO TEXT The username of the cartographer who completed the production work. INITCARTO TEXT The username of the cartographer who sent the case back for rework. INITSTAGE TEXT The stage during which the case was sent back for rework. INITDATE TEXT The date on which the case was sent back for rework. MESSAGE TEXT The message explaining why the case was sent back for rework. COMPDATE TEXT The date on which the rework was completed.JOBTRACKINGThis table holds information related to the overall tracking of a single job notation.JOBTRACKING Table DefinitionName SQLite3 Type Description JOBID TEXT The Job Notation's Job Id SERIAL TEXT The Job Notation's Serial Number PLSSIDS TEXT The PLSSID(s) that the Job Notation affects. JOBTYPE TEXT The type of Job Notation CORPFLAG TEXT The corporate flags, if any, attached to the case. PRODUCTIONCARTO TEXT The username of the cartographer who completed the production work. PRODUCTIONDATE TEXT The date on which the production work was completed. QCDATE TEXT The date on which the QC was completed. QADATE TEXT The date on which the QA was completed. LOADDATE TEXT The date on which the GIS data was uploaded into the master. PUBLISHDATE TEXT The date on which the Job Notation was published.JOBWORKINGThis table holds information related to the current status of a single job notation.JOBWORKING Table DefinitionName SQLite3 Type Description JOBID TEXT The Job Notation's Job Id STAGE TEXT The current stage that the case is in. CARTO TEXT The username of the cartographer currently working on the case.LOCKEDThis table hods information on which PLSSIDs are currently locked and by which job notation.LOCKED Table DefinitionName SQLite3 Type Description PLSSID TEXT The PLSSID that is currently locked. JOBID TEXT The Job Id of the Job Notation that is causing this lock.MTPONHOLDThis table holds information related to the hold status of any MTP construction put on hold.MTPONHOLD Table DefinitionName SQLite3 Type Description JOBID TEXT The MTP's Job Id INITCARTO TEXT The username of the cartographer who put the case on hold. INITSTAGE TEXT The stage during which the case was put on hold. INITDATE TEXT The date on which the case was put on hold. HOLDMESSAGE TEXT The message explaining why the case was put on hold. ASSIGNEDCARTO TEXT Deprecated. FIXMESSAGE TEXT The message explaining why the case was taken off hold. COMPDATE TEXT The date on which the case was taken off hold.MTPREWORKThis table holds information related to the hold status of any MTP construction sent back for rework.MTPREWORK Table DefinitionName SQLite3 Type Description JOBID TEXT The MTP's Job Id ORIGCARTO TEXT The username of the cartographer who completed the production work. INITCARTO TEXT The username of the cartographer who sent the case back for rework. INITSTAGE TEXT The stage during which the case was sent back for rework. INITDATE TEXT The date on which the case was sent back for rework. MESSAGE TEXT The message explaining why the case was sent back for rework. COMPDATE TEXT The date on which the rework was completed.MTPTRACKINGThis table holds information related to the overall tracking of an MTP construction.MTPTRACKING Table DefinitionName SQLite3 Type Description JOBID TEXT The MTP's Job Id PLSSID TEXT The PLSSID(s) that the MTP affects. PRODUCTIONCARTO TEXT The username of the cartographer who completed the production work. PRODUCTIONDATE TEXT The date on which the production work was completed. QCDATE TEXT The date on which the QC was completed. QADATE TEXT The date on which the QA was completed. LOADDATE TEXT The date on which the GIS data was uploaded into the master. PUBLISHDATE TEXT The date on which the Job Notation was published.MTPWORKINGThis table holds information related to the current status of an MTP construction.MTPWORKING Table DefinitionName SQLite3 Type Description JOBID TEXT The MTP's Job Id STAGE TEXT The current stage that the case is in. CARTO TEXT The username of the cartographer currently working on the case.SAVEDQUERIESThis table holds information used to re-execute queries saved by users.SAVEDQUERIES Table DefinitionName SQLite3 Type Description QUERYNAME TEXT The name of the query. QUERYHEADERS TEXT All field headers that the query brought in. QUERYSQL TEXT The SQL statement that the query executes. USERNAME TEXT The username of the cartographer who saved the query.TIMETRACKERThis table holds all time tracking data submitted by the user.TIMETRACKER Table DefinitionName SQLite3 Type Description JOBID TEXT The Job Id of the case. CARTO TEXT The username of the cartographer who recorded time. STAGE TEXT The stage during which time was recorded. HOURS TEXT The amount of hours recorded. WORKDATE TEXT The date on which time was recorded.sqlite_sequenceAuto-generated and updated table that keeps track of the ASSIGNEDIDS auto increment variable.sqlite_sequence Table DefinitionName SQLite3 Type Description name The name of the table whose sequence is being tracked. seq The value of the current auto increment variable.