Migrating Queries

EDC Migrator supports the ingestion of queries in the following statuses: Open, Answered, and Closed. You can load your source’s Queries into EDC via a CSV file.

Key Concepts

  • Scope and Limits: The Migrator supports queries at the Event and Item level. It does not support queries at the Subject, Study, Site, or Form level.
  • Migration Order: The Migrator processes queries in a specific order. You must migrate queries into EDC after you load the study data but before you create execution states (Freeze and Lock).
  • File Specifications: Each study has only one Queries CSV file.
  • Audit Trail: The Migrator does not migrate a query’s existing (pre-migration) audit trail.
  • Query Status: Each query has only one status. The status of the query’s last message determines the query’s overall status (e.g., Opened, Answered).
  • Multiple Queries: A single Item or Event can have multiple queries with an Open or Answered status, and a single query can have an unlimited number of messages, responses, or comments.
  • Qualifying Levels: Similar to attributes, you only qualify out to the level required. For an Event level query, you qualify to the event, and for an Item level query, you qualify to the item.

Preparing Query Data

The Migrator processes queries at the Event and Item levels, but it doesn’t verify that the corresponding events and items actually exist in your source data. If a query’s event or item is missing, the load still progresses through different stages, but the system doesn’t process the query.

Queries CSV

You use a UTF-8 encoded CSV file named “queries.csv” to map your Queries to their target objects in your source data. The name “queries” is a requirement for the CSV file.

Use this template to create your CSV file:

  Download the Template CSV

Required Columns

Your CSV file must include the following columns:

Column Description InForm™ Rave™ Target Property
STUDYID

Input your study’s Study ID

 SITEMNEMONIC or SITEID (IRV_CUR_QUERY) Project

Study on Query, Query Message, and Query Binding
SUBJID

Input the unique Subject identifier

 SUBJECTID (IRV_CUR_QUERY) Subject

Subject on Query, Query Message, and Query Binding
SITENUM

Input the Site Number for the subject’s Site

 SITEMNEMONIC or SITEID (IRV_CUR_QUERY) SiteNumber

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) InstanceName

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) InstanceRepeatNumber

N/A
EVENT

Input the unique identiifer for the Event containing the Query

 VISITMNEMONIC or VISITID (IRV_CUR_QUERY) FolderName

ID on Event
FORM

Input the unique identifier for the Form containing the Query

 FORMMNEMONIC or FORMID (IRV_CUR_QUERY) DataPageName

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) RecordPosition

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 columnName from the mapping YAML file).

 ITEMID (IRV_CUR_QUERY) ?

ID on Item 2
QUERY_ID

Input a unique identifier for the Query. The minimum length is one character and 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, the answered message text, or the closed 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:

  • “1” (Opened)
  • “2” (Answered)
  • “3” (Closed)
 QUERYSTATE (IRV_QUERY_STATE_CHANGES) TBD

Status on Query and Query Message
MESSAGE_DATE

Input the date and time when the query was last updated. Use ISO local datetime 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:

  • CASEBOOK
  • EVENT
  • FORM
  • ITEM The chosen reference type must have an identity set up in the identities object in the header file.
 N/A

N/A

Query Statuses

EDC Migrator can migrate Queries into EDC with the statuses: Open (1), Answered (2), or Closed (3). Queries with the Closed status remain closed after migration. The Migrator does not support migrating Deleted (4) queries.

Acceptable Workflows

The queries you ingest into the Migrator must follow workflows accepted by the EDC model. The following rules apply to these workflows:

  • The first query message must be in an Open state.
  • No consecutive Answered query messages. For example, Open > Answered > Answered.

Outside of these rules, messages can be in any sequence, including Reopened. For example, Open > Answered > Closed > Reopened is an acceptable workflow.

Error Handling

The Migrator continues to process data even when it finds errors or warnings in your CSV file. It logs invalid records in the Log File.

You may encounter an error if the following occurs:

  • Import a query at an invalid level (like the Subject or Site level).
  • Import a query with a status other than Open, Answered, or Closed.
  • Import a query with a blank or invalid Query ID. The Query ID must contain at least one character and cannot exceed 50 characters.
  • Import a query with a blank Query Message. The message must contain at least one character.
  • Import a Query Message Date that is not in the appropriate ISO local datetime format.
  • Import queries that have an unacceptable workflow.

Multiple Messages per Query

The Migrator imports a query’s entire message history in chronological order, from earliest to latest. One query may have more than one Query Message, or comment, associated with it. To load these into EDC with EDC Migrator, 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).

If there is a gap in the message sequence numbers, EDC Migrator 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 EDC 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.

Migrated Queries in EDC

After a successful load, migrated Queries are visible in EDC. Query Messages and query-related audit trail entries are attributed to the CDMS Migration User. Statuses are reflected in the audit trail.

EDC users with the appropriate permissions can now answer, close, and re-open these queries in the same way as queries originating in Veeva EDC.