# Complaint Type Master

### Introduction

The Complaint Type Master sheet defines complaint categories and their respective sub-types for the tenant.\
These values drive key functionalities across the DIGIT platform, including:

* Citizen service request creation
* Workflow routing
* SLA and resolution time calculations
* Search behaviour in citizen-facing applications

To ensure consistency, accuracy, and avoidance of duplicates, the sheet applies multiple Excel-level validations.

Common Master: [Common and Complaint type Master](https://docs.google.com/spreadsheets/d/1CQOSxJZBi3LJ8PUrJW3mkiEHTmGDp-F8ZPw4ShvRBUA/edit?usp=sharing)

## Data Table

<table><thead><tr><th width="73.63671875">S. No</th><th>Complaint Type*</th><th>Complaint Sub Type*</th><th>Department Name*</th><th width="111.57421875">Resolution Time (Hours)*</th><th>Search Words</th></tr></thead><tbody><tr><td>1</td><td>Water not coming</td><td>Tap broken</td><td>WATER DEPARTMENT</td><td>1</td><td>water tap, water supply</td></tr><tr><td>2</td><td>Water not coming</td><td>Sanitation broken</td><td>SANITATION DEPARTMENT</td><td>3</td><td>sanitation, broken tap</td></tr></tbody></table>

<mark style="color:$warning;">Note: This is sample data. All entries must adhere to the validation rules defined below.</mark>

***

## Procedure

### Data Definition

<table><thead><tr><th width="70.90234375">Sr. No.</th><th width="117.18359375">Column Name</th><th width="101.53125">Data Type</th><th width="98.2890625">Data Size</th><th width="115.3359375">Mandatory?</th><th>Definition / Description</th></tr></thead><tbody><tr><td>1</td><td>Complaint Type</td><td>Text</td><td>256</td><td>Yes</td><td>Main complaint category. Only alphabets and spaces allowed. Must be unique across the sheet.</td></tr><tr><td>2</td><td>Complaint Sub Type</td><td>Text</td><td>256</td><td>Yes</td><td>Sub-classification under the main complaint category. Must be alphabetic and unique within its parent complaint type.</td></tr><tr><td>3</td><td>Department Name</td><td>Text (Dropdown)</td><td>256</td><td>Yes</td><td>Department responsible for handling the complaint. Value must match Department &#x26; Designation Master.</td></tr><tr><td>4</td><td>Resolution Time (Hours)</td><td>Number/Decimal</td><td>64</td><td>Yes</td><td>SLA time in hours. Only positive integers or decimal values allowed.</td></tr><tr><td>5</td><td>Search Words</td><td>Text</td><td>512</td><td>No</td><td>Comma-separated keywords used to support complaint search. Only alphabetic words allowed.</td></tr></tbody></table>

***

## Steps to Fill Data

1. Download the Complaint Type Master template.
2. Review all column headers and understand their purpose from the Data Definition section.
3. For each row:
   1. Enter a valid Complaint Type (mandatory and must be unique).
   2. Enter a Complaint Sub Type (mandatory; unique within the complaint type).
4. Select the Department Name from the dropdown list sourced from the Department & Designation Master.
5. Enter Resolution Time (Hours) using only numeric or decimal values.
6. Enter Search Words as comma-separated alphabetic terms.
7. Ensure:
   1. No complaint type is repeated.
   2. No subtype is duplicated under the same type.
8. Department names exactly match the master list.
9. SLA values do not contain text or symbols.
10. Keywords contain only alphabets and commas.
11. After filling all rows, verify data against the validation rules.
12. Save the file and prepare for MDMS ingestion.

***

## Excel Validation Framework

The sheet enforces multiple validations to ensure structural correctness and prevent invalid or duplicate master data.

### Types of Validations Applied

<table><thead><tr><th width="276.95703125">Validation Type</th><th>Description</th></tr></thead><tbody><tr><td>Unique Complaint Type</td><td>Ensures each complaint type appears only once.</td></tr><tr><td>Unique Sub-Type Under Type</td><td>Prevents duplicate sub-types under the same complaint category.</td></tr><tr><td>Department Name Validation</td><td>Ensures value matches the Department Master via dropdown list.</td></tr><tr><td>SLA Number Validation</td><td>Restricts input to numeric or decimal values only.</td></tr><tr><td>Alphabetic Search Keyword Validation</td><td>Ensures comma-separated search terms contain only letters.</td></tr></tbody></table>

***

## Sample Data Rows

<table><thead><tr><th>Complaint Type</th><th>Complaint Sub Type</th><th>Department Name</th><th width="126.40625">Resolution Time (Hours)</th><th>Search Words</th></tr></thead><tbody><tr><td>Water not coming</td><td>Tap broken</td><td>WATER DEPARTMENT</td><td>1</td><td>water tap, water supply</td></tr><tr><td>Water not coming</td><td>Sanitation broken</td><td>SANITATION DEPARTMENT</td><td>3</td><td>sanitation, broken tap</td></tr></tbody></table>
