User-Defined Properties (UDP)

The main data tables for user-defined properties are:

The database structure is as follows:

The main data table for UDPs is Table_Field_Values. The logical key for the table is as follows:

The UDP value is always stored as a string so the field type is generally not necessary when retrieving the UDP value, as the data type is already known. As such, the field type is only important for creating a new UDP.

The KeyId value is dependent on which table the UDP is for. For example, if the TableId corresponded to the Prod_Units table (for example, Production Units), then the KeyId will be equal to a particular PU_Id. If the TableId corresponded to the PrdExec_Paths table (for example, Execution Paths) then the KeyId will be equal to a particular Path_Id.

The following is an example of the configuration of a UDP for a Production Unit in the Administrator:

The following is an example query of how to retrieve the UDP value for the above configuration:

SELECT tfv.Value
FROM Table_Fields_Values tfv
   JOIN Tables t ON tfv.TableId = t.TableId
   JOIN Table_Fields tf ON tf.Table_Field_Id = tfv.Table_Field_Id
   JOIN Prod_Units pu ON tfv.KeyId = pu.PU_Id -- This depends on the TableId being referenced 
WHERE	t.TableName = 'Prod_Units'
        AND tf.Table_Field_Desc = 'MyUDP' 
        AND pu.PU_Desc = 'Machine 1'

The following is an example configuration of a UDP for an Execution Path in the Administrator:

The following is an example query of how to retrieve the UDP value for the above configuration:

SELECT tfv.Value
FROM Table_Fields_Values tfv
    JOIN Tables t ON tfv.TableId = t.TableId
    JOIN Table_Fields tf ON tf.Table_Field_Id = tfv.Table_Field_Id 
    JOIN PrdExec_Paths p ON tfv.KeyId = p.Path_Id
WHERE	    t.TableName = 'PrdExec_Paths'
            AND tf.Table_Field_Desc = 'MyUDP' 
            AND p.Path_Code = 'M1'

While only a few of the tables have an interface (either in the Client or Administrator) there are many tables currently defined in Tables and more are continually being added. Another thing to note is that while UDPs are typically created for custom configuration, they can also be used to track additional information in data tables (for example, Events). The following table lists a subset of the available SQL tables and the corresponding keys that are reference in Table_Fields_Values.