7 Replies Latest reply: May 7, 2013 7:23 AM by 1005774 RSS

    ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM

    1005774
      I have a form and I built an LOV on a particular field. The LOV returns the values, I select a value, it goes into the field, but what I want is to have the form retrieve all the information from the database and display (only) all the data. But when I put a value in the field from the LOV, nothing happens. i have tried to have the form go into "enter_query" mode, then select from the LOV, then end the "when button pressed" with "excute_query". But it will ask if i want to save, i say no, then it just displays the first record in the database. what am i doing wrong?

      thank you,

      Steven
        • 1. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
          François Degrelle
          Hello,
          You could use:
          Set_Block_Property( ..., DEFAULT_WHERE, 'table_column=:block.item');
          execute_query;
          Where :block.item is the item populated by the LOV

          Francois
          • 2. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
            InoL
            This is how Forms works (if your block is based on a table):
            - Go to enter query mode (depending on your key setting this is often F7 or by default F11).
            - Go to your LOV item and open the LOV
            - Select a value from the LOV. This value is shown in your field.
            - Click on Execute Query.
            • 3. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
              1005774
              within the "button pressed" i started the query with "enter_query" then the list values, then "excute_query", but that doesn't work properly. However, if i select the 'enter_query' from the menu bar, then my LOV, all works just fine. I don't want to have my customers to have to do this. I want the block to be in query mode, select from the LOV, then execute each and every time. suggestions?
              • 4. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
                1005774
                here is my complete code on this button: when i press the button, it keeps asking me if i want to save it (if i say yes, it adds it to the table in the database, which i do not want). however, if i first select "enter query" from the menu bar, and then click on my button, everything works as expected.

                set_item_property('SL_3_ITEMS_HEADER.SL_3_SHORT_TITLE',queryable,property_true);
                --Set_Block_Property( 'SL_3_ITEMS_HEADER', DEFAULT_WHERE, 'parker.SL_3_SHORT_TITLE=:sl_3_short_title');

                declare
                     SL3 varchar2(12);
                BEGIN
                     
                     go_item('SL_3_SHORT_TITLE');
                list_values;
                SL3 := :SL_3_ITEMS_HEADER.SL_3_SHORT_TITLE;
                     if SL3 is not null then
                          select SL_3_SHORT_TITLE into :SL_3_ITEMS_HEADER.SL_3_SHORT_TITLE
                          FROM parker.SL_3_ITEMS_HEADER WHERE SL_3_short_title = SL3;
                     ELSE
                          message ('You need to specify an SL-3 to be able to search');
                     end if;
                     exception
                          when no_data_found then
                          message('An SL-3 with that name does not exist.');
                          clear_form;
                     end;
                     
                     execute_query;
                • 5. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
                  InoL
                  You have to set the block in enter query mode first. What you can do is create a when-new-block-instance trigger on your block:
                  enter_query;
                  It will probably never really work the way you want it. This works when you first open the form, but will not work when you click the button a 2nd time. This just does not work in a when-button-pressed:
                  enter_query;
                  list_values;
                  execute_query;
                  IMHO, it's better to have users get to know how Forms works than trying to get Forms to work the way your users want it to work.
                  There are workarounds with a search item in control block instead of the base table block, but is is just so much easier to use standard Forms functionality.
                  • 6. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
                    1005774
                    Thank you for all the suggestions, but below is how i actually got it to work. Created a new control block that was non-database related.

                    clear_form;
                    go_item('text_item60'); --control block, non-database related item.
                    list_values; LOV created from the database.

                    set_item_property('SL_3_ITEMS_HEADER.SL_3_SHORT_TITLE',queryable,property_true);
                    set_block_property ('SL_3_ITEMS_HEADER', DEFAULT_WHERE, 'SL_3_SHORT_TITLE like :BLOCK59.text_item60'); --copy from control_block into actual field to be used.

                    go_item('SL_3_SHORT_TITLE'); -- go to the actual field and execute. works everytime.
                    EXECUTE_QUERY;

                    Edited by: 1002771 on May 6, 2013 11:27 AM
                    • 7. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
                      1005774
                      thank you everyone.