Framework Database Tools
- Matthew Garrett
Framework database tools provide the ability to run extracurricular updates to Framework system and job data.
This may be required when Framework data has been updated externally outside of the Framework suite or when statistics need to be recalculated.
Some of the tools operate on job-specific data, others on internal system data. When running a tool for job-specific data (see below) criteria must be supplied to select the job(s) to be updated.
The following criteria options are available to select the job(s) to run the selected Database Tool on:
The following options are available in the Apply To combo box to select the criteria to select jobs with:
The Region/Division combo allows the addition of Region/Dvision criteria to be included in the job filtering.
If the selection of Apply To criteria (see above) requires further specification, this is chosen in the Criteria and Context combos. E.g for 'All jobs by major stage':
The Context combo allows further specification of criteria when needed, e.g when 'All jobs by minor stage' is selected in the Apply To combo box, the Criteria combo allows selection of the major workflow stage (parent) with the Criteria combo allowing selection of the minor workflow stage (child):
The following table describes the database tools available:
# | Database Tool Name | Description | Availability? | Tool Criteria | When is it needed? |
|---|---|---|---|---|---|
1 | Add zero records to all tables | This tool ensures all tables in the Framework database have a 'zero record' (i.e a record with a PKID of 0) in them, as required for the Framework system internally to link data. | Available | None - not job specific. Applies to all Framework tables. | When troubleshooting data issues, such as when data is missing or not displayed. (Typically only when instructed by Insula.) |
2 | Compile Construction Chart Data | Creates/updates the construction chart data for the selected job(s). For legacy (non-logistics) construction program jobs only. | Available | Job(s) specified by tool criteria | When legacy (pre-logistics) jobs require a construciton chart. |
3 | Compile Labour Allocation Entity Statistics (Carpenter) | Compiles labour allocation statistics for carpenter entities for the selected job(s). For legacy (non-logistics) construction program jobs only. | Available | Job(s) specified by tool criteria | When legacy (pre-logistics) jobs require labour allocation stats (for carpenters) |
4 | Compile Labour Allocation Entity Statistics (Standard) | Compiles labour allocation statistics for standard entities for the selected job(s). For legacy (non-logistics) construction program jobs only. | Available | Job(s) specified by tool criteria | When legacy (pre-logistics) jobs require labour allocation stats. |
5 | Create Construction Programme | Creates a construction program for the selected job(s). For legacy (non-logistics) construction program jobs only. | Available | Job(s) specified by tool criteria | When legacy (pre-logistics) jobs require a construction program to be applied. |
6 | Forecast Labour Requirements | Forecasts labour requirements for the selected job(s). For legacy (non-logistics) construction program jobs only. | Available | Job(s) specified by tool criteria | When legacy (pre-logistics) jobs require forecast labour allocation stats. |
7 | Link Purchase Orders | Links purchase orders and logistics items for the selected job(s) based on cost centre codes. | Available | Job(s) specified by tool criteria | When logistics orders on jobs are not linked to logistics items. This may be after orders have just been integrated from a 3rd party system, to prevent the need for supervisors and/or admin saff to perform the linking on a job by job basis. |
8 | Linked Files - Create Client Folders | Creates disk folders for the selected job(s) client based on Framework system configuration and job files system preferences. | Not available | Job(s) specified by tool criteria | Not available |
9 | Linked Files - Create Job Folders | Creates disk folders for the selected job(s) based on Framework system configuration and job files system preferences. | Available | Job(s) specified by tool criteria | When job(s) require a linked file folder structure to be created on disk. The folders may not have been created when the job(s) was initially created in Framework. |
10 | Linked Files - Create Land Folders (All Lands) | Creates disk folders for the selected job(s) land based on Framework system configuration and job files system preferences. | Not available | Job(s) specified by tool criteria | Not available |
11 | Linked Files - Create Purchase Order Folders | Creates disk folders for the selected job(s) purchase orders based on Framework system configuration and job files system preferences. | Not available | Job(s) specified by tool criteria | Not available |
12 | Permanently Delete Client (this cannot be undone) | Permanently delete the client(s) from the Framework database, for the client(s) specified by the tool criteria. Note: that this process is a permanent (physical) delete and cannot be undone. | Available | Job(s) specified by tool criteria | When a client MUST be deleted permanently from Framework. |
13 | Permanently Delete File (this cannot be undone) | Permanently delete the file(s) from the Framework database, for the file(s) specified by the tool criteria. Note: that this process is a permanent (physical) delete and cannot be undone. | Available | Job(s) specified by tool criteria | When a file MUST be deleted permanently from Framework. |
14 | Permanently Delete Job (this cannot be undone) | Permanently delete the job(s) from the Framework database, for the job(s) specified by the tool criteria. Note: that this process is a permanent (physical) delete and cannot be undone. | Available | Job(s) specified by tool criteria | When a job MUST be deleted permanently from Framework. |
15 | Recalculate Logistics Progress | Recalculates the individual logistics item activity progress, for each logistics item within the selected job(s) logistics programme. | Available | Job(s) specified by tool criteria | When logisitcs items have had partial progress ticked off but their % complete is not accurate. This may have been due to logistics item and call data syncing between updates from various other sites/sources. This is typically not required as the Framework logistics system regularly updates this information during normal operation. |
16 | Run Logistics MS-Access Triggers | Executes the Autoexec macro in the the Ms-Access database at \$Framework\Triggers\LogisticsTriggers.mdb | Available | None - not job specific. | Only required at specific legacy sites that have custom Ms-Access triggers implemented. |
17 | Update All Construction Details | Performs the following construction-related updates for the selected job(s):
| Available | Job(s) specified by tool criteria | When logistics and/or construction data needs to be completely updated based on all recent data updates/uploads. This may be used to bring all construction stats up to date for various reasons, e.g: for running reports |
18 | Update Construction Claims | Updates the construction claims for the selected job(s). | Available | Job(s) specified by tool criteria | When construction claims data needs to be completely updated based on all recent data updates/uploads. This may be used to bring all construction stats up to date for various reasons, e.g: for running reports |
19 | Update Construction Delays | Updates the construction delays for the selected job(s). | Available | Job(s) specified by tool criteria | When construction delays data needs to be completely updated based on all recent data updates/uploads. This may be used to bring all construction stats up to date for various reasons, e.g: for running reports |
20 | Update Construction History Notes | Updates the construction period history notes for the selected job(s). This tool copies the Operator and Supervisor notes from the current construction period to the construction record, as seen on some Construction Overview screens) | Available | Job(s) specified by tool criteria | When construction overview notes needs to be refrshed to the notes from the current construction period. This may be required when tablet sites have uploaded other notes or when the 'current' construction period has been updated to the next period where no notes exist. |
21 | Update Construction Progress | Recalculates the construction progress stats for the job(s) specified by the tool criteria. This tool recalculates stats across any 'dirty' construction periods for the selected job(s) i.e only for construction periods that have had activity recorded since the job's last progress update. | Available | Job(s) specified by tool criteria | When construction progress stats need to be completely updated based on all recent data updates/uploads. This may be used to bring all construction stats up to date for various reasons, e.g: if the preferenes for auto-progress updates is Disabled, or for running reports. |
22 | Update Construction Progress (All including non-dirty) | Recalculate the construction stats for the job(s) specified by the tool criteria. This tool recalculates stats across ALL construction periods for the selected job(s), regardless of whether or not there has been any recent activity recorded since the job's last progress update. | Available | Job(s) specified by tool criteria | When construction progress stats need to be completely updated based on all recent data updates/uploads. This may be used to bring all construction stats up to date for various reasons, e.g: if the preferenes for auto-progress updates is Disabled, or for running reports. |
23 | Update Job Status | Recalculates the job status for the selected job(s) based on data within each job. | Available | Job(s) specified by tool criteria | When the job(s) have an incorrect status - where the status inconsistent with the job data, e.g if the job data has been updated outside of the Framework suite without triggering the status update etc. |
24 | Update Logistics Item Completion Statistics | Updates progress for all completed logistics items for the select job(s). | Available | Job(s) specified by tool criteria | When logisitcs items have been call-completed but the item stats do not reflect this. This may have been due to logistics item and call data syncing between updates from various other sites/sources. This is typically not required as the Framework logistics system regularly updates this information during normal operation. |
25 | Update Maintenance Request Work Flow Stage | Updates the workflow stage for all maintenance requests for the selected job(s). | Available | Job(s) specified by tool criteria | When maintenance requests have inaccurate workflow stages according to the workflow dates on the requests. This may be required when logistics sites have updated and synced maintenance requests ir if requests have been updated outside of the Framework suite. |
26 | Update System Keys | Updates Framework's internal record of current Primary Key (PKID) values across all tables in the Framework database. This tool incorporates site-specific system keys as well as keys for the main Framework database, whereby remote sites (e.g logistics tablets) can reserve block ranges of Ids for exclusive record insertion in a table. | Available | None - not job specific. Applies to all Framework system keys. | When troubleshooting data issues, such as duplicate key runtime errors. This is required when data has been inserted into the Framework database outside of the Framework suite, e.g via custom queries or integration (Typically only when instructed by Insula.) |
27 | Update Total Number of Tender Appointments | Sets diary appointment links and diary item numbers for tender appointments for the selected job(s). This tool also clears presentation and current appointment links and counts for non-final tender documents. | Available | Job(s) specified by tool criteria | When the count of tender appointments is incorrect for the job. This may be required when tender appointments have changed and/or tender documents have been superceded. |
28 | Update Workflow Stage | Recalculates the workflow stage for the selected job(s). This tool executes the usp_updateWorkflowstages stored procedure for each selected job. | Available | Job(s) specified by tool criteria | When the job(s) have an incorrect workflow stage - where the stage inconsistent with the job data, e.g if the job data has been updated outside of the Framework suite without triggering the workflow stage update etc. |