Solved

Populating lists dynamically

Posted on 2001-06-07
4
3,211 Views
Last Modified: 2010-05-18
I have two list items, both pop-lists. Item A and B.

I populate item A based on a query in WHEN-NEW-FORM-INSTANCE

I want to populate item B based on the value selected in item A using a query.
What I did was put the following code in the WHEN-LIST-CHANGED trigger of item A :-

DECLARE
     rg_B varchar2(40):='B_REC';
     status_B number;
     groupid_B recordgroup;
BEGIN
     groupid_B:= Find_Group( rg_B);

     IF NOT Id_Null(groupid_B) THEN
          Delete_Group(groupid_B);
     END IF;

     groupid_B:=create_group_from_query(rg_B,'select field2,field2 from Table where field1='''||:BLK_NAME.A||'''');
     /* Table has 2 fields, field1 and field2 */

     status_B:=populate_group(groupid_B);
     populate_list('B',groupid_B);
END;

However, in doing so I get the following error :-

FRM-41337 : Cannot populate list from record group

Any ideas?

Thanks a ton,

-- Asim
0
Comment
Question by:asim_shankar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Accepted Solution

by:
Vulture earned 100 total points
ID: 6163006
Hi Asim, I am using this functionality in a menu system
here is the code.

I use a program unit to populate list A.

----------------------------------------------------------
PROCEDURE pop_menu_group(men_group varchar2) IS

 /* Cursor to retrieve all application systems */
  cursor curs_group is
  select group_code, group_desc from sec_menu_group
  where menu_tab = men_group order by order_no;

  /* Cursor record for application systems cursor */
  t_menu_group   sec_menu_group%rowtype ;

  /* Working variables */
  t_list_id     item ;
  t_list_count  number ;
  t_loop_count  number ;

BEGIN
   /* find application list items */
  << find_list >>
  t_list_id := find_item(men_group) ;
  if id_null(t_list_id) then
     message('Application Systems text list is missing.') ;
     return ;
  end if ;

  /* populate menu group list */
  << populate_group >>
  clear_list(t_list_id) ;
  t_loop_count := 1 ;
  t_list_count := Get_List_Element_Count(t_list_id) ;
  for t_menu_group in curs_group loop
      add_list_element(t_list_id, t_list_count + t_loop_count,
                       t_menu_group.group_desc,
                       t_menu_group.group_code );
      t_loop_count := t_loop_count + 1 ;
  end loop populate_group ;

  copy(get_list_element_value(t_list_id, 1), men_group);
 
END;
---------------------------------------------------------

and my WHEN_LIST_CHANGED trigger

---------------------------------------------------------
pop_menu_item('a_item', :a);
---------------------------------------------------------

calls a second program unit.

---------------------------------------------------------
PROCEDURE pop_menu_item(men_tab varchar2,
                        men_group varchar2) IS

   /* Cursor to retrieve all modules for a group */
  cursor curs_mod is
  select mod_code, mod_desc from sec_module
  where group_code = men_group
  order by order_no;
 
  /* Cursor record for application systems cursor */
  t_sec_module   sec_module%rowtype ;

  /* Working variables */
  t_list_id     item ;
  t_list_count  number ;
  t_loop_count  number ;
  item_list     varchar2(40) := men_tab;
 
BEGIN
   /* find application list items */
  << find_list >>
  t_list_id := find_item(item_list) ;
  if id_null(t_list_id) then
     message('Application Systems text list is missing.') ;
     return ;
  end if ;

  /* populate menu group list */
  << populate_group >>
  clear_list(t_list_id) ;
  t_loop_count := 1 ;
  t_list_count := Get_List_Element_Count(t_list_id) ;
  for t_sec_module in curs_mod loop
      add_list_element(t_list_id, t_list_count + t_loop_count,
                       t_sec_module.mod_code || '    ' || t_sec_module.mod_desc,
                       t_sec_module.mod_code );
      t_loop_count := t_loop_count + 1 ;
  end loop populate_group ;

  copy(get_list_element_value(t_list_id, 1), item_list);


END;
---------------------------------------------------------

I trust this will help you overcome your problem
0
 
LVL 48

Expert Comment

by:schwertner
ID: 6166555
Modifying the following WORKING example you can do what you want. In fact you have the issue the builtin POPULATE_LIST_WITH_QUERY at the right place in your WHEN-LIST-CHANGED trigger



In the trigger WHEN_NEW_BLOCK-INSTANCE where your list item is defined put the following call

POPULATE_LIST_WITH_QUERY(
'item241',
'SELECT land_text,land_code FROM pepper_land_lookup
WHERE sysdate BETWEEN valid_from AND NVL(valid_to,sysdate+10)');

where item241 is the name of the list item you need to populate and the second argument is the needed
querry.

Put the PROCEDURE populate_list_with_query in the program units section of your form

--Populates the given list item with the specified query
PROCEDURE populate_list_with_query
  (p_list_item IN VARCHAR2,
   p_query     IN VARCHAR2)
IS
  /* Name the record group after the list item
   (no block prefix).  */
  cst_rg_name CONSTANT VARCHAR2(30) :=
      GET_ITEM_PROPERTY(p_list_item,item_name);
  v_rg_id RECORDGROUP;
BEGIN
  v_rg_id := FIND_GROUP(cst_rg_name);
  IF ID_NULL(v_rg_id) THEN
     v_rg_id := CREATE_GROUP_FROM_QUERY(cst_rg_name,p_query);
  END IF;
  IF POPULATE_GROUP(v_rg_id) = 0 THEN
     POPULATE_LIST(p_list_item,v_rg_id);
        /* Force display of first list element label
           in the list item. */
     COPY(GET_LIST_ELEMENT_VALUE(p_list_item,1),p_list_item);
  END IF;  
END populate_list_with_query;  


 
   
 
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6857700
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.

Question(s) below appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question QID/link(s) that it regards.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Please click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20129023.html
http://www.experts-exchange.com/questions/Q.20130485.html
http://www.experts-exchange.com/questions/Q.20272941.html


To view your locked questions, please click the following link(s) and evaluate the proposed answer.
http://www.experts-exchange.com/questions/Q.20129948.html
http://www.experts-exchange.com/questions/Q.20128821.html
http://www.experts-exchange.com/questions/Q.20130657.html
http://www.experts-exchange.com/questions/Q.20131172.html

PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6866884
It is requested that any grade less than an A include comments by you as to why.  This not only helps the expert, but others who may now seek this solution in our PAQ database.  If this grade was chosen in error, let me know, I can correct it.  The points to you are always the same, but the expert points granted depend on the grade.

Thanks,
Moondancer - EE Moderator
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question