Release 10.1A: OpenEdge Development:
Progress Dynamics Basic Development

> ## Defining and using dynamic combos

> The dynamic combo choice is appropriate if the total number of possible valid values to choose from is limited to around 50 or fewer, depending on your user interface preferences. More values than this will not fit easily into the drop-down list of the combo box visualization. For a larger set of values, use the dynamic lookup.

> Once you have built a static viewer, you can easily add one or more dynamic combo objects to it.

> | To replace the SalesRep field in the viewer with a combo with a list of all `SalesRep` records:

> 1. Open the viewer in the AppBuilder.

> 2. Right-click on the SmartDataField icon on the AppBuilder palette, as shown:

> A pop-up menu appears and lists the predefined objects of this type.

> 3. From this list, select the dynamic combo.

> The mouse cursor changes to look like the icon for a fill-in field.

> 4. Position the cursor over the field you want to replace, and left-click. The field’s visual representation is replaced by a field that looks like a drop-down list, with a ventilator icon on the left, as shown:

> Immediately following this change, the Choose Existing SmartDataField dialog box appears, as shown:

> 5. Select an existing field from the browse, or click Create New SDF. In either case, the SmartDataField Maintenance window appears where you can edit the SDF properties or specify properties for a new SDF, as shown:

> The SDF Maintenance window allows you to specify the instance properties that apply to only this instance of the SDF. If you are using the SDF Maintenance tool to create a new SDF, then you are also setting the master SDF properties for this SDF.

> Use the fields at the top of the window if you decide to save a combo definition as a named object in its own right, or if you want to use an object saved earlier as the basis for a new one. The following sections describe the elements of the SmartDataField Maintenance window.

> ##### Specify base query string

> Double-click in the base query string area to display the Query Builder dialog box. Here you can visually build a Progress query definition that identifies the data set that should appear in the combo box. Often this will be a simple `FOR EACH` statement.

> Follow these guidelines as you write the query:

> - The query must start with the `FOR` `EACH` keywords.

> - The query must not end in a period or colon.

> - The query must not include the `END` statement.

> - In the simplest (and probably most typical) case, the appropriate query definition is:``

>       FOR EACH <table-name> NO-LOCK

>   ``

> - The query should always include the `NO-LOCK` keyword because it will be reading values for selection only.

> - The query can join to as many tables as you want.

> - You can place the tables in any order.

> - The tables can contain any `WHERE` clause you require.

> - The query can also contain a `BY` clause if necessary.

> - The query should include any tables whose field values you want to display in the combo box.

> - Table names should not contain a database prefix.

> When you have finished typing in the query, choose Refresh.

> The following other items in the property sheet are filled in from the database schema information for the field:

> - Query tables — This read-only field is derived from the query you typed. It is a list of the database tables used in the query.

> - Browser fields — This browser shows all the fields in the query tables.

> - Field name — This read-only field shows the name of the field whose value will be assigned when a user selects an entry from the combo box list. This is the field you originally placed on the viewer and replaced with the dynamic combo.

> Caution: When an SDF is on a datafield in a viewer, the name of the SDF and the datafield must match or the viewer will not function correctly. While you cannot change the field name here, you could do so by accessing the Field Name.

> - Key field — This is the field whose value you should assign to the External Field on completion of selecting a value in the combo. You can use any of the fields from any of the buffers in the specified query, but the field you select should have the same data type as the external field.

> - Field label — This is the label for the dynamic combo object in the viewer. It is initialized to the label of the Key Field, but if needed, you can change it to some other sensible label for the External Field.

> - Datatype — The data type of the selected Key Field. This is a read-only value.

> - Format — The format of the selected Key Field. This is a read-only value.

> - Field width — Specifies how wide this instance of the SDF should appear in the viewer.

> - Build sequence — Defines the order in which objects are initialized at run time in a frame or window,

> - Enable Field — Specifies whether this instance should be enabled for input.

> - Display Field — Specifies whether this instance should be displayed when the viewer is displayed. When a fill-in widget is dropped onto a viewer, the Display toggle box is checked by default. Remove the Display toggle box to use the field as a parent field. When checked, Display causes the widget to lose its current value before it can be used as a parent field in a combo or lookup.

> - Sort — Specifies whether the values in the combo should be alphanumerically sorted in the list.

> ##### Browser fields

> It is likely that the foreign key value you assign will not be very meaningful to the user. In fact, if it is an arbitrary numeric key value, such as a Progress Dynamics object ID, you normally will not want to display it at all. For this reason, the dynamic combo allows you to display any meaningful value you like for the user to choose. The Key Field is the value that is assigned regardless of what you choose to display to the user.

> So, from the browser containing all the available fields in the query tables, you can select one or more whose values you want to display as a single concatenated string in each combo box entry. Choose fields that will have a meaningful descriptive value for the user. To select a field, click in the Display Sequence cell for that field, and change the 0 to a sequence indicating the order in which you want the field values displayed (so 1 for the first, 2 for the second, etc.).

> Once you have entered a display sequence for one or more fields and tabbed out of the field browser, the list of fields you have chosen is displayed as the Description Fields, and a default Description Substitute is shown.

> ##### Description substitute

> If you select more than one field to display in the combo entry, the Description Substitute field is enabled and displays a default substitution string to be applied to the values. The default is
> `&1 / &2`. This string means that the first Description Field will be shown, followed by a slash, followed by the second field. You can edit this string if you want to use a different string to separate the values.You can also change the order of the fields by changing the numeric sequence of the substitution arguments in the string (&1 and so on).

> ##### Field label

> This is the label of the displayed field to show on the viewer.

> ##### ToolTip

> Enter a ToolTip that will help the user understand what value is being requested here.

> ##### Inner lines

> In this field, specify the number of inner lines you want associated with the combo; that is, the number of choices visible when the combo is in the drop-down position. The default is 5. If the number of inner lines is less than the total number of items in the combo’s list, the user will have to scroll down the list to see all the items.

> ##### External field data type and format

> These display-only fields on the right side of the property sheet show the data type and the display format of the External Field. It’s possible that the Key field might not be the same as the external field. Although you can select any field for the Key field, you must ensure that you choose one with a matching data type of the external field.

> ##### Field width

> This fill-in on the right side of the property sheet shows the width in character units of the Displayed Field in the viewer. Normally, you can easily modify this value visually, by using the grab handles on the widget itself in the viewer design window. If you need more precise or standardized sizing, you can set this fill-in.

> ##### Build sequence

> If you have multiple dynamic combos that have a parent-child relationship and you need to have a value in one combo before you build data for the other, then you must set the Build Sequence field for each combo. The Build Sequence indicates the order in which data for the combos must be retrieved. The following example shows how to specify the Parent Fields and Parent Filter Query for the dependent combo in this kind of parent-child relationship. The Build Sequence for the child combo is set to 2; and the Build Sequence for the parent Product Combo is set to 1, to ensure that the parent data is populated prior to the child data.

> ##### Other tab options

> In some cases, you might want a combo that gives you an All or None choice. The ComboType radio set on the Other tab provides this option. Data only, the default, specifies a normal combo without an All or None entry. None means that the user is effectively defining a record where the foreign key dependency is simply not established. Whether this is a valid option depends on the referential integrity rules of your own application. If it is valid, for example, to create a `Customer` record without assigning it to a `SalesRep`, then None is a valid choice. If your business rules forbid this, then it would not be. Since the combo box widget does not support a blank as a valid value for the visual item list, and since it can be visually awkward to select a blank value from the list, the dynamic combo lets you put a value in the list that is visually represented by the string <None>. You can then map it in the Default Value field (described below) to any appropriate value for the database field.

> Likewise, where the choice list represents a filtering mechanism of some kind, All might be a logical choice to indicate that no filtering is to be done. For example, many of the screens in the Progress Dynamics tools themselves (which are largely built in the framework) provide combos used to filter a selection, for example to filter objects by their Product Module or to associate a user login with a particular Login Company. If you want to see objects in all Modules or if you want to log in with the privileges of all companies, then you can provide <All> as an option in the choice list and map it to a meaningful value for the underlying field.

> Select <None> and Data if you want the value <None> added to the choice list. Select <All> and Data if you want the value <All> added to the choice list. Otherwise select Data Only.

> ##### Default value

> If you select <None> and Data or <All> and Data for the Extra Options, then the Default Value field is enabled. In this field, enter the value that you want mapped to that option. You can use any value your application interprets as the default value in the Key Field, representing All or None.

> ##### Parent fields

> In some cases you might have multiple combos in a single viewer that represent a parent-child dependency. Products and Product Modules are an example of this kind of relationship. If there were a `Region` field in the `Customer` table, and `SalesReps` were organized by `Region`, then the user might select a `Region` for a `Customer` in one combo and then select from a separate combo a `Sales Rep` from that `Region`. The list of valid Modules in the first example must be created after the Product is chosen, since Modules are organized by Product. Likewise the list of valid choices for `Sales Rep` must be created after the `Region` is selected. The dynamic combo gives you the ability to modify the query for the child Combo in this kind of relationship, so that you get the right list of values.

> If you are defining a child combo in a parent-child relationship, you can enter the name of the key fields in the parent that should be used to filter the query for the child combo. The parent does not need to be another combo. It could be a lookup, or it could simply be an ordinary field or fields in the viewer. There is no need for the parent values to be values for database fields. They are just field names in the viewer.

> If you use more than one parent field, separate them with commas.

> Note: If a dynamic combo’s parent field is an object other than another dynamic combo, you should run the `RefreshChildDependancies` procedure in the dynamic combo to let it know that it should refresh its contents because the value of the parent field might have changed. This procedure engages the parent filter query on the combo. Any other dynamic combo does this automatically when it is a parent field to another dynamic combo on the viewer. Run the RefreshChildDependancies procedure in the handle of the combo whose query you want to change. Pass in the name of one of its parent fields or the one whose value has changed. If your combo’s parent filter query is dependent on more than one field, you only need to pass in one of its parent fields and it will fetch the values of the rest automatically.

> ##### Parent filter query

> If the combo is the child object in a parent-child relationship, then you must enter the query phrase that will be added to the child’s query to filter the records based on the parent values. If you define values for Parent Fields, enter the query phrase in the Parent Filter Query field, using Progress substitution arguments (&1, &2, etc.) to represent the Parent Fields.

> The query string must be a syntactically correct Where-clause phrase, but not a complete query statement. So it might not contain the words `WHERE, EACH, FIRST, LAST, NO-LOCK, SHARE-LOCK`, or `EXCLUSIVE-LOCK`, or a comma (,) or colon (:). Since you cannot check the syntax at design-time, it is important that you test this query properly. If the query fails at run time due to any syntax error, the combo will simply be blank. In such a case, you must check the AppServer log file for an error message.

> Functionality has been added to the SmartDataField Maintenance tool for Dynamic lookups and Dynamic combos to allow the developer to specify the parent filter query with a pipe (|) delimiter to indicate where that string should be applied, as follows:

>     FOR EACH customer NO-LOCK, FIRST order OF customer NO-LOCK

> In the previous example, the parent filter query would be applied after the joined table. To force this to be placed before the first join, the Parent Filter query should look as follows:

>     Customer.CustNum = INTEGER('&1')|

> Only the pipe (|) added at the end tells the program to place the filter string as part of the first table. If we change the Parent filter field as follows:

>     |Customer.CustNum = INTEGER('&1')

> Then, with the pipe (|) at the front, the program will add the filter string after the second table join. If you do not specify a pipe (|), it will always add it after the last joined table. This is to ensure backward compatibility. When using the pipe(|) delimiter in the filter string, you no longer have to specify AND as a joining operator between to filter strings, unless that belong to the same table.

> To filter on the customer number and the order date, do the following:

> - In earlier versions, the code looked like the following:

>     Customer.Custnum = INTEGER('&1') AND Order.OrderDate = DATE('&2')

> This is resolved at runtime in the following way:

>     FOR EACH Customer, FIRST Order OF Customer WHERE Customer.CustNum =
>     INTEGER('1') AND Order.OrderDate = DATE('01/01/2001') NO-LOCK

> - In Version 2, the code looks like the following:

>     Customer.Custnum = INTEGER('&1')|Order.OrderDate = DATE('&2')

> This is resolved at runtime in the following way:

>     FOR EACH Customer WHERE Customer.CustNum = INTEGER('1'), FIRST Order OF
>     Customer WHERE Order.OrderDate = DATE('01/01/2001') NO-LOCK

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095