Dataphin:Create and manage standard codes (lookup tables)

Permission description

• Super administrators and data standard administrators have the authority to create and manage all lookup tables, lookup table directories, and import template libraries.

• Data standard set maintainers are authorized to edit or delete lookup tables they have joined.

Limits

• Lookup table directories cannot exceed 10 levels in hierarchy.

• The total number of lookup table directories must not surpass 1000 across all levels.

• You can create up to 100,000 lookup tables.

• Each lookup table can contain a maximum of 10,000 code values, which must be unique within the table.

Lookup table list

The lookup table list offers two display modes: Edit and View. On the Reference Tables page, switch between views by clicking the icon in the upper right corner .

In View mode, you can browse lookup table directories and their information, but operations are not permitted. This mode is ideal for viewing only.

In Edit mode, you can view and perform actions such as viewing references, editing, exporting lookup tables, and deleting. Lookup table directories can also be edited and deleted in this mode. Edit mode is accessible only to users with lookup table management permissions.

1. On the Reference Tables list page, the left-side directory classifies and manages lookup tables from a business or organizational perspective. Selecting a directory level displays the corresponding lookup tables on the right, including name, directory, description, status, definition method, code value count, owner, and last updated by/time. Click All Lookup Tables to view all lookup tables.

2. (Optional) Search for lookup tables by name or code, or filter by directory, owner, etc.

3. Perform the following operations on the selected lookup table directory or table:

   Operation Item		Description
   Lookup Table Directory	Create Lookup Table	To create a new lookup table, click the Create Lookup Table icon next to the directory. If in the All Lookup Tables directory, the new table will be added there directly.
   	Import Lookup Table from Template Library	To import a lookup table template into the current directory, click the Import lookup table from template library icon to the right of the directory row, or hover over the create lookup table button and select Import lookup table from template library from the dropdown menu.
   Lookup Table List	View Details	Click View Details in the operation column to view detailed lookup table and reference information.
   	Edit	Click Edit in the operation column to modify the lookup table's configuration. If the definition method, source table, or fields are changed, the existing configuration will only be updated after data retrieval is successful and the changes are saved and published. Otherwise, the existing configuration will be retained.
   	Export	Click Export in the operation column or select lookup tables from the list and click Batch Export at the bottom to export lookup table and code value information as an Excel file. This is useful for maintenance and sharing with business personnel. For export details, see Batch Import and Export Lookup Table.
   	Update Code Values	This operation is only available when the definition method is "Import from Table." Click More in the operation column and select Update Code Values. The system will automatically retrieve the latest code value data according to the import configuration. After successful retrieval, you need to save and publish to update the existing code value configuration.
   	Edit Code Values	This operation is only available when the definition method is "Import from Table." Click More in the operation column and select Edit Code Values to directly modify code value definitions. This is suitable for scenarios where simple modifications to the original code value information imported from tables are needed.
   	View References	Click More in the operation column and select View References to view which standard templates reference the lookup table. Click the Standard Template name for details, including property information, references, and historical versions.
   	Delete	Click More in the operation column and select Delete. Deletion is irreversible; proceed with caution.

Manage lookup table directory

1. Navigate to the Dataphin home page, select Administration > Standard from the top menu bar.

2. In the navigation pane on the left, choose Reference Data > Standard Codes (Lookup Table). On the Lookup Table Directory tab, click the Edit icon.

3. In the Lookup Table Directory Management panel, you can create, move, edit, and delete lookup table directories.

   Operation item	Description
   Create	When creating a directory, ensure the name is unique within the same directory and does not exceed 128 characters. Avoid using characters like the forward slash (/). New directories can be nested within an existing hierarchy. If no parent directory is selected, the new directory will be placed directly under the root directory of the lookup table.
   Edit	This lets you change the name of an existing directory.
   Move	This option lets you change the parent directory of a lookup table.
   Delete	Deletion is not possible if the directory contains subdirectories or lookup tables.

Create lookup table

1. On the Reference Tables list page, click the + Create Lookup Table button.

2. In the Create Custom Lookup Table dialog box, fill in the required parameters.

   Parameter	Description
   Lookup Table Name	Specify the lookup table name, ensuring it is unique within the parent directory and does not exceed 128 characters. For instance: National Region Code Table.
   Lookup Table Code	Enter a unique identifier for the lookup table, limited to 128 characters. Acceptable characters include English letters, numbers, and half-width symbols. Example: DQ001.
   Directory	Choose the directory for the lookup table. If you are already in a lookup table directory, this directory will be automatically selected when you create a new lookup table. If no directory is chosen, the lookup table will default to the root directory. For instructions on creating a new directory, see Manage lookup table directory.
   Owner	Assign an owner for the lookup table from the list of users.
   Description	Provide a description for the lookup table, detailing its purpose and scope, within a 1000-character limit.
   Code Information	Two definition methods are supported: Custom and Import From Table. Custom create lookup table Fill in the information for the lookup table. You can also edit and delete the code information. Click the Add Code Value button to add more code values. You can also click the Batch Entry button to add code values in batches, or click the Delete All button to clear all code values. Batch Entry: In the Batch Entry dialog box, you can enter or paste code value information. The system parses a maximum of 10,000 rows. Each row is parsed for the code value, code name, English code name, and code description in sequence. Use a tab character (\t) or a space character (\s) to separate parameters, which are columns. Use a line feed character (\n) or Shift+Enter to start a new line. Example entry: Code value: 2900, Code name: Shanghai City, Code English name: Shanghai, Code description: Identifies Shanghai City. Code Value: Required. Must be unique within the lookup table. Maximum 64 characters. Code Name: Required. Maximum 64 characters. Code English Name: Optional. The name can be up to 64 characters in length and can contain uppercase and lowercase letters, digits, and the following special characters: ()^~`!@#$%&*_+\-=[]{}|;':",./<>·?. Code Description: Optional. Accepts up to 256 characters. Click Save And Publish to complete the creation of the lookup table. Import from table Note When retrieving code value data, the system does not filter based on row-level permissions or perform desensitized queries. Please select data sources that can be publicly accessed. When the lookup table definition method is "Import from Table," using partitioned tables is not recommended. If necessary, use tables where each partition stores the full data. Because each lookup table can contain a maximum of 10,000 code values, only the first 10,000 records that meet the conditions are queried. The current operator must have select permission on the selected source table fields. The system automatically converts data read from the source table to string type for storage. If the code name and code value source field types cannot be converted, the entire record fails to be added. If the English name and description source fields cannot be converted, the corresponding configuration items are left empty without affecting the addition of the entire record. You can reference different data tables to quickly create lookup tables, reducing configuration costs. In the code information area, configure the following information. Source Table (Required): Supports referencing Dataphin tables (except meta tables and image tables) and data source tables that support Data Preview. You can perform fuzzy searches based on table full name or table description, or click the filter icon to quickly filter the required data tables by module/project/data source or table type. Code Value (Required): Select a field from the source table. Code values must be unique within the lookup table. If the source field values are duplicated, the system automatically retrieves the data corresponding to the first record. If the source data exceeds 64 characters, the entire record fails to be added. Code Name (Required): Select a field from the source table. If the source data exceeds 64 characters, the entire record fails to be added. Code English Name (Optional): Select a field from the source table. The name can contain uppercase and lowercase letters, digits, and the following special characters: ()^~`!@#$%&*_+\-=[]{}|;':",./<>·?. If the source data exceeds 64 characters in length, the system automatically truncates the data or leaves the field empty. Code Description (Optional): Select a field from the source table. If the source data exceeds 256 characters, the system automatically truncates or leaves it empty. Data Filtering (Optional): Supports filtering source data by adding filter conditions such as partitions, null values, or duplicate values based on the selected table fields. When the table is a MaxCompute computing source table or Dataphin logical table, the system automatically enables and populates partition filter conditions. For other source types, you can enable this as needed. After completing the configuration, click the Preview Data button to verify the parameters, permissions, and syntax. Parameters: Verifies that all required parameters are filled in. Permissions: Verifies that the current operator has permission to create lookup tables. Filter Conditions: Verifies that the filter condition statements are correct. After verification passes, retrieve the lookup table data and add the lookup table to Dataphin. While the lookup table data is being retrieved, you can click Cancel or close the dialog box to terminate the query and exit. The configured data will not be saved. If you click Previous, you will return to the previous step and terminate the query. You will need to retrieve the data again. If the lookup table data retrieval fails, you can click Retry to retrieve the data again, or click View Log to see the reason for the failure. When the lookup table data is successfully retrieved, you can preview the code value information. Click Save And Publish to add the lookup table to Dataphin. If some records fail to be retrieved, an exception list will be displayed showing the specific reasons.

What to do next

Once the lookup table has been established, you can choose the appropriate lookup table to set value constraints for properties within standard templates. For more information, see Create and manage standard templates.