Single/multi-select fields - remote validation

Learn how to define your single/multi-select field terms using a database table

What are single/multi-select fields?

A quick recap, single/multi-select fields are used to help improve the consistency of content tagging upon upload. Imagen end users navigate these fields by typing a few characters and being shown matching options from which to select.

Single- Select	With this field, you can choose one unique value from a list of predefined terms
Multi- Select	With this field, you can choose multiple unique value from a list of predefined terms

What is remote validation?

Remote validation allows Imagen administrators to define their field terms using a database table.

What are the benefits?

Remote validation enables Imagen administrators to define and manage many thousands of field terms without the need for manual entry using our Media Control Centre (MCC), thereby not encumbering ImagenWeb with a slow interface due to attempting to load thousands of values into a dropdown.

Remote validation requires administrators to manage field terms by modifying the relevant database table using a script or function.

NOTE: In future, there are plans to have an API call to allow field terms to be CRUD.

Examples and configuration

Multi-select field to contain player names:

You will need two new tables, one to list players and the other to associate multiple players with one record.

2. Create table one:

CREATE TABLE `players` (

`pkPlayerID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID of the player',

`playerName` varchar(255) NOT NULL COMMENT 'Name of the player',

PRIMARY KEY (`pkPlayerID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table of players';

NOTE: At minimum the table needs a primary key field and a field for term values.

2. Create table two:

CREATE TABLE `players_main` (

 `pkPlayersMain` int NOT NULL AUTO_INCREMENT COMMENT 'primary key',

 `fkPlayerID` int NOT NULL COMMENT 'Foreign key ID of the player',

 `fkRecordNumber` int NOT NULL COMMENT 'ID of the record to which this player belongs',

 PRIMARY KEY (`pkPlayersMain`),

 UNIQUE KEY `record_player_UNIQUE` (`fkPlayerID`,`fkRecordNumber`),

 CONSTRAINT `FK_playerID_players` FOREIGN KEY (`fkPlayerID`) REFERENCES `players` (`pkPlayerID`),

 CONSTRAINT `FK_recordNumber_main` FOREIGN KEY (`fkRecordNumber`) REFERENCES `main` (`pkRecordNumber`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Link between players and the records to which they belong';

NOTE: At minimum the table needs a foreign key for the main.pkRecordNumber field and another foreign key for the first table.

3. Once the tables are complete you need to modify the DBSchema.xml configuration file manually (this file holds the configuration information for all database fields).

In the DBSchema.xml file, usually under C:\ProgramData\Imagen_Ltd\MediaControlCentre, back up the current file.
Then look under the Fields node of the database and the last field in the database is Field Number="nnn" so you know you need to add fields after that starting from field number nnn + 1 (obviously enter the actual number here).
Now, add the following:

<Table Name="players_main" RecordNumberField="fkRecordNumber">

<Field Number=”nnn+1">

<FieldName>fkPlayerID</FieldName>

<FieldType>0x4</FieldType>

<DisplayName>fkPlayerID</DisplayName>

<FieldDetails>0x1</FieldDetails>

<EntryType>0x1</EntryType>

<DisplayType Type="Language">

<DisplayInfo Key="en-GB">fkPlayerID</DisplayInfo>

</DisplayType>

</Field>

<Reference From="fkPlayerID">pkPlayerID</Reference>

</Table>

<Table Name="players" PrimaryKey="pkPlayerID">

<Field Number="nnn+2">

<FieldName>playerName</FieldName>

<FieldType>0x20001</FieldType>

<DisplayName>Players</DisplayName>

<FieldDetails>0x41</FieldDetails>

<EntryType>0x1</EntryType>

<DisplayType Type="CIS_BROWSE">

<DisplayInfo Key="Record">1</DisplayInfo>

<DisplayInfo Key="DataEntry">1</DisplayInfo>

<DisplayInfo Key="Searchable">1</DisplayInfo>

<DisplayInfo Key="VisibleResult">1</DisplayInfo>

<DisplayInfo Key="ContentLanguage">en-GB</DisplayInfo>

</DisplayType>

<DisplayType Type="Language">

<DisplayInfo Key="en-GB">Name</DisplayInfo>

</DisplayType>

</Field>

</Table>

4. Restart your IMCC.

NOTE: If the customer wants, for example, PlayerName to be searchable in Solr AND a third party modifies a player’s name then the third-party module needs to make a REST call to update Solr accordingly. Or, you can manually add these fields to Solr standard search using the MediaControlCentre as normal.

Single-select field to contain manager names:

1. Create table one:


CREATE TABLE `managers` (

`pkManagerID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID of the manager,

`managerName` varchar(255) NOT NULL COMMENT 'Name of the manager,

PRIMARY KEY (`pkManagerID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table of managers;

NOTE: At minimum the table needs a primary key field and a field for term values.

2. Once the table is complete you need to modify the DBSchema.xml configuration file manually (this file holds the configuration information for all database fields).

In the DBSchema.xml file, usually under C:\ProgramData\Imagen_Ltd\MediaControlCentre, back up the current file.
Look under the Fields node of the database and the last field in the database is Field Number="nnn" so you know you need to add fields after that starting from field number nnn + 1 (obviously enter the actual number here).
Now, add the following:

<Reference From="fkManagerID">pkManagerID</Reference> <!—This notes that these unique identifiers are linkedà

</Table>                      

<Table Name="managers" PrimaryKey="pkManagerID"><!—Here you are defining the sub tableà                              

<Field Number="nnn+1">                            

<FieldName>managerName</FieldName>                    

<FieldType>0x40001</FieldType>

<DisplayName>Manager</DisplayName>

<FieldDetails>0x41</FieldDetails>

<EntryType>0x1</EntryType>

<DisplayType Type="CIS_BROWSE">

<DisplayInfo Key="Record">1</DisplayInfo>      

<DisplayInfo Key="DataEntry">1</DisplayInfo>                            

<DisplayInfo Key="Searchable">1</DisplayInfo>                            

<DisplayInfo Key="VisibleResult">1</DisplayInfo>                            

<DisplayInfo Key="ContentLanguage">en-GB</DisplayInfo>                            

</DisplayType>          

<DisplayType Type="Language">      

<DisplayInfo Key="en-GB">Manager</DisplayInfo>                        

</DisplayType>        

</Field>              

</Table>

3. Restart your IMCC.

4. Add these fields to Solr standard search using the MediaControlCentre management interface as normal.

NOTE: If the customer wants, for example, ManagerName to be searchable in Solr AND a third party modifies a manager’s name then the third-party module needs to make a REST call to update Solr accordingly. Or, you can manually add these fields to Solr standard search using the MediaControlCentre as normal.