SPARCRequest Wiki
SPARCFulfillment Klok Report Data Dictionary (SPARCFulfillment v3.6.1)
Implemented in May 2017 with SPARCFulfillment v2.0.5 release, Klok is an external time tracking application that interfaces with SPARCFulfillment as an XML formatted report imported into SPARCFulfillment through mapped data. Service providers using Klok to track their time can be set up to use the Klok-SPARCFulfillment interface to document Non-Clinical (one-time fee) service fulfillments.
The uploaded Klok report (.xml file) contains <entries>, <projects>, and <people> elements that map to SPARC study a service request through its components, with a final entry of the fulfilled work task. There is not a unique Klok ID for each service’s component. Because the service ID can be matched to multiple components (1:N match), the service ID is the Klok project code and final mapping happens with the component name. There must be an exact component name match in SPARC and Klok.
This report provides information about the SPARCFulfillment data included on a Klok Export report after proof/import uploads.
Data Dictionary
Proof Report - draft upload done to ensure data will successfully import. Done prior to import upload (see below)
Column Name | Description | Database Name | Database Source | Type |
|---|---|---|---|---|
Ssr_id | System generated service request id from SPARCRequest | sparc_request.protocols.id “-” sparc.request.sub_services_req.ssr_id | Within Klok, the parent (main) project code is the Service Request ID (SPARC ID - service request). Service request ID is automatically generated by SPARC application. | BIGINT(20) |
Reason | Status of the report upload in fulfillment (e.g., success or failure) and Fulfillment ID # | fulfillments.id | Status source is its success in matching Klok and SPARC Fulfillment data and importing the information. Fulfillment ID: Automatically generated by SPARC Fulfillment Application | INT(11) |
created_at | Date & time fulfillment line item generated on the Klok report | klok_entries.created_at | Generated in Klok; obtained from Klok XML import | TIMESTAMP |
project_id | Unique id for projects and subprojects as a hierarchal structure. The project = main folder within Klok, generally the SPARC (study) service request # entered in Klok. The subproject = subfolders in Klok, generally a service’s component for the service request # entered in Klok. The service ID is entered as the Project Code within the component folder. The subproject ‘rolls up’ to the project. | klok_projects.project_id klok_entries.project_id | Generated in Klok; obtained from Klok XML import | INT(11) |
resource_id
| Unique id for person performing the service | klok_people.resource id klok_entries.resource_id | Generated in Klok; obtained from Klok XML import | INT(11) |
rate | Service rate | klok_entries.rate | Obtained from Klok XML upload; rates are also included in SPARC db | INT(11) |
date | Date service was fulfilled | klok_entries.date | Obtained from Klok XML upload | DATE |
start_time_stamp_formatted | Time service started in AM/PM format | klok_entries.start_time_stamp_formatted | Generated in Klok; obtained from Klok XML import | VARCHAR(255) |
start_time_stamp | Date and time service started in M/D/YYYY 24HR Time format | klok_entries.start_time_stamp | Entered in Klok; obtained from Klok XML import | TIMESTAMP |
entry_id | Unique ID for each fulfillment entry | klok_entries_entry.id | Generated in Klok; obtained from Klok XML import | INT(11) |
duration | Amount of time elapsed from start and end times in seconds x 1,000 | klok_entries_duration | Generated in Klok; obtained from Klok XML import | INT(11) |
submission_id | ID associated with the date/time the Klok entry was logged | klok_entries.submission_id | Generated in Klok; obtained from Klok XML import | INT(11) |
device_id | N/A | N/A | N/A | N/A |
comments | Fulfillment notes entered in Klok | klok_entries_comments | Entered in Klok; obtained from Klok XML import | TEXT |
end_time_stamp_formatted | Time service ended in AM/PM format | klok_entries.end_time_stamp_formatted | Generated in Klok; obtained from Klok XML import | VARCHAR(255) |
end_time_stamp | Date and time service ended in M/D/YYYY 24HR Time format | klok_entries.end_time_stamp | Entered in Klok; obtained from Klok XML import | TIMESTAMP |
roll_up_to | Parent project to which the fulfillment is associated. The parent project is coded as the service request ID in Klok. | klok_entries.rollup_to | Generated in Klok; obtained from Klok XML import | INT(11) |
enabled | Indicates whether the Klok application checks for duplicate entries. | klok_entries.enabled | Generated in Klok; obtained from Klok XMP import. | TINYINT(1) |
Import Report - import upload that updates Fulfillment data
Column Name | Description | Database Name | Database Source | Type |
|---|---|---|---|---|
ssr_id | System generated service request id from SPARCRequest | sparc_request.protocols.id “-” sparc.request.sub_services_req.ssr_id | Within Klok, the parent (main) project code is the Service Request ID (SPARC ID - service request). Service request ID is automatically generated by SPARC application. | BIGINT(20) |
reason | Status of the report upload in fulfillment (e.g., success or failure) and Fulfillment ID # | fulfillments.id | Status source is its success in matching Klok and SPARC Fulfillment data and importing the information. Fulfillment ID: Automatically generated by SPARC Fulfillment Application | INT(11) |
created_at | Date & time fulfillment line item generated on the Klok report | klok_entries.created_at | Generated in Klok; obtained from Klok XML import | TIMESTAMP |
project_id | Unique id for projects and subprojects as a hierarchal structure. The project = main folder within Klok, generally the SPARC (study) service request # entered in Klok. The subproject = subfolders in Klok, generally a service’s component for the service request # entered in Klok. The service ID is entered as the Project Code within the component folder. The subproject ‘rolls up’ to the project. | klok_projects.project_id klok_entries.project_id | Generated in Klok; obtained from Klok XML import | INT(11) |
resource_id
| Unique id for person performing the service | klok_people.resource id klok_entries.resource_id | Generated in Klok; obtained from Klok XML import | INT(11) |
rate | Service rate | klok_entries.rate | Obtained from Klok XML upload; rates are also included in SPARC db | INT(11) |
date | Date service was fulfilled | klok_entries.date | Obtained from Klok XML upload | DATE |
start_time_stamp_formatted | Time service started in AM/PM format | klok_entries.start_time_stamp_formatted | Generated in Klok; obtained from Klok XML import | VARCHAR(255) |
start_time_stamp | Date and time service started in M/D/YYYY 24HR Time format | klok_entries.start_time_stamp | Entered in Klok; obtained from Klok XML import | TIMESTAMP |
entry_id | Unique ID for each fulfillment entry | klok_entries_entry.id | Generated in Klok; obtained from Klok XML import | INT(11) |
duration | Amount of time elapsed from start and end times in seconds x 1,000 | klok_entries_duration | Generated in Klok; obtained from Klok XML import | INT(11) |
submission_id | ID associated with the date/time the Klok entry was logged | klok_entries.submission_id | Generated in Klok; obtained from Klok XML import | INT(11) |
device_id | N/A | N/A | N/A | N/A |
comments | Fulfillment notes entered in Klok | klok_entries_comments | Entered in Klok; obtained from Klok XML import | TEXT |
end_time_stamp_formatted | Time service ended in AM/PM format | klok_entries.end_time_stamp_formatted | Generated in Klok; obtained from Klok XML import | VARCHAR(255) |
end_time_stamp | Date and time service ended in M/D/YYYY 24HR Time format | klok_entries.end_time_stamp | Entered in Klok; obtained from Klok XML import | TIMESTAMP |
roll_up_to | Parent project to which the fulfillment is associated. The parent project is coded as the service request ID in Klok. | klok_entries.rollup_to | Generated in Klok; obtained from Klok XML import | INT(11) |
enabled | Indicates whether the Klok application checks for duplicate entries. | klok_entries.enabled | Generated in Klok; obtained from Klok XMP import. | TINYINT(1) |
Sample Import Report Output
Related content
Copyright © 2011-2025 MUSC Foundation for Research Development