• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1090
  • Last Modified:

Select multi data from tlist and store them in a variable

hi,

I'd like to know how to ago about for the multi selection option for the TList component. (i.e) I want to select more than one data from the TList1 and add them to TList2 then store all elements which added in TList2 in variable to create a select statement.

many thanks
0
khaledsoliman2001
Asked:
khaledsoliman2001
  • 3
  • 3
  • 3
  • +1
1 Solution
 
Helena Markováprogrammer-analystCommented:
You cannot select multiple elements of T-list item. You have to use some work around e.g. create a block with check boxes=TList1 and checked values pass to the TList2...
0
 
khaledsoliman2001Author Commented:
Dear Henka
my proplem is:
I have some tables in my DB, when I choose a table from a list the T-list1 will fill with the table coulmns, so I need to choose some of this coulmns which add in the T-list1 and create a select statement from the choosen T-list elements.
what is the Ideal method to do that?
sorry for poor English
0
 
Helena Markováprogrammer-analystCommented:
I think that you can achieve the same functionality:
block 1 - tables        master block
block 2 - columns    detail block. In this block you can add a check box (it is not based on a table column).
User can check some rows in the block 2 (= table columns) and you can LOOP through this block and create a desired select statement (e.g. in a When-Button-Pressed trigger of button 'Create statement').
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
khaledsoliman2001Author Commented:
Sorry , could u explain step by step
how can I add tables names in block and the coulmns in other block?
and is it possible to change the number of checkboxes debended on the number of table's coulmns because the coulmns in each tabel in not the same number?
0
 
musduCommented:
Or you can use a hierarchical tree to allow multi selection.

regards.
0
 
schwertnerCommented:
2 years ago Mr. M-Ali and I have answered how to construct hierarchical tree.
This is a very nice feature of Forms introduced beginning with Forms 6i.

Look in
http://oldlook.experts-exchange.com:8080/Databases/Oracle/Q_20312351.html

The record group for hierarchical tree should have 5 columns:
1) Initial State - specifies which nodes are expanded/collapsed. Values are 0, 1, -1.
2) Level of node tree depth
3) Displayed value on node
4) Icon name
5) Data -- value associated with the node.


Here are the steps you need to follow using emp table in Scott schema:

1) Create a block B1. Create a hierarchical tree item TREE1.

2) Create another block B2. Create a push-button PB1.

3) Create Record Group RG1 with the following query:
SELECT 1, LEVEL, ename, '', TO_CHAR(empno)
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH job = 'PRESIDENT'

4) Create WHEN-BUTTON-PRESSED trigger on PB1:
DECLARE
 my_tree ITEM;
 my_num  NUMBER;

BEGIN
 my_tree := Find_Item('B1.TREE1');
 my_num  := POPULATE_GROUP('RG1');
 FTREE.SET_TREE_PROPERTY(my_tree, FTREE.RECORD_GROUP, 'RG1');
END;

5) Create layout and run the program. Click PB1 to populate the tree.


I use hierarchical trees in my Human Resources Info System. Before using them you have to know some "underground" facts. First at all the tree item ha to be the only item in the block. The second fact is very important - due a bug in Forms a tree CANNOT have more then 1800 elements (leaves, subtrees, etc.) So if you try to use huge trees (what is not a good solution) you will run into this bug.

I provide you with the body of the package I use to deal with the hierarchical tree. My hope is that you will fid it useful.

Good luck!

PROCEDURE POPULATE_TREE (p_tree_id ITEM) IS
     TYPE pl_table_group_names IS
        TABLE OF VARCHAR(120)
        INDEX BY BINARY_INTEGER;
  TYPE pl_table_group_id IS
        TABLE OF NUMBER(6)
        INDEX BY BINARY_INTEGER;
     htree             ITEM;
     work_gr_names     pl_table_group_names;
     work_gr_id        pl_table_group_id;
     vn_group          INTEGER;
     vn_group_id       INTEGER;
     vn_member_begin   INTEGER;
     vn_member_count   INTEGER;
     v_new_node_root   FTREE.NODE;
     v_new_node_group  FTREE.NODE;
     v_new_node_member FTREE.NODE;
     vc_root_label     VARCHAR2(80);
     
     CURSOR rila_groups IS
        SELECT id,group_name,group_director_name  FROM rila_group;
        vc_rila_group rila_groups%ROWTYPE;
       
      CURSOR rila_employees IS
        SELECT id, rila_id|| '        '|| given_name||' ' || second_name ||' '|| surname AS name
        FROM employee
         WHERE     service_status = :service_status
               AND employee.rila_group = vn_group_id
         ORDER BY rila_id;
     vc_rila_employee rila_employees%ROWTYPE;
     
BEGIN

   -- Register ALL Rila group - Group_Names and Id
  OPEN rila_groups;
  vn_group := 0;
  LOOP
         FETCH rila_groups INTO vc_rila_group;
         EXIT WHEN  rila_groups%NOTFOUND;
        vn_group := vn_group + 1;
        work_gr_names(vn_group) := vc_rila_group.group_name;
        work_gr_id(vn_group) := vc_rila_group.id;
  END LOOP;
  CLOSE rila_groups;
 
   
   -- Delete the existing tree (if any)
   -- Ftree.ROOT_NODE is an undocumented constant pointing the Root node of the tree
   Ftree.Delete_Tree_Node(p_tree_id,  Ftree.ROOT_NODE);
   
   -- Make the Root Node "Rila Solutions AD"
     
   vc_root_label :=  'RILA SOLUTIONS AD';
   IF    :service_status = '1' THEN  vc_root_label := vc_root_label || ' - list of candidates';
   ELSIF :service_status = '2' THEN  vc_root_label := vc_root_label || ' - list of employees';
   ELSIF :service_status = '3' THEN  vc_root_label := vc_root_label || ' - list of released';
   END IF;

                 v_new_node_root := Ftree.Add_Tree_Node(
                                       p_tree_id,
                                       Ftree.ROOT_NODE,
                                       Ftree.PARENT_OFFSET,
                                       Ftree.LAST_CHILD,
                                       Ftree.EXPANDED_NODE,
                                       vc_root_label,
                                       NULL,
                                       NULL);
             
   -- Add nodes to the Hierarchical Tree - Subgroup Nodes  
  FOR i IN  1..vn_group LOOP
             -- Add parent node - the name of the Rila Group
             /*
             v_new_node_group := Ftree.Add_Tree_Node(
                                       p_tree_id,
                                       Ftree.ROOT_NODE,
                                       Ftree.PARENT_OFFSET,
                                       Ftree.LAST_CHILD,
                                       Ftree.EXPANDED_NODE,
                                       work_gr_names(i),
                                       NULL,
                                       work_gr_names(i));                                  
                                     
         */
                       v_new_node_group := Ftree.Add_Tree_Node(
                                       p_tree_id,
                                       v_new_node_root,
                                       Ftree.PARENT_OFFSET,
                                       Ftree.LAST_CHILD,
                                       Ftree.EXPANDED_NODE,
                                       work_gr_names(i),
                                       NULL,
                                       work_gr_names(i));
      -- Add all sibling nodes - Employees Member of the Subgroup                                
       vn_group_id := work_gr_id(i);
      FOR k IN rila_employees LOOP
          v_new_node_member := Ftree.Add_Tree_Node(
                                       p_tree_id,
                                       v_new_node_group,
                                       Ftree.PARENT_OFFSET,
                                       Ftree.LAST_CHILD,
                                       Ftree.EXPANDED_NODE,
                                       k.name,
                                       NULL,
                                       TO_CHAR(k.id));
     
       END LOOP;


  END LOOP;
 
   -- Expand the ROOT node of the hierarchical tree  
   FTREE.SET_TREE_NODE_PROPERTY (p_tree_id, v_new_node_root, Ftree.NODE_STATE, Ftree.EXPANDED_NODE);
 
   --Ftree.Set_Tree_Node_Property(htree, node, Ftree.NODE_STATE, Ftree.EXPANDED_NODE);
  vn_member_begin := vn_group + 1;
       
 EXCEPTION
    WHEN OTHERS THEN
       CLOSE rila_groups;
      CLOSE rila_employees;
END POPULATE_TREE;  -- End Procedure POPULATE_TREE
 


PROCEDURE Open_Employee_Form (p_tree_id ITEM) IS  

  node_label    VARCHAR2(100);
  node_value    VARCHAR2(10);
  node_depth    VARCHAR2(1);  
      list_id   ParamList;
      list_name VARCHAR2(10) := 'param_list';
BEGIN
   
   -- Find the DEPTH of the node clicked on.
  node_depth := Ftree.Get_Tree_Node_Property(p_tree_id, :SYSTEM.TRIGGER_NODE, Ftree.NODE_DEPTH);
 
   --MESSAGE(node_depth || ' ' || node_value ||' '|| node_label);
  --PAUSE;

     
    -- only nodes with depth 3 correspond to employees
    -- depth 3 corresponds to groups
    -- depth 1 corresponds to RILA SOLUTION
    IF  node_depth  = '3' THEN
         
           -- Find the VALUE of the node clicked on.
       node_value := Ftree.Get_Tree_Node_Property(p_tree_id, :SYSTEM.TRIGGER_NODE, Ftree.NODE_VALUE);
       -- Find the LABEL of the node clicked on.
       node_label := Ftree.Get_Tree_Node_Property(p_tree_id, :SYSTEM.TRIGGER_NODE, Ftree.NODE_LABEL);
       /*
           ** Create a parameter list named "param_list"
            */
       list_id := Get_Parameter_List(list_name);
        IF NOT Id_Null(list_id) THEN
                  Destroy_Parameter_List(list_id);
           END IF;
       
           list_id := Create_Parameter_List(list_name);
         
           /*
            ** Add parameter to the list to pass values for each
            ** user-defined parameters defined in the target form; for each
            ** parameter, specify its key, type (text or data), and value
            */
              Add_Parameter(list_id, 'PROCESS_TYPE',TEXT_PARAMETER,'EDIT');
             Add_Parameter(list_id, 'EMPLOYEE#',TEXT_PARAMETER,TO_NUMBER(node_value));
             Add_Parameter(list_id, 'EMPLOYEE_SERVICE_STATUS',TEXT_PARAMETER,:SERVICE_STATUS);
             Add_Parameter(list_id, 'WEB_or_LAN',TEXT_PARAMETER,:DATA.WEB_or_LAN);
 

              -- now open the form using the created parameter list
             OPEN_FORM('staff_new',ACTIVATE,SESSION,list_id);
    END IF;

END Open_Employee_Form; -- End Procedure Open_Employee_Form


 
PROCEDURE Colapse_All_Nodes (p_tree_id ITEM) IS
   node ftree.node;
  state varchar2(30);
  node_label varchar2(80);

BEGIN

  -- Get the root node
  node := Ftree.Find_Tree_Node(p_tree_id,'Management',Ftree.FIND_NEXT,Ftree.NODE_LABEL, Ftree.ROOT_NODE);

  --node_label := Ftree.Get_Tree_Node_Property(p_tree, node, Ftree.NODE_LABEL);
  --MESSAGE(node_label);
  --PAUSE;      

   -- collapse the node if it is expanded
  WHILE NOT Ftree.ID_NULL(node) LOOP
     state := Ftree.Get_Tree_Node_Property(p_tree_id, node, Ftree.NODE_STATE);
     IF state = Ftree.EXPANDED_NODE THEN
         Ftree.Set_Tree_Node_Property(p_tree_id, node, Ftree.NODE_STATE, Ftree.COLLAPSED_NODE);
      END IF;

     node := Ftree.Find_Tree_Node(p_tree_id, '', ftree.find_NEXT,Ftree.NODE_LABEL,'', node);

  END LOOP;
END colapse_all_nodes;
 
   

PROCEDURE Expand_All_Nodes (p_tree_id ITEM) IS
  node ftree.node;
  state varchar2(30);
  node_label varchar2(80);

BEGIN

-- Get the root node
  node := Ftree.Find_Tree_Node(p_tree_id,'RILA SOLUTIONS',Ftree.FIND_NEXT,Ftree.NODE_LABEL, Ftree.ROOT_NODE);

  --node_label := Ftree.Get_Tree_Node_Property(htree, node, Ftree.NODE_LABEL);
  --MESSAGE(node_label);
  --PAUSE;      

   -- expand the node if it is collapsed
  WHILE NOT Ftree.ID_NULL(node) LOOP
     state := Ftree.Get_Tree_Node_Property(p_tree_id, node, Ftree.NODE_STATE);
     IF state = Ftree.COLLAPSED_NODE THEN
        Ftree.Set_Tree_Node_Property(p_tree_id, node, Ftree.NODE_STATE, Ftree.EXPANDED_NODE);
     END IF;
     node := Ftree.Find_Tree_Node(p_tree_id, '', ftree.find_NEXT,Ftree.NODE_LABEL,'', node);
  END LOOP;
END Expand_All_Nodes;
0
 
musduCommented:
Hi,

above document is very good, but you may need a simple one to start and understand how it works;

-Create a new form
-Create a hierarchical tree on your block (name it as TABLETREE)
-Create a when-new-form-instance trigger
-Copy/Paste below code and run your form. This code will populate all tables and their columns to tree item.
-Set "MULTI SELECTION" property to "YES" to allow users to select multiple items

regards

DECLARE

   htree   ITEM;
   new_node FTREE.NODE;
   
BEGIN


  -- Find the tree itself.
  htree := Find_Item('tabletree');

  FOR vTable IN (SELECT Table_Name FROM USER_TABLES) LOOP

     new_node := Ftree.Add_Tree_Node(
        htree,
        Ftree.ROOT_NODE,
        Ftree.PARENT_OFFSET,
        Ftree.LAST_CHILD,
        Ftree.EXPANDED_NODE,
        vTable.Table_Name,
        NULL,
        'root');

     -- Add the new node at the top level and children.
     Ftree.Add_Tree_Data(
        htree,
        new_node,
        Ftree.PARENT_OFFSET,
        Ftree.LAST_CHILD,
        Ftree.QUERY_TEXT,
        'SELECT 0, 1, Column_Name, NULL, Column_Name FROM User_Tab_Columns WHERE Table_Name = '''||vTable.Table_Name||'''');

  END LOOP;
 
END;
0
 
khaledsoliman2001Author Commented:
Thanks All;
But how can I create a select statement from the multiselected coulmns ans tables in the tree?
0
 
Helena Markováprogrammer-analystCommented:
You can LOOP through selected tree items - see example "GET_TREE_SELECTION" topic in the on-line help.
0
 
musduCommented:
Goodmorning,

if you created the form I mentioned above;

1) Create a new block (because tree items must be single item on a block)
2) Create a button on this new block
3) Copy below code to when-button-pressed-trigger.
4) Run form. Select several columns of a table in tree
5) Press the button
6) It'll create a select statement using selected column names and display it as message

regards

----------------

DECLARE
   htree         ITEM;
   num_selected  NUMBER;
   current_node  FTREE.NODE;
   parent_node  FTREE.NODE;
   vQuery VARCHAR2(500) := 'SELECT ';
   vColumnName            VARCHAR2(50);
   vTableName            VARCHAR2(50);
BEGIN
   -- Find the tree itself.
   htree := Find_Item('tabletree');

   -- Find the number of nodes marked as selected.
   num_selected := Ftree.Get_Tree_Property(htree, Ftree.SELECTION_COUNT);

   FOR j IN 1..num_selected LOOP
      current_node := Ftree.Get_Tree_Selection(htree, j);
      vColumnName := Ftree.Get_Tree_Node_Property('tabletree', current_node, Ftree.NODE_VALUE);
      vQuery := vQuery || vColumnName;
      EXIT WHEN j=num_selected;
      vQuery := vQuery || ',';
   END LOOP;
   
   parent_node := ftree.get_tree_node_parent('tabletree', current_node);
   vTableName := Ftree.Get_Tree_Node_Property('tabletree', parent_node, Ftree.NODE_LABEL);
   vQuery := vQuery ||' FROM '||vTableName;
   message(vquery);
END;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now