Migrating Queries
Migration Vault supports the ingestion of queries in the following statuses: Open, Answered, and Candidate. You can load your source’s Queries into Vault CDMS via a CSV file.
Preparing Query Data
You use a CSV file (“queries.csv”) to map your Queries to their target objects in your source data.
Use this template to create your CSV file:
Required Columns
Your CSV file must include the following columns:
| Column | Description | InForm™ | Target Property |
|---|---|---|---|
| STUDYID | Input your study’s Study ID |
SITEMNEMONIC or SITEID (IRV_CUR_QUERY) | Study on Query, Query Message, and Query Binding |
| SUBJID | Input the unique Subject identifier |
SUBJECTID (IRV_CUR_QUERY) | Subject on Query, Query Message, and Query Binding |
| SITENUM | Input the Site Number for the subject’s Site |
SITEMNEMONIC or SITEID (IRV_CUR_QUERY) | Site on Query, Query Message, and Query Binding |
| EGROUP | Input the unique identifier for the Event Group containing the Query |
VISITMNEMONIC or VISITID (IRV_CUR_QUERY) | Event Group on Event |
| EGROUPSEQ | Input the number identifier for the sequence/instance of the Event Group containing the query. If the target Event Group isn’t repeating, enter “1”. |
VISITINDEX or VISITIDX (IRV_CUR_QUERY) | N/A |
| EVENT | Input the unique identiifer for the Event containing the Query |
VISITMNEMONIC or VISITID (IRV_CUR_QUERY) | ID on Event |
| FORM | Input the unique identifier for the Form containing the Query |
FORMMNEMONIC or FORMID (IRV_CUR_QUERY) | ID on Form |
| FSEQ | Input the number identifier for the sequence/instance of the Form containing the query. If the target Form isn’t repeating, enter “1”. |
FORMINDEX or FORMORDER (IRV_CUR_QUERY) | Form Sequence on Form |
| IGSEQ | Input the number identifier for the sequence/instance of the Item Group containing the query. If the target Item Group isn’t repeating, enter “1”. |
ITEMSETINDEX (IRV_CUR_QUERY) | Item Group Sequence on Item Group 2 |
| ITEM | Input the unique identifier for the Item containing the query. This is expected to match the item’s variable name (verbatim |
ITEMID (IRV_CUR_QUERY) | ID on Item 2 |
| QUERY_ID | Input a unique identifier for the Query. The maximum length is 50 characters. This is expected to match against the unique query identifier value from the source system. |
QUERYID (IRV_QUERY_STATE_CHANGES) | N/A |
| QUERY_MESSAGE | Input the Query Message text. This can be the candidate message text, the open message text, or the answered message text. THe maximum length is 500 characters. |
QUERYTEXT (IRV_QUERY_STATE_CHANGES) | Message on Query Message |
| QUERY_STATUS | Input the following number values to represent the query status:
|
QUERYSTATE (IRV_QUERY_STATE_CHANGES) | Status on Query and Query Message |
| MESSAGE_DATE | Input the date and time when the query was last updated. Use UTC format. Don’t include a timezone (the system will use the site’s timezone). |
QUERYSTATETIME (IRV_QUERY_STATE_CHANGES) | Message Date on Query Message |
| MESSAGE_SEQUENCE | Input the number identifier for the sequence of the Query Message. This represents the order in which the query messages were created. All sequences for a specific Query begin at “1”, with the following appearing in sequential order. |
QSTATEORDER (IRV_QUERY_STATE_CHANGES) | N/A |
| REFERENCE | This column is required even if you aren’t using configurable identity. If you aren’t using this feature, include these column headers but leave the rows blank. Configurable identities specify multiple columns/values. Enter each of these values for the row’s record, separated by a pipe ( |
Specified in REFERENCE_TYPE | N/A |
| REFERENCE_TYPE | This column is required even if you aren’t using configurable identity. If you aren’t using this feature, include these column headers but leave the rows blank. Enter the type (object) of reference you’re providing for the record’s identity. The following types are supported:
|
N/A | N/A |
Query Statuses
Migration Vault is able to migrate Queries into CDMS with the Open (1) or Answered (2) statuses. If you include any Queries with the Candidate (0) status, Migration Vault migrates those Queries as being in the Open status.
Closed Queries: In the current release, Migration Vault doesn’t support the migration of Closed (3) or Deleted (4) queries.
Multiple Messages per Query
One query may have more than one Query Message, or comment, associated with it. To load these into CDMS with Migration Vault, you add them to the CSV file as new query rows, but use incrementing numbers for MESSAGE_SEQUENCE. When the CSV file contains more than one row with the same value for QUERY_ID, Vault uses the one with “1” for MESSAGE_SEQUENCE first, then adds those with subsequence sequence numbers to that query as additional messages (comments).
Migration Vault will not migrate Queries in the Answered status unless there is a corresponding row for the Query in the Open status.
If there is a gap in the message sequence numbers, Migration Vault uses the MESSAGE_DATE to identify the order.
Example CSV
STUDYID,SUBJID,SITENUM,EGROUP,EGROUPSEQ,EVENT,FORM,FSEQ,IGSEQ,ITEM,QUERY_ID,QUERY_MESSAGE,QUERY_STATUS,MESSAGE_DATE,MESSAGE_SEQUENCE
Cholecap,0101-0001,0101,eg_screening,1,screening_visit,demographics,1,subject_dob,0101-0001_demo_1,Subject's date of birth is above average,1,2022-07-27T22:50:35Z,1
Cholecap,0101-0001,0101,eg_screening,1,screening_visit,demographics,1,subject_dob,0101-0001_demo_1,Verified date is correct,2,2022-07-27T23:14:46Z,2
Migrating Queries
Migration Order: Queries must be migrated into CDMS after study data is loaded but before execution states (Frozen or Locked) are created. You can migrate queries after review states (SDV/DMR), signature states, and ILB states.
View Results of a Query Load
Migrated Queries in CDMS
After a successful load, migrated Queries are visible in Vault CDMS. Query Messages and query-related audit trail entries are attributed to the CDMS Migration User.
CDMS users with the appropriate permissions can now answer, close, and re-open these queries in the same way as queries originating in Vault CDMS.