Creating a Field of a Specific Type

This section describes the configuration settings required in order for a field to adopt a particular field type on a generated entity form. For each field type, follow the steps in General Steps for Creating a Field and modify the Fields record as described in each section below.

• Standard Text Field
• Multi-Line Text Field
• Check Box
• Date Selection Field
• Time Field
• Link Box
• Joined Virtual Field

• Using the Wizard to Create a Joined Virtual Field

• Manually Create a Joined Virtual Field

• Standard Combo Drop-down List

• Data Combo Drop-down List

• Calculated and Computed Virtual Fields 

• Extended Attribute Fields

• Money Fields

Standard Text Field

To create a standard text field, configure the following options:

• SQLDataType: Use a data type that corresponds to a string value, such as char or nvarchar.
• SQLFieldSize: Use a field size of 255 or less.

Multi-Line Text Field

To create a multi-line text field, configure the following options:

• SQLDataType: Use a data type that corresponds to a string value, such as char, nchar, varchar, or nvarchar. For large fields, use nvarchar(max) or varchar(max).
  
  
• SQLFieldSize: Use a field size greater than 256 (or max, for nvarchar(max) and varchar(max)).
  
  
• Category: If you want a large text field to appear as its own tab without a separate field caption, enter the field's Name in the Category field. If no other fields are in the same category, Aptify will create a separate tab for this field that does not include a field caption.
  • In the example shown in the figure below, the multi-line Details field is displayed on a separate Details tab.
  • Note that this functionality is only applicable if the Category value equals the field's Name and no other fields are in the same category.
    
    
• Text Interpretation Mode: This option is found on the Options tab. For plain text fields, leave this option set to None. To specify that this field stores text in rich text format, specify RTF. To specify that this field stores HTML text, set this option to HTML.
  • When set to None, Aptify adds this field to a generated form as a standard multi-line text field. See Multi-Line Text Field for more information.
  • When set to RTF, Aptify adds this field to a generated form as a Rich Text Field part. See Rich Text Field for more information.
  • When set to HTML, Aptify adds this field to a generated form as an HTML Field part. See HTML Text Field for more information. 
    
    

Check Box

To create a check box, configure the following option:

• SQLDataType: Use a data type of bit. A value of 1 corresponds to a checked box, and a value of 0 corresponds to an unchecked box.

Date Selection Field

To create a date/time field that includes the calendar icon, configure the following option:

• SQLDataType: Use a data type of datetime or date.
• Default Value: If desired, enter a default value for the date/time field.
  • Aptify's supports the GETDATE() function that returns the current date or date/time combination depending on the chosen data type. Note that the GETDATE function only returns the date element of a DateTime field.
  • Starting with 5.5, Aptify supports using the function GETDATETIME to populate both the date and timestamp of fields of type DateTime.

Time Field

To create a time field that does not include a date component, configure the following option:

• SQLDataType: Use a data type of time.
• Default Value: If desired, enter a default value for the time field.
  • Aptify's supports the GETDATE() function that returns the current time.
  • Starting with 5.5, Aptify also supports using the function GETDATETIME to populate both the date and timestamp of fields of type DateTime.

Link Box

A link box provides a link to another entity, typically to the other entity's ID field. This field creates a foreign key in the database and establishes a one-to-many relationship between the new entity and the related entity. To create a link box, configure the following option:

• Name: Aptify's standard naming convention for link boxes is the name of the linked entity (in its singular form) and the linked entity field. For example, the name of a field linked to the Applications entity's ID field would be ApplicationID.
• Display Name: Specify the name that you want users to see as the link box's caption on generated forms.

• Linked Entity: Select an entity from the drop-down list.

  When you save a linked field for the first time, a message box appears asking if you want to create a virtual field for the linked field. See Joined Virtual Field for details. Likewise, when you delete a linked field and one or more virtual fields exist for the linked field, a message box appears asking you if you want to delete the virtual fields as well.

• Linked Entity Field: Select a field in the Linked Entity from the drop-down list; this field is used as the foreign key. In general, the Linked Field is the ID field of the Linked Entity. However, other fields also appear in the drop-down list. To qualify as a Linked Field, a field in a related entity must meet the following criteria:
  • The Linked Field must be a primary key (the ID field) or a unique key in the Linked Entity. (This is a SQL Server requirement.)
  • The field in the Linked Entity must have the Is Required option selected. (This is an Aptify requirement.)
    
    
• Values Tab: You only need to configure the Values tab if you want to enable the Multiple Selection dialog for In List filters. See Values Tab for details.
• Options Tab: Do not select the Default in View if you intend to create a Joined Virtual Field.
• Validation Tab: To create a required foreign key, select the Is Required field; a user will need to specify a value for the link box before he or she can save a new record in this service. To create an optional foreign key, clear the Is Required field; a user does not need to specify a value for the link box before saving a new record in this service.
  
  

The example in the figure below illustrates a field that is linked to the Application entity's ID field.

Joined Virtual Field

In a view, the value of the field shown in the above figure will be the ID of the Applications record that the user enters into the link box. To display a different field from the Linked Entity in a view (such as the Applications record's Name field), you need to add a virtual field to the entity. Aptify automatically adds virtual field information to an entity's Base View.

Aptify includes a wizard that assists with the creation of joined virtual fields. You can use this wizard or manually create the field yourself.

Using the Wizard to Create a Joined Virtual Field

The Aptify Entity Virtual Fields dialog appears automatically each time you save a new field that has a value selected for the Linked Entity field. You can also launch this dialog at any time by clicking the Add Virtual Fields... icon in the Fields tab's toolbar.

Follow these steps to create a joined virtual field using the dialog:

1. Create a Link Box or Data Combo Drop-down List field and click OK to save and close the Fields record.
   • A message dialog appears automatically.
     
     
2. Click Yes to open the Aptify Entity Virtual Fields dialog.
   • You can also open the dialog at any time by clicking the Add Virtual Field... icon in the Field tab's toolbar. Note that an entity must have at least one linked field before the dialog will open.
     
     
3. If the entity's Fields list has more than one linked field, select the linked field for which you want to create a virtual field from the Linked (Foreign Key) Field drop-down list.
   • The wizard loads and displays all of the fields from the entity linked to the field you selected.
   • If the linked field you created in Step 1 is the first or only linked field in the entity, the wizard loads and displays all of the fields from that linked entity automatically and the Linked (Foreign Key) Field is grayed out.
   • If one or more virtual fields already exist for the linked field, these existing virtual fields are grayed out within the wizard. Note that you cannot modify the settings for an existing virtual field within the wizard; in this case, you need to edit the corresponding Fields record.
   • The Is Required column helps you identify which of the fields are required to save a record in the related entity. You can ignore this column when creating a virtual field for a Standard link. (this column is important for Embedded links — see Understanding the Embedded Object Model for details).
     
     
4. Select the Create column for each field for which you want to create a virtual field.
   • Typically, you want to create a virtual field for the linked entity's Name or Display Name field.
   • When you select the Create column for a field, the wizard also selects the Default In View column automatically.
     
     
5. If you do not want the Virtual Field to appear in views by default, clear the Default In View column option.
6. Enter a name for the field in the Virtual Field field.
   • Aptify's standard naming convention for virtual fields associated with a linked entity field is the name of the link box field plus _Name. For example, the virtual field for an ApplicationID link box would be named ApplicationID_Name.
   • The name cannot contain spaces or dashes.
     
     
7. Specify the name that you want users to see as this field's column heading in a view in the Display Name field.
8. Enter a Description for the virtual field (optional).
   
   
9. Click OK to create the field.
   • The virtual field is added to the entity's Fields list.
   • If you want to review the virtual field's configuration, you can open the new Fields record.
     
     

10. Add any additional fields and save and close the entity.

    • The virtual field information is automatically added to the entity's base view.

Manually Create a Joined Virtual Field

Open a new Fields record and configure the following fields:

• Name: Aptify's standard naming convention for virtual fields associated with a link box is the name of the link box field plus _Name. For example, the virtual field for an ApplicationID link box would be named ApplicationID_Name. The name cannot -contain spaces or dashes.
  
  
• Display Name: Specify the name that you want users to see as this field's column heading in a view.
  
  
• SQL Server settings: You do not need to specify these settings since this field will not be in the table. However, typically the SQL Data Type and SQL Field Size should be the same as the field to which the virtual field refers.
  
  
• Options Tab: Configure the following options:
  
  • In Table: Clear the In Table option under the Options tab. A Virtual Field tab displays automatically.
  • Default In View: Select the option to display the virtual field in list views of this entity by default.
    
    
• Virtual Field Tab: Configure the following fields on this tab:
  
  • Virtual Field Type: Select Joined.
  • Joined Entity: Select the entity that appears in the link box's Linked Entity field.
  • Joined Field: Select the field from the Joined Entity whose value you want to display in views.
  • Join Foreign Key: Enter the name of the corresponding link box field you created (such as ApplicationID). This field fills in automatically when you specify a Joined Entity.
    
    
• Join Type: Select a Join Type. See Join Type for details. In general, use Optional for if the related linked field is not required and Required if the related linked field is required.

Standard Combo Drop-down List

To create a combo drop-down list field, configure the following options:

• Top Area and General Tab: Configure the fields in these areas as necessary.
  
  
• Values Tab: Configure the following:
  
  • GUIType Single Select: Select Supported.
  • Value List Type: Select Static List.
  • Values Sub-Type: Add one Values sub-type record for each possible field value. In the example shown in the figure below, the static list contains the Active and Inactive values. These are the values that will appear in the drop-down list on the generated form. Note that the values enter in the value list can not exceed the SQL Field Size for the specified field. You can also localize the values associated with a standard comb drop-down list. See Localizing Standard and Data Combo Value Lists for more information.

Data Combo Drop-down List

A Data Combo drop-down field displays a list of records from another table in the database. A field of this type may or may not be linked to a field in another entity.

You should use this control if you want to provide users with a drop-down list of pre-populated items from another table or entity. By specifying a SQL statement, you define the drop-down's list contents and you can display more than one column from the corresponding table or entity.

The example in the figure below shows a data combo drop-down field that displays the records from the Applications service. In this example, the Application's ID and Name appear in the drop-down list. When a user selects one of the applications from the list, the system stores the Application's ID as the contents of this field and displays the Application's Name within the field when displayed on the form.

Use the following settings to create a data combo drop-down list:

• Top Area and General Tab: Configure the fields in these areas as necessary.
  • SQLData Type and SQLField Size: Configure the SQL Server settings for this field to match the type of data that will be returned by the SQL statement you specify on the Values tab.
  • Linked Entity (optional): If you want to link this field to an existing entity, select an entity from the drop-down list. Otherwise, you can leave this blank.
    
    

• • Linked Entity Field (optional): If you configured Linked Entity, select a field in the Linked Entity from the drop-down list; this field is used as the foreign key. In general, the Linked Entity Field is the ID field of the Linked Entity. Otherwise, you can leave this blank. Note that for a field to qualify as a Linked Field, it must meet the following criteria:
  • The Linked Field must be a primary key (the ID field) or a unique key in the Linked Entity. (This is a SQL Server requirement.)
  • The field in the Linked Entity must have the Is Required option is selected. (This is an Aptify requirement.)
    
    
• Values Tab: Configure the following settings:
  • GUIType Single Select: Select Supported.
    
    
  • GUIType Multi Select: If you want to enable the Multiple Selection dialog for In List filters, select Supported - Standard. Otherwise, you can leave this set to Not Supported.
    
    
  • Value List Type: Select SQL Statement.
    
    
  • Values Sub-Type: Add one Values sub-type record for this field. Enter the SQL statement to execute in the Values record's Value field.
    
    • For example, the following SQL statement returns the ID and Name of Applications records: SELECT ID,Name FROM APTIFY.dbo.vwApplications
    • A Data Combo Box form component includes a ValueField input property that determines which of the fields returned by the SQL statement is saved to the database. If the ID field is specified in the SQL statement, the Form Template Generator specifies the ID as the ValueField. If the ID field is not specified, the Form Template Generator uses the first field specified in the SQL statement.
    • Note that in the example above, the data combo field's SQL Data Type and SQL Field Size should match the settings for the Applications entity's ID field since that data value will be stored for the field. The field's SQL Data Type and SQL Field Size should match the values for the Data Combo Box's ValueField.
    • A Data Combo Box form component also includes a DisplayField input property that determines which of the fields returned by the SQL statement to display as the value on the form. If a Name field is specified in the SQL statement, the Form Template Generator specifies the Name as the DisplayField. If a Name field is not specified, the Form Template Generator uses the second field specified in the SQL statement. If only one field is specified in the SQL statement, then the Form Template Generator uses that field as the DisplayField.
    • During the entity save process, the system will prompt you if it detects an invalid SQL statement in a Values sub-type record.
    • You can also localize the values associated with SQL-sourced data combo boxes. See Localizing Standard and Data Combo Value Lists for more information.
      
      
  • Validation Tab: If marked as Required, the data combo box defaults to the first record in the list for new records. If marked as Not Required, the data combo box default to blank and the blank option remains available in the drop-down list.
    
    

Extended Attribute Fields

Aptify provides 17 extended field types. You can assign one of these extended types to a field to enable additional functionality for that field on the entity's generated form.

For example, if you mark a field with the WebURL Extended Type, an Internet Explorer icon appears next to the field on the entity's generated form. When a user enters a Web page in this field and clicks the icon, Internet Explorer opens to the page specified in the field.

To add extended functionality to a field, configure all necessary options for the field and then select the desired type from the Extended Type drop-down list.

See Extended Type for the complete list of Extended Types.

Calculated and Computed Virtual Fields 

Calculated and Computed Fields are virtual fields that do not exist as fields in a database table. These fields use a specified transact-SQL expression to derive the value from other fields in Aptify. For Calculated Fields, this expression can include any field in the current entity, as well as subqueries to fields in other entities. For Computed fields, this expression can only reference fields that are in the same Base Table as the Computed field (that is, the entity's primary Base Table).

Aptify dynamically calculates the field's value either at the table level (for Computed fields) or within the Base View (for Calculated fields). Note that since virtual fields are not In Table, these fields are also not Updateable. Therefore, Calculated or Computed fields do not appear on generated forms.

Follow these steps to create a Calculated or Computed field:

1. Open a new Fields record in the entity.
2. Enter a name in the Name field.
   • A field's Name cannot contain spaces or dashes.
     
     
3. The Display Name field populates automatically when you click off of the Name field. Modify this name as necessary.
   • The system uses the Display Name as the label for the column heading in list views. In other words, this is the name that users will see.
   • The Description field on the Info tab also populates automatically with the value you specified in the Name field.
     
     
4. If creating a Computed field, specify the entity's primary base table in the Base Table. Note that the fields that will be referenced in the Computed field's SQL expression must also reside in the primary base table.
   
   • A Computed field and all of the fields that are referenced in its calculation must be in the entity's primary base table. See Creating Multiple Base Tables for information on using multiple base tables.
   • You can skip this step if creating a Calculated field, since these fields are not associated with a particular table.

   

5. If you are using localization, specify a the culture string that corresponds to this field's name in the Display Culture String field. This field is optional; see Localization for more information.
6. Configure a SQL Data Type under the General tab, if desired.
   • For virtual fields, Aptify uses this selection to determine the appearance of the field within the viewing system. For example, if the virtual field performs a mathematical operation on currency fields, you may want the virtual field to display as a currency value. In this case, you would set the SQL Data Type to Money.
     
     
7. Click the Options tab.
8. Clear the In Table option to display the Virtual Field tab.
9. Click the Virtual Field tab.
   
   
10. Select Calculated or Computed from the Virtual Field Type drop-down list.
    • Calculated: Indicates that the virtual field is calculated from other values, including values from fields in other entities. The information is not stored in the database table but the relevant information is added to the entity's Base View.
    • Computed: Indicates that the virtual field is calculated from other values within the same table. Unlike a Calculated field, which is calculated within an entity's Base View, a Computed field is calculated at the entity's primary database table. Note that a Computed field is not a physical field in the table even the Computed field appears in the table details when viewed using SQL Server Management Studio. You cannot create a constraint or an index for a computed field. (Aptify does not create Persisted computed columns.)
      
      
11. Enter the SQL expression that defines the value for the virtual field in the Calculated or Computed field.
    • This expression may include mathematical or string operations on one or more fields. For Calculated fields, it can include any field in the current entity as well as subqueries to fields in other entities. For Computed fields, the expression can only reference fields that are in the entity's primary Base Table, which you -specified in the Field's top area.
    • When Virtual Field Type is set to Calculated, the text in the Calculated Field appears in the SELECT section of the base view once the entity is saved. When Virtual Field Type is set to Computed, the text in the Computed Field is stored in the field's specified base table.
      
      
      
      
12. Click OK to save and close the Fields record.
13. Save the entity.

Money Fields

In Aptify, a field that uses the money or smallmoney SQL Data Type can be expressed in one or more currencies, if the form also includes a CurrencyTypeID linked field. Then, when a user specifies a value for money field on an entity's record, Aptify automatically expresses that value in the selected Currency Type, using the appropriate currency symbol.

Follow these steps to add a money field that can be expressed in multiple currencies:

1. If one does not already exist, add a CurrencyTypeID linked field to the entity.
   • When configuring a money field that supports multiple currencies, you must specify the CurrencyTypeID linked field that will define the field's currency type. This can be either a linked field in the current entity or its parent (if a sub-type entity).
     
     
2. Open a new Fields record in the entity.
3. Enter a name in the Name field.
   • A field's Name cannot contain spaces or dashes.
     
     
4. The Display Name field populates automatically when you click off of the Name field. Modify this name as necessary.
   • The system uses the Display Name as the label for the column heading in list views. In other words, this is the name that users will see.
   • The Description field on the Info tab also populates automatically with the value you specified in the Name field.
     
     
5. Select money or smallmoney from the SQLData Type drop-down list.
   • The Supports Multiple Currencies option box becomes enabled.
     
     
6. Select the Supports Multiple Currencies option.
   • The Currency Type Field becomes enabled.
   • If you leave this option cleared, this money field is currency-agnostic (in other words, values in this field will not be treated as having a particular currency type).
     
     
7. Select a pre-existing CurrencyTypeID linked field from the set of available fields in the Currency Type Field drop-down list.
   • This field displays the set of available CurrencyTypeID fields already configured for this entity and its parent entity (for sub-type entities). Therefore, you must add one or more CurrencyTypeID linked fields to the entity before adding any multi-currency money fields.
   • When a user specifies a currency type on the entity's record, Aptify automatically knows that a value specified in this money field is expressed in the specified currency.
     
     
     
     
8. Continue configuring the field as necessary. See General Steps for Creating a Field for more information.