How to use a list item

[bookouri]

Im using developer 6i forms. I have several thousand records in an existing database. My forms use several list_items... for example job title. My drop down for job title has several selections. The problem I have is that we have job titles that are in the database that no longer active so we dont have them in the list_items. If i do query on these records, they dont appear to exist. If I change their job title to one that IS in the list_items, they DO show up in a query. Since things such as job titles are subject to change how can I insure that even inactive titles appear in queries? I dont want to allow users to select inactive job titles.

thanks for any suggestions

 

[bookouri]

after some more research Ive found that this behavior is by design. According to Oracle documentation any record that does not match an item in the list box will be "silently" rejected. I have found notes relating to the "mapping of other values" part of the "functional" section under the list box properties. But the notes I have found make absolutely no sense and do not really explain how to configure this "mapping of other values"

 

[lewisp]

I personally dont like relying on fixed drop down boxes for database items. Instead I populate the drop-down boxes at run-time so the list is always up-to-date and dynamic.

As you say, a record will be rejected if the value of a data item is not in a drop down list. The whole record will be blank even thought the space will be taken up on the form....

Fixed drop downs are useful for values that will not change, such as 'Yes' and 'No'.

 

[bookouri]

All of the fixed drop downs Im using have less than 6 selections so I havnt had to use any dynamic lists. I figured that loading up the list box would be pretty time consuming, and it would load values that are no longer supposed to be selectable by users.

I figured out how to use the "mapping of other values"

it's not a really good way to handle it, but it seems to be the only way developer will do it. In each drop down, I have added an "unknown" or "none" to the selection. If you then put that value in the "mapping of other values" section, developer will display any non-valid field contents as "unknown" or "none" you dont get the REAL value, but at least the records are not "silently" discarded"

 

[kashv]

try to populate
your list dinamically from a record group.
The format of query in the record group is like this:

SELECT to_char(id_title) value, title label
FROM t$title
order by title

then use the function from block's pre-select trigger:

/*
p_rg_name - name of record group
p_list_name - name of list-item
*/
PROCEDURE create_query_list (p_list_name VARCHAR2, p_rg_name VARCHAR2) IS
the_Rowcount NUMBER;
rg_id RecordGroup;
gc_id_label GroupColumn;
gc_id_value GroupColumn;
Exit_Function Exception;
status number;
BEGIN

Clear_List(p_list_name);

/*
** Determine if record group exists, and if so get its ID.
*/
rg_id := Find_Group( p_rg_name );
IF Id_Null(rg_id) THEN
msg_box2('Record Group '||p_rg_name||' does not exist.');
RAISE Exit_Function;
END IF;
/*
** Make sure the column name specified exists in the
** record Group.
*/
gc_id_label := Find_Column( p_rg_name||'.label');
IF Id_Null(gc_id_label) THEN
msg_box2('Column "label" does not exist.');
RAISE Exit_Function;
END IF;
gc_id_value := Find_Column( p_rg_name||'.value');
IF Id_Null(gc_id_value) THEN
msg_box2('Column "value" does not exist.');
RAISE Exit_Function;
END IF;

/*
** Get a count of the number of records in the record
** group
*/
status := Populate_Group( rg_id );
the_Rowcount := Get_Group_Row_Count( rg_id );
/*
** Loop through the records, getting the specified column’s
** value at each iteration and comparing it to ’the_value’
** passed in. Compare the values in a case insensitive
** manner.
*/

FOR j IN 1..the_Rowcount LOOP
Add_List_Element(p_list_name, j, GET_GROUP_CHAR_CELL( gc_id_label, j ) , to_number(GET_GROUP_CHAR_CELL( gc_id_value, j )));
END LOOP;
/*
** If we get here, we didn’t find any matches.
*/
RAISE Exit_Function;
EXCEPTION
WHEN Exit_Function THEN
RETURN;
END;