Customising the ESdat database |
  
|
Customising the ESdat database |
|
ESdat is designed to work closely with the underlying database in the management and manipulation of your data. It is possible that at some point users who are capable in the use of databases (Access or SQL Server) will want to add additional tables, or write additional queries/views to extract data through ESdat or otherwise.
In Access, this can be achieved by directly opening the underlying database in Access; and any user familiar with usage of Access can customise the database.
As Access based projects are in a seperate Access database per project the changes will apply only to the project being customised.
*In Access, changes to database structures, or table properties may not be available while the database is open in ESdat. This can be overcome by ensuring the table you wish to change is not being used in ESdat (ie by selecting a different Data Type in the ESdat Interface), or for a very small number of core database changes, closing the database in ESdat.
SQL Server
In SQL Server this is achieved through SSMS (SQL Server Management Studio), or similar.
Any changes made to the ESdat SQL Server database will affect and be available to all ESdat users, as this is an enterprise level solution.
Given the potential for the whole system to be compromised if changes are made that subsequently cause issues, it is recommended that only staff qualified in the development of SQL Server database schemas be engaged to perform this task, and that procedures be implemented to allow for a development, test and production environments. If this is not feasible, EScIS can be engaged to accommodate additional data tables or relationships in the ESdat database schema. EScIS are also happy to provide less formal advice, and may request to be compensated for time, depending on the extent of the advice provided.
As certain tables, fields and queries/views (objects) are critical to the operation of ESdat it is not supported that users delete or rename any database objects.
Additional fields (columns) can be added to the current ESdat database tables as required. Additional fields should be limited to types already present in ESdat tables, and set to "Not Required" (meaning these fields can be left blank if necessary).
Additional database tables and relationships to existing tables can be added to the database. It is recommended this be completed by someone with the skills and knowledge appropriate to the level of additional tables being added. Referential relationships against existing ESdat tables is also supported.
Custom fields or tables will automatically appear on ESdat Excel Import Templates, which can be used for importing this data. For import from other interfaces some further accomodation of these customisations on that interface may be required.
All key outputs through ESdat, and all data shown through the user interface is presented via database Queries (or Views). This includes all outputs to tables, GIS, Graphing, and the extensive analysis and reporting functionality that ESdat provides.
Users are able to customise and add additional database quries (views) to the database and analyse the outputs from these views using all the ESdat functionality that applies to the standard views.
There is no restriction on the type of queries (views) which can be created . All of the functionality available through database platform can be used to create your required query/view. Action queries (Make Table, Update, Append, Delete) cannot be run through ESdat.
Custom Queries/Views can either be given the appropiate prefix to appear as an option under an existing DataType Button (ie SChem for Soil Chemistry), or a new DataType button can be used (in ESdat select Setup - Data Types).
The Query/View must have a field giving x and y coordinate fields in order for the data locations to be shown on the mapping interfaces.
The example below shows a modification of the query WaterLevel_AHD query so that only one result (the average) is returned per day per Well. The original query can be left as is if preferred, and the modification given a new name.
1. Open the Query in Design View. If you get presented with a box of text select View - Design View from the Access menu's. The screen should look like below:
2. Click the 
Totals Button
3. Modify the design as follows:
Use the DateValue formula to remove the time portion of the Date_Time and rename as Date as shown below. The Group By instruction will now group the results by each date rather than each date_time record.
In the Water_Level Column change the Group By to Avg.
Preview the results and save as a new query. If you wish to make these modifications yourself you should research Access functions and Queries.
