The proprietary statements provided by dqMan simplify the user's tasks by employing a language that closely resembles SQL.

Distinguished by the dqMan logo, these proprietary statements are visually marked on the corresponding query row.

dqMan empowers users by enabling them to utilize select *, group by, order by, count, as well as descriptions of various fields or relations.

Select statement extensions

Selecting all queryable fields

SELECT * FROM user__sys;
SELECT * FROM group__sys;
SELECT * FROM binder_node__sys;

The SELECT * statement is used to retrieve all queryable fields from a specified target. In the given examples, SELECT * is used to fetch all queryable fields from the targets "user__sys," "group__sys," and "binder_node__sys."

Counting the objects in a query:

SELECT count(*) FROM documents WHERE type__v = 'Clinical' 

The COUNT(*) function is used to calculate the number of rows returned by a query. In the provided example, the COUNT(*) function is used to count the records in the "documents" where the "type__v" column is equal to 'Clinical.'

Caching the query results in a local table:

SELECT 
  id,
  major_version_number__v,
  minor_version_number__v,
  name__v,
  title__v,
  document_number__v,
  type__v,
  subtype__v,
  classification__v,
  status__v,
  document_creation_date__v,
  version_creation_date__v,
  version_modified_date__v,
  created_by__v,
  version_created_by__v,
  last_modified_by__v,
  lifecycle__v,
  state_stage_id__sys,
  global_id__sys,
  filename__v ,
  clinical_study__v,
  product__v
FROM 
  documents
WHERE
  type__v = 'Clinical'
INTO local_clinical;

The INTO clause is used to store the results of a query into a local table. In the given example, the query results from selecting specific columns from the "documents" table, where the "type__v" column is equal to 'Clinical,' are stored in a local table named "local_clinical."

Querying local tables by using the full SQLite syntax

SELECT 
  subtype__v,
  classification__v,
  count(id) as number_of_documents
FROM 
  local_clinical 
GROUP BY subtype__v, classification__v;

After storing query results in a local table, regular SQL syntax, specifically SQLite syntax in this case, can be used to query the local table. The provided example demonstrates a SELECT statement on the "local_clinical" table, where the results are grouped by "subtype__v" and "classification__v," while also counting the number of documents using the COUNT function.

The full syntax for querying the local tables can be found here: https://www.sqlite.org/lang_select.html

DESCRIBE command

Syntax:

Describe <type> [fields|relations] [all|custom] Describe <[\]doc_type[\doc_subtype[\doc_classification]]> [all|custom]

Arguments:

Examples:

Describing object and document relations:

The DESCRIBE statement is used to obtain information about relations of an object in the system. In the given examples, the DESCRIBE statement is used to retrieve information about the relations of objects such as "activity__v" and "user__sys."

describe <object> relations - returns all relations of an object
describe <document type> relations - returns all relations of the document type

DESCRIBE activity__v RELATIONS;
DESCRIBE user__sys RELATIONS;

Describing document and object fields:

To view all fields of a document or object type in the results grid, users can utilize the "describe" command. The syntax for the "describe" command is as follows:

DESCRIBE object | []doc_type [\doc_subtype [\doc_classification]] [[[ALL|CUSTOM] FIELDS]]

describe <object> fields - returns all fields of an object
describe <document type> fields - returns all fields of the document type
describe <document type> custom fields - returns only the fields defined for the specified document type hierachy
<document type> can be any type hierarchy : type\subtype\classification. Subtype and classification are optional

In the provided example, the DESCRIBE statement is used to retrieve information about the fields of the "\regulatory__c" document type, including both standard and custom fields.

DESCRIBE \regulatory__c FIELDS;
DESCRIBE \regulatory__c CUSTOM FIELDS;

In the VQL View, you can execute single VQL queries.

To execute a VQL query, do one of the following:

• Press F9 or Shift+Enter.

• Go to the Edit menu>Run.

• Click Run on the context menu.

• Click the Run button on the toolbar.

Other actions you can perform in the VQL View:

• Double-click a cell in the result grid to copy the value on the clipboard. Press Ctrl when double-clicking to insert the cell value into the code editor at the current cursor position. The latter can also be performed on the result grid's column headers to insert the respective column names as attribute names at the cursor position.

• Click and hold the left mouse button for 1 second on any value representing a valid Veeva id to load the respective object into the Object Navigator (see Object Navigator).

• Double-click the messages in the status bar to copy them to the clipboard. Move the mouse cursor over the status bar to display the current message in a tooltip, if it does not fit into the message bar area.

• You can see information about the latest executed action and the query result in the Messages area.

The status bar displays the complete execution time of the query, including the transfer of the query results to dqMan. The pure server response time without data transfer is labeled as Server response time.

Work with VQL Queries

You can enter one or several VQL queries in the SQL Query input area. When executing the query, all the content in the input area is regarded as one query and transferred to the Veeva Vault for execution. However, if only a part of a query is selected, then only this part is executed.

Use semicolons as separators for two or more queries. Setting the text cursor on one query selects this query for execution.

Moreover, you can add comments. Every line beginning with --, # or // characters is regarded as a comment line.

Syntax assistance

A number of syntax assistance functions supports the creation of VQL queries:

• Command syntax: Click a VQL command and wait for the hint to view the correct command syntax.

• Auto completion: Several dynamic values, such as object names, query targets, VQL functions and so on, or allowed function values are faded in automatically in the form of a selection list. You can copy the values from selection lists by pressing Enter.

You can manually activate the selection list anywhere in the command area by pressing Ctrl+Space. The list is filled context dependent with the likely values at the current position. If none of the pre-defined lists applies, the list is filled with the possible attribute names.

Results list

The Results list contains the data returned by the previously executed VQL statement.

Use your mouse to resize the columns and rows and reposition the columns in the Results list.

To search through the entire table, go to Edit menu>Search. Typing one or several initial letters within one column will scroll to the first matching value.

You can right-click the desired Results list column head and select to sort it alphabetically. Clicking an item to scroll the column into the visible range of the list.

On the data area, a list of functions is faded in for object processing. Available functions are:

Edit Starts the Results List Inline Editor.

Copy This function copies the selected values to the clipboard. Rows will be separated with a linefeed and a carriage return character; columns will be separated with tabs.

Copy with Header This function copies the selected values to the clipboard, including the header names of the selected columns in the first line. Rows will be separated with a linefeed and a carriage return character; columns will be separated with tabs.

Duplicate Duplicate copies all selected values in a separate data grid.

Add to compare list The selected objects are added to the Object Compare list.

Sort Sorts the result data ascending or descending by the selected column.

Goto... Enables the fast change to a line or a column for the Results list. Entering a number enables vertical scroll, entering a column header or selecting one from the will enables horizontal scroll.

Marks Used to select different objects in the Results list. You can toggle or scroll marks to the next or previous marked object. Marks are mainly used by the Compare Data function, but can also be set manually.

Export to Exports the selected cells of the Results list to CSV or Microsoft Excel (you need to have Excel installed to be able to export to this format). You cannot export data to Excel if a remote Office 365 installation is used; if this is the case, use the Copy with Header command.

Dump Dump the select document or object. Available only when id field is displayed in the grid.

View file content Opens the content of the selected document. Available only when id field is displayed in the grid.

View Renditions Opens the last version of rendition of the selected document. Available only when id field is displayed in the grid.

View Attachments Opens a list of attachments where you can choose from. Available only when id field is displayed in the grid.

Delete Selected Version Deletes the selected version of the document. This option is present only when all fields required for identifying a version are displayed in the grid: id, major_version_number__v, minor_version_number__v,

Delete All Versions Deletes all versions of a document.

Open in Veeva Opens the Veeva UI in the browser and navigates to the selected object

Results List Inline Editor

You can edit values directly in the Results list, if the results contain the id attribute. Press Enter to save any changes. Pressing Esc or moving the focus to any other control will close the editor without saving.

Single Attributes For single attributes, a standard input field will be created in the corresponding cell of the grid. Some attributes display a list with possible values.

Repeating Attributes You can modify Repeating Attributes within a small sub grid. The sub grid offers an own context menu for inserting, deleting or reordering values of the repeating attribute.

Multiple Single Attributes If the Results list contains more than one result, you can edit a single attribute of multiple objects simultaneously. Select more than one value in a column and start the editor. The values you enter are applied to all the selected objects.

Multiple standalone or linked repeating attributes You can also modify standalone or linked repeating attributes of more than one object at the same time, if the selection is extended to more than one object (line) in the result grid. The values you enter are applied to all the selected objects.

Attribute Filter

You can filter query results by the queried values using the attribute filter, which can be open for each column. You can combine filters and use them for multiple columns using an “and” operation.

To open an attribute filter for a column, click the button in the column header and then, in the filter dialog box, you can see the filter options depending on the column data type.

Click Apply Filter to activate the filter after selecting the filter options. If a filter is active, the filter button in the column header and the row numbers are highlighted.

Additionally, all filter buttons in column headers have a context menu.

Message Area

The Message Area logs the executed VQL commands and their results. The context menu offers some settings to influence the behavior:

You can activate the Message Area from the View menu.

Formating VQL Queries

You can format VQL queries automatically. The associated format templates are defined in the file FormatTemplates.xml.

To format a query, click on the query in the VQL input area and select the function Auto Format Query from the context menu of the VQL input area.

The Query:

Select id, name__v, binder__v, created_by__v from documents where binder__v = true order by id;

Will be formatted to:

SELECT id,

binder__v,

created_by__v

FROM

documents

WHERE

binder__v = true

ORDER BY

id

To clear the formatting of a query (unformat), use the Unformat Query function from the context menu. Calling this function removes all tabs, multiple spaces, line feeds and carriage returns from the query, leaving a plain single line query.

The MDL View allows the user to write and execute Vault MDL (Metadata Definition Language) commands to configure a Vault or automate tasks by writing scripts which manipulate the various Vault component types.

The MDL View offers advanced code editing and debugging functionality, such as MDL syntax highlighting, auto-complete functionality for MDL commands, setting breakpoints, stepping through code during execution, logging responses from the server as commands are executed, executing a sequence of commands as a script, etc.

The MDL View is divided into an upper and lower area - the upper area is the code editor area, while the lower area is the log viewer which displays output and error messages in response to the MDL commands/script being executed.

The bottom border of the code area can be dragged with the left mouse button to split the MDL work area between the code and log windows as per the user's preference (e.g. view more of the code editor or show more log output).

Edit, Load and Save MDL code

MDL code can be typed/edited directly in the upper code editor area of the window, pasted from the clipboard or loaded from/saved to an external file:

• Paste from clipboard: you can paste any text copied to your clipboard into the MDL code editor by using the default Ctrl+V shortcut or selecting Paste from the MDL code editor's context menu. The text will be inserted at the cursor's position.

• Load from file: Either use the Load Script action from the Edit main menu, the Script Navigator (folder tree) or drag-and-drop the file from your filesystem to the MDL view in dqMan Veeva Vault Edition.

• Save to file: Use the Save Script action from the Edit main menu or from the MDL code editor's context menu in order to save MDL you have edited in dqMan Veeva Vault Edition to an external file.

Execute Scripts

Individual statements or scripts can be executed in several ways. It is also possible to step through scripts, pause, or stop a running script. You can control the execution using the action buttons to the right of the main toolbar.

Additional functions are available in the context menu:

Run to Cursor executes all commands from the current cursor position or from the beginning to the selected line. The command in the current line is highlighted and debug mode is turned on.

In debug mode, you can select to execute the next command using Set Next Statement. This can be used to place the next statement/command that will be executed anywhere in the code, thus skipping errors or unneeded steps.

Breakpoints

Breakpoints can also be set to control the execution of a script. Any number of breakpoints can be set anywhere in a script using the Set/Remove Breakpoint context menu items respectively. Breakpoints will be set/removed on the line where the context menu is opened, and will be marked with a red dot to the left of the line as well as highlight. It is also possible to remove all breakpoints at once when they are no longer needed using the Clear All Breakpoints context menu item.

Script Navigator

The Script Navigator is a folder tree for all available local drives, enabling quick access to MDL command/script files stored on the user's machine.

The Script Navigator is optional and can be closed if the user prefers to open MDL files by other means (e.g. using Load Script from the context menu or simply dragging an MDL file into the MDL code editor)

You can open a selected script using the Return key or by double-clicking it. Open scripts are marked as such until the session window is closed. Additionally, you can toggle the marks using the function Mark as New and/or Mark as Opened from the context menu.

Error Handling

The option Stop on Errors in the script editor's context menu defines the error handling behavior:

• When this option is active, the script pauses in case of error and highlights the faulty statement/command. The statement or command affected by the current error will be highlighted in its entirety (even if it spans multiple lines) to clearly show which part of the script is affected.

• If this option is inactive, the script will not stop execution but will display error messages in the result log.

• Error handling will always pause on any error, prompting you to take action before resuming execution (for example, to fix the error or place the cursor after the error to skip it).

Once logged in, you can work in the Session Window in any of the following editors/views:

• VQL: edit & run VQL queries

• MDL: edit & run MDL statements and scripts

You can switch anytime between these views using the above toolbar buttons or the View menu. Changing the view does not delete any existing commands or results in other views; moreover, during script debugging, you can execute commands in other views without stopping the debug process.

You can use the below toolbar buttons to execute your VQL queries/MDL commands/scripts. It is possible to run/step into/pause/stop VQL queries/MDL scripts.