As discussed in the Field Properties topic, an enumerated field is one that contains a pre-defined set of codes that mean something. For example, in the sample Northwind database that comes with SQL Server, the ShipVia field in the Orders table contains a 1, 2, or 3.

While you can set up the enumerated values in Stonefield Query Studio, you may need to do this programmatically, especially if the field is a custom field rather than a field in the stock database. This is typically done in a DataEngine.GetCustomMetaData script. To do that, fill in the following properties:

  • UserDefined: the values in UserDefined need to be in a specific format: Value, space, dash, space, Description, carriage return, Value, space, dash, space, Description, carriage return, etc. See the example below.

  • OutputType: set OutputType to the data type of the description, which is usually C (for Character).

  • OutputLength: set this to length of the longest description. For example, if "A" means "Active" and "I" means "Inactive," set OutputLength to 8 (the length of "Inactive").

  • Expression: set this to "EnumValue('TableName', 'FieldName')", where TableName is the name of the table the field belongs to and FieldName is the field name.

Here’s an example:

loField = toApplication.DataEngine.Fields.AddItem('Customers.TestEnum')
loField.Caption = 'Test Enum'
loField.Type = 'I'
loField.UserDefined = '1 - Fedex' + chr(13) + ;
    '2 - UPS' + chr(13) + ;
    '3 - Mail' +chr(13)
loField.OutputType = 'C'
loField.OutputLength = 5
loField.Expression = "EnumValue('Customers', 'TestEnum')"