1 2 3 Previous Next 32 Replies Latest reply: May 12, 2010 5:01 AM by 749777 Go to original post RSS
      • 15. Re: Populate Form Field from Post-Query Trigger
        634176
        OK, 1 field only, so you'll have to concatenate the field:
        I tried this but I'm getting "no data found" error.
        • 16. Re: Populate Form Field from Post-Query Trigger
          Andreas Weiden
          Is the form a readonly form? If so, you could create a view and base the form on that view.
          • 17. Re: Populate Form Field from Post-Query Trigger
            634176
            No, it is not readonly.

            By the way, Andreas, this is with reference to the previous question that I had asked. It is the same form, and I'm using the post-query to grab data for the unbound items
            • 18. Re: Populate Form Field from Post-Query Trigger
              Andreas Weiden
              I would go with poelgers approach of concatenating the value to an unbound multiline-item, i think i remember that an item get automatically multiline if you set the width of the item to something more than 1.
              • 19. Re: Populate Form Field from Post-Query Trigger
                634176
                I already tried concatenation. It doesn't work. It still displays only one record in the item.

                I know my cursor works fine because I have tested it independently in TOAD by putting it inside an anonymous block.

                Any other ideas?
                • 20. Re: Populate Form Field from Post-Query Trigger
                  Andreas Weiden
                  I already tried concatenation. It doesn't work.
                  It should work. Post your code to have a look at it.
                  • 21. Re: Populate Form Field from Post-Query Trigger
                    634176
                    I tried something like this in the post_query:
                    declare
                     cursor previous_data_cur is  
                            select company_id,start_date
                               from my_companies
                               where company_id = :myblock.company_id
                               and start_date < :myblock.start_date;     
                    
                    l_previous_data previous_data_cur%ROWTYPE;
                    
                    begin   
                    
                    FOR l_previous_data in previous_data_cur
                        LOOP     
                            :my_block.company_id:= :my_block.company_id || l_previous_data.company_id||CHR(10)||CHR(13);
                            :my_block.prev_start_date := :my_block.prev_start_date || l_previous_data.start_date||CHR(10)||CHR(13);
                           
                        END LOOP; 
                    end; 
                    • 22. Re: Populate Form Field from Post-Query Trigger
                      Andreas Weiden
                      i guess company_id is a number and prev_start_date is a date ? So concatenation into the "original" field cannot work because of the datatype.

                      Try the following:

                      Create two Non-database-items (unbound item in designer-language). name them DSP_start_date and DSP_company_id, set datatype to VARCHAR2(4000)
                      then use the following code:
                      declare
                       cursor previous_data_cur is  
                              select company_id,start_date
                                 from my_companies
                                 where company_id = :myblock.company_id
                                 and start_date < :myblock.start_date;     
                       
                      l_previous_data previous_data_cur%ROWTYPE;
                       
                      begin   
                        :my_block.DSP_company_id:= :my_block.company_id;
                        :my_block.DSP_start_date := :my_block.start_date;
                       
                        FOR l_previous_data in previous_data_cur
                          LOOP     
                            :my_block.DSP_company_id:= :my_block.DSP_company_id || l_previous_data.company_id||CHR(10)||CHR(13);
                            :my_block.DSP_prev_start_date := :my_block.DSP_start_date || l_previous_data.start_date||CHR(10)||CHR(13);
                          END LOOP; 
                      end; 
                      • 23. Re: Populate Form Field from Post-Query Trigger
                        634176
                        Andreas,

                        Sorry to say but it still doesn't work. All i get is a single value for each field followed by 2 squares (i guess those are chr(10) and chr(13) ).

                        Any more suggestions?

                        This thing is driving me nuts! Something so simple yet taking so much time!
                        • 24. Re: Populate Form Field from Post-Query Trigger
                          Andreas Weiden
                          The squares are shown in a single-line-textitem when you have multi-line text in it. So the concatenation-code seems to be ok. To be sure open the form in forms-builder and set the property multiline at the item "by hand".

                          Now you have to get the item to multiline in designer. Did you try setting the item-height to larger than 1 in designer? If so, perhaps you ask that question in the designer-forum.
                          • 25. Re: Populate Form Field from Post-Query Trigger
                            428180
                            How many time the FOR LOOP instructions are executed ??? place message on it ...

                            Why don't try to write your logic in a DB procedure... after you must test it .. and finally you can call from FORMS ...just to see if it works ...

                            example : FUNCTION getResult(currCompany NUMBER) RETURN VARCHAR2;

                            ah, don't forget to set Multi-Line = YES

                            Regards
                            • 26. Re: Populate Form Field from Post-Query Trigger
                              634176
                              Andreas,

                              I discussed this issue with someone else and they suggested that I should use my code in the "key-exeqry" trigger instead of post-query trigger because "key-exeqry" is unrestricted and allows "next_record". However I'm not quite sure how I should put my code there...should I just write the entire cursor as-is in that? or is there any other way?

                              I would greatly appreciate your feedback
                              • 27. Re: Populate Form Field from Post-Query Trigger
                                Andreas Weiden
                                I should use my code in the "key-exeqry" trigger instead of post-query trigger because "key-exeqry" is unrestricted and allows "next_record"
                                I don't know you suggested that, but as far as i understood you you need to execute this "lookup" for each record found by the query. KEY-EXEQRY fires only once for the query, so you won't have any chance to do a per-record-operation in it. What about my suggestion to try to set the field to multiline "by hand" in the forms-builder to see if your concatenation works?
                                • 28. Re: Populate Form Field from Post-Query Trigger
                                  634176
                                  Andreas,

                                  I tried changing the item to multiline by hand in form builder. Turns out data is not getting fetched. I'm getting this error:

                                  "frm-1403: ORA-01403: no data found"

                                  My cursor query is the same as I mentioned in my previous posts. I'm using something like this to open the cursor:
                                     FOR l_previous_data in previous_data_cur
                                      LOOP
                                          :my_block.prev_seq_num := :my_block.prev_seq_num || l_previous_data.seq_num||CHR(10)||CHR(13);
                                          :my_block.prev_start_date := :my_block.prev_start_date || l_previous_data.start_date||CHR(10)||CHR(13);
                                            END LOOP;
                                  Any ideas?
                                  • 29. Re: Populate Form Field from Post-Query Trigger
                                    Andreas Weiden
                                    the ORA-01403 cannot be raised by the shown code. I would guess that some other error occurs which is not handled correctly by the designer error-handling used by designer. To see the "real" cause of the error, delete all ON-ERROR-triggers in the form (both at item, block, and form-level) and try again.