Risk register import fails due to unmapped mandatory fields

QMS ETQ Reliance
, , , , , , , ,
1

We’re trying to import our legacy risk register into ETQ using the import utility, but the process keeps failing with validation errors. The Excel template seems properly formatted with all columns matching the risk register fields, but we’re getting errors about mandatory fields being unmapped.

The error log shows:


Error: Mandatory field 'Risk_Owner' not mapped
Error: Required field 'Mitigation_Status' missing value
Row 47: Validation failed - check template mapping

We’ve verified the Excel column headers match the field names in ETQ, and the data looks clean. Has anyone encountered similar import template field mapping issues? The validation requirements seem stricter than documented, and we’re not sure if it’s an Excel formatting problem or something with how ETQ handles required fields during bulk imports.

2

For lookup fields like Risk Owner, you need to provide the exact user ID or username that exists in ETQ, not just a name. If your Excel has ‘John Smith’ but ETQ expects ‘jsmith’ or a numeric ID, validation will fail. Export a sample risk record from ETQ first to see the exact format expected for each field type. Also check if Mitigation Status is a dropdown - you’ll need exact picklist values.

3

Excel formatting can definitely cause issues with ETQ imports. Make sure all cells are formatted as ‘Text’ before entering data, especially for fields that might look like numbers or dates. I’ve seen cases where Excel auto-converts data (like risk IDs that start with zeros) and ETQ rejects them. Also, watch out for hidden characters or extra spaces - use TRIM function on all text fields. Save as CSV and open in a text editor to verify there’s no hidden formatting.

4

Check if your field names in Excel exactly match the API names in ETQ, not just the display labels. Go to Risk Management module configuration and look at the actual field identifiers. Sometimes ‘Risk Owner’ (display) maps to ‘risk_owner_id’ (API name). The import utility uses API names for mapping.

5

Let me address all three key aspects systematically:

Import Template Field Mapping: The critical issue is distinguishing between display names and API field names. In ETQ 2021, navigate to Settings > Risk Management > Field Configuration and export the field schema. Your Excel headers must match the ‘Field Name’ column exactly, not the ‘Display Label’. For example:


Excel Header: risk_owner_id (not 'Risk Owner')
Excel Header: mitigation_status (not 'Mitigation Status')
Excel Header: risk_category_id (not 'Risk Category')

Required Field Validation: ETQ enforces multiple validation layers during import:

  1. Static mandatory fields: Marked as required in field configuration
  2. Conditional requirements: Fields that become mandatory based on other field values (check Form Rules)
  3. Lookup field validation: For user/department/category fields, values must exist in ETQ. Export reference data first:
    • Users: Settings > User Management > Export Users (use ‘username’ column)
    • Categories: Export existing risk categories to get valid category IDs
  4. Workflow state requirements: If importing into specific workflow states, ensure all state-required fields are populated

Before importing, create a validation template:

  • Export 2-3 existing risks that match your target state
  • Use this as your column header and format reference
  • Compare data types and formats exactly

Excel Formatting Issues: Common problems and solutions:

  • Format all columns as Text before data entry (select columns > Format Cells > Text)
  • Remove hidden characters: Use formula =CLEAN(TRIM(A1)) on all text fields
  • Date standardization: Convert to ISO format using `=TEXT(date_cell,“YYYY-MM-DD”)
  • Numeric IDs: Prefix with apostrophe ('00123) to prevent Excel from dropping leading zeros
  • Special characters: Avoid smart quotes, em dashes, and non-ASCII characters
  • Save as CSV UTF-8: File > Save As > CSV UTF-8 (Comma delimited)

For row 47 specifically, open your CSV in Notepad++ and check for:

  • Inconsistent delimiters (commas within quoted fields)
  • Line break characters within cells
  • Byte order marks (BOM) at file start

Test import with just rows 1-10 first, then gradually increase batch size. ETQ’s import log will be more specific with smaller batches. If issues persist, enable debug logging in the import utility settings to capture detailed validation messages.

6

We had this exact problem last year. The issue was that some mandatory fields have dependencies on other fields. For example, if ‘Mitigation_Status’ is set to ‘Completed’, ETQ might require ‘Completion_Date’ to also have a value, even if Completion_Date isn’t marked as mandatory in the field definition. Check the field rules and business logic in your risk register form configuration.

7

Another common gotcha: date format inconsistencies. ETQ expects dates in ISO format (YYYY-MM-DD) for imports, but Excel might show them differently based on regional settings. Convert all date columns to text format with the formula =TEXT(A1,“YYYY-MM-DD”) before importing.

8

That’s a good point about API names vs display names. I checked the field configuration and found some mismatches. However, even after correcting the column headers to use API names like ‘risk_owner_id’ and ‘mitigation_status’, we’re still getting validation errors on certain rows. Row 47 mentioned in the error has data in all mandatory fields, so I’m wondering if there’s something wrong with the data format itself.