How do you make a dynamic list default to a database value?

[Vanessa]

Question
I have a drop down built from a dynamic list, in which the display text will be the 'List_Value' (e.g. country name) and the value will be 'List_Id' (e.g. country ID).

Those 'List_Value' details are retrieved from TableA and before the form is displayed, a record is retrieved from TableB which contains a 'List_Id' field.

How can I get the drop down default to the 'List_Value' that matches the 'List_Id' retrived from TableB?


(one) Answer
Assuming that:
- the dynamic list is built from database table 'tableA '
- the data is fetched from a database table 'tableB'
- the drop down field should to default to the value in the database table (tableB) when it is built.

Assuming you have a simple form with a dynamic list and 3 form fields, ID, List_Value and List_Id

In tableA you have list fields List_Value and List_Id
In tableB you have column field ID (and others but these are not relevant to this)

You also have ID set to anything that will identify the record in TableB

Database resources
1. Do a 'fetch' on tableB with a database resource using that ID in the 'where' clause. This will retrieve List_Value and List_Id from the database for that ID (along with other record details).

2. The fields in the database resource are mapped to the form fields corresponding to the list fields, therefore List_Value is mapped to List_Value, etc

This retrieves the List_Id from tableB and stores it in a form field.

3. Then you need to do another fetch on the same database (TableA) that is used to build the dynamic list where you fetch the List_Value and List_Id. In the 'where' clause, you must then filter the results by List_Id (which is the value retrieved from tableB):

[where] List_Id=&&List_Id

4. Immediately after the fetch set the value of the dynamic list according to the value retrieved from the last fetch:

set dyn_list_field = List_Value;

Step 4 - the 'set' statement can be ommited if you map the list field 'List_Value' in the second database resource directly to the dynamic list, in the field mappings dialog in the form.

Therefore, in the field mappings dialog, map 'List_Value' to 'dyn_list_field'.