Friday, June 19, 2015

Create a Dynamic List Item




In the name of Allah, Most Gracious, Most Merciful
Praise be to Allah, blessing and peace be upon our prophet Mohammed, his family and his companions. After that,


Task Definition: Create a Dynamic List Item

Let's have a quite simple application, e.g. "Club Membership" application involved  two simple tables 'YEARS' and 'MEMBERS' with two abstract column contents; id and name column for each table individually. Now, we want to populate two list items dynamically for each individually.



Task Execution Idea:  You can follow the following logical steps to accomplish the task safely and properly...


Procedure


   ''ADD_LIST_ELEMENT''Using the following procedure 

        Syntax

       ADD_LIST_ELEMENT(list_id ITEM, list_index VARCHAR2, list_label VARCHAR2, list_value VARCHAR2);

o   Syntax Specifications

o   list_id: Specifies the unique ID that Oracle Forms assigns when it creates the list item.
o   Use the FIND_ITEM built-in to return the ID to an appropriately typed          variable.  The data type of the ID is ITEM.
          Example:  v_ stage  ITEM  := FIND_ITEM ('STAGE.st_id');
o   list_index:  Specifies the list index value.  The list index is 1 based.
o   list_label  Specifies: the VARCHAR2 string that you want displayed as the label of the list element.
o   list_value:  The actual list element value you intend to add to the list item.
o   Get_List_Element_Value: access the current index number for each loop independently. 

·        This procedure adds only a single element to a list item. 
·        This is not practical for multi-list values…

So it's more logical to think about looping through the whole database records stored in your 'List Item' table.
          Consequently, it's more appropriate use For…. Loop structure.
   
·      It's a dynamic procedure; it executes run-time. You have to free the memory from any old value to get actual and correct values result

·      built-ins used as needed:

o   CLEAR_LIST Built-in: Clears all elements from a list item. After Oracle Forms clears the list, the list will contain only one element the 'Null' value, regardless of the item's required property.
o   GET_LIST_ELEMENT_COUNT Built-in: Returns the the current index of the list item.

. 
Task Solution: 


Let's follow the above mentioned solution idea concerning…


 
ADD_LIST_ELEMENT(list_id ITEM, list_index VARCHAR2, list_label VARCHAR2, list_value VARCHAR2);


Pls. Follow the logic of the following steps to do the task safely and properly...



DECLARE

     v_count    NUMBER := 1 ;

     CURSOR  year_cur  IS SELECT year_id , year_name
                                          FROM  YEARS ORDER BY  year_id;

     v_ year  ITEM  := FIND_ITEM ('YEARS. year_id');


    CURSOR  member_cur IS SELECT member_name , member_id
                                           FROM  MEMBERS ORDER BY member_id ;

    v_ member ITEM  := FIND_ITEM ('STAGE. member_id');

BEGIN 
                   
     CLEAR_LIST (v_ year);

     FOR  m in year_cur LOOP

/** Get_List_Element_Value access the current index number for each loop independently.
This helps Determine the total number of list elements. **/

     ADD_LIST_ELEMENT t(v_ year  , GET_LIST_ELEMENT_COUNT(v_ year)+1,  m. year_name , TO_CHAR(m. year_id));

END LOOP;


/* The traditional technique */


  CLEAR_LIST (v_ member);
   
 FOR  z in member_cur LOOP

     ADD_LIST_ELEMENT (v_member, v_ member +1 , z. member_name , TO_CHAR (z. member_id ));

 END LOOP;

END;


Note:

o   You may face a Compilation error i.e.
  FRM-30351: No list elements defined for list item
  and a run time error i.e. unable to resolve reference to the item...
o   Although you fill your list item dynamically at runtime, but the 'Null' value always displays in the List Item.
      
           Key Reason: By default, the property of the list item 'Required' is set to 'YES'.
  
       Key Solution:

               *       Reset List item property: 'Required' value to 'NO'.
               *       You must provide 'dummy' values: let's say 0.


For more advanced techniques pls. read dependent drop down list

Hope this helps…

My success only comes from Allah, pls. note your comments and suggestions are great help for me in progress thanks in advance.