1 2 Previous Next 28 Replies Latest reply: Dec 11, 2013 11:20 AM by Nicolette Go to original post RSS
      • 15. Re: Form with Report - linking 2 records together on form from DB?
        Nicolette

        Rambo79

        Rambo79 wrote:

         

        Thanks

         

        Could you clarify what you mean by 'header process joint' 

        With the process point is meant at which point in rendering or submitting the page a process, computation, branch etc is executed.

        The header process point is the very first process point.

        If your are using the tree view for your page development then all your processes , computation etc. are already ordered by process point.

         

        I take it you would create this page process on the actual form page where I have created 2 html regions containing the form details for groom in the 1st region and bride in the 2nd region (PAGE 16) and not from the interactive report page where the 'linked to' column is located (PAGE 15).

        Yes the process that selects/ fetch the bride and groom detailed information has to be located on the same page as that information is shown.

        In your case that means page 16.

         

        How would this process statement change so when you click on the 'link column' it knows which SQL statement in the process to run.

        What do you mean?

        I thought the whole idea was to get the information of both the groom and the bride. Not just one

         

        As for the select statement I personally wouldn't use select * from ... but only return the columns I'm going to use.

        So  select INDSNAME, INDFNAME .... from....

         

        Nicolette

        • 16. Re: Form with Report - linking 2 records together on form from DB?
          makel

          Do the bride and groom always have different gender?

          • 17. Re: Form with Report - linking 2 records together on form from DB?
            Rambo79

            What do you mean?

            I thought the whole idea was to get the information of both the groom and the bride. Not just one

            It does, but how would you know what to specify in the source value or expression in the form field so that the grooms information goes into the groom indfname and indsname and the same for the bride

             

            Also with the process point on page 16 I have  created a PL/SQL 'on load before header' in my SQL how would you separate the groom and brides details so it goes into the correct region  on the form (as I mentioned above)

            • 18. Re: Form with Report - linking 2 records together on form from DB?
              Nicolette

              Rambo79

               

              Because you want to retrief two rows into page items you have to do the setting of the page items yourself.

              This means that the source definition of the page items stays empty.

               

              In the page process where you have the select statements you will be setting the page item values.

              I personally would use a cursor with a for loop. But you could also a select into statement.

               

              The code with the for loop would be something like

              declare
                   cursor c_mar(b_indent in number
                                            ,b_indvenue in varchar2
                                            ,b_inddom   in varchar2
                                            )
                   is
                        select bgflag
                        ,      indsname
                        ,      indfname
                        ,      indinit2
                        ,      indinit3
                        ,      indfthr
                        ,      deldoc
                        ,      gender
                        from   marriages mar
                        where  mar.indent   = b_indent
                        and    mar.indvenue = b_indvenue
                        and    mar.inddom   = b_inddom
                   ;
              
              begin
                   for r_mar in c_mar(:p16_indent,:p16_indvenue,:p16_inddom)
                   loop
                        if r_mar.gender = 'M'
                        then
                             :p16_groom_bgflag   := r_mar.bgflag;
                             :p16_groom_indsname := r_mar.indsname;
                             :p16_groom_infname  := r_mar.infname;
                             --etc.
                        else
                             :p16_bride_bgflag   := r_mar.bgflag;
                             :p16_bride_indsname := r_mar.indsname;
                             :p16_bride_infname  := r_mar.infname;
                             --etc.
                        end if;   
                   end loop;
              end; 
              

              Change the page item names and add the remaining ones according to your needs.

              I have used naming convention I am used to.

               

              Nicolette

              • 19. Re: Form with Report - linking 2 records together on form from DB?
                Rambo79

                Hi Nicolette

                 

                Thanks I think I am following you now, and the snippet is great.  Just want to clarify a few things please

                 

                So on page 16 I create a page process > PL/SQL > Point On Load - Before Header > and then I enter the PL/SQL code block?

                 

                When using the code with the for loop, how could this be adapted so instead of using gender to identify male and female (as not all records have this data 'M' or 'F') insteead how could it be based on 'ID' as the grooms ID number is always before the bride in the sequential number

                • 20. Re: Form with Report - linking 2 records together on form from DB?
                  Nicolette

                  Rambo79

                  Rambo79 wrote:

                   

                  So on page 16 I create a page process > PL/SQL > Point On Load - Before Header > and then I enter the PL/SQL code block?

                  Yes The type of the process is "PL/SQL anonymous block" .

                  When using the code with the for loop, how could this be adapted so instead of using gender to identify male and female (as not all records have this data 'M' or 'F') insteead how could it be based on 'ID' as the grooms ID number is always before the bride in the sequential number

                  Add rownum to the select statement and add an order by on the ID so you know if the bride or the groom is the first row.

                  And then change the if statement to check if the rownum is 1. This is your first row.

                  Don't forget to change according to the names of your page items.

                   

                  Nicolette

                  • 21. Re: Form with Report - linking 2 records together on form from DB?
                    Rambo79

                    Hi Nicolette

                     

                    I have created aa page process > PL/SQL > Point On Load - Before Header using the code below.  No errors show, but when clicking through from the interactive report (page 15) to the form using the following for the link columns

                     

                    P16_INDENT   #INDENT#

                    P16_INDVENUE  #INDVENUE#

                    P16_INDDOM   #INDDOM#

                     

                    However when the form loads the only information on the form fields that is populated is the entry number (INDENT), book (INDVENUE) and the date of marriage fields (INDDOM) ?

                     

                    What have I done wrong?  In the source for the form fields it is set to 'Database Column' with a source value or expression of 'INDSNAME' for example

                     

                    declare  
                         cursor c_mar(b_indent in number  
                                                  ,b_indvenue in varchar2  
                                                  ,b_inddom   in varchar2  
                                                  )  
                         is  
                              select bgflag  
                      ,      id
                              ,      indsname  
                              ,      indfname  
                              ,      indinit2  
                              ,      indinit3  
                              ,      indfthr  
                       ,      indent
                       ,      indvenue
                              ,      inddom
                              from   marriages mar  
                              where  mar.indent   = b_indent  
                              and    mar.indvenue = b_indvenue  
                              and    mar.inddom   = b_inddom  
                    ORDER BY ID
                         ;  
                    begin  
                         for r_mar in c_mar(:p16_indent,:p16_indvenue,:p16_inddom)  
                         loop  
                              if r_mar.id = '1'  
                              then  
                                   :P16_INSNAME_GROOM   := r_mar.indsname;  
                                   :P16_INDFNAME_GROOM := r_mar.indfname;  
                                   :P16_INDINIT2_GROOM  := r_mar.indinit2;
                            :P16_INDINIT3_GROOM  := r_mar.indinit3;  
                                   :P16_INDFTHR_GROOM  := r_mar.indfthr;    
                              else  
                                    :P16_INDSNAME_BRIDE   := r_mar.indsname;  
                                   :P16_INDFNAME_BRIDE := r_mar.indfname;  
                                   :P16_INDINIT2_BRIDE  := r_mar.indinit2;
                            :P16_INDINIT3_BRIDE  := r_mar.indinit3;  
                                   :P16_INDFTHR_BRIDE  := r_mar.indfthr;    
                              end if;     
                         end loop;  
                    end;   
                    
                    • 22. Re: Form with Report - linking 2 records together on form from DB?
                      Nicolette

                      Rambo79

                       

                      In the source for the form fields it is set to 'Database Column' with a source value or expression of 'INDSNAME' for example

                      Since your are fetching the data yourself the Source type of the page fields should be set to "Static assignment".

                      The Source value or expression is left empty.

                      And the Source Used is "Only when current session state is null"

                       

                      Also check if there is already a fetch process make sure it doesn't run. If you initially created the page using a wizard to create the report and form page then there is such a process. With a name like "Fetch Row from OEHR_EMPLOYEES" and of type "Automated Row Fetch"

                       

                      Nicolette

                      • 23. Re: Form with Report - linking 2 records together on form from DB?
                        Rambo79

                        Hi Nicolette

                         

                        It seems to be working, but at the moment it's only displaying the brides details and not the groom?

                         

                        All of the field names are correct, so could it be something with the if statement?

                        • 24. Re: Form with Report - linking 2 records together on form from DB?
                          Nicolette

                          Rambo79

                          Rambo79 wrote:

                           

                          Hi Nicolette

                           

                          It seems to be working, but at the moment it's only displaying the brides details and not the groom?

                           

                          All of the field names are correct, so could it be something with the if statement?

                          Everything is possible.

                           

                          Is time to start debugging.

                          Use the apex_debug api to track what is happening in the process.

                           

                          Or/and create an application on apex.oracle.com and post the login details of a guest developer so we can have look.

                          It is a bit hard to know what is happening without having access to the application and data.

                           

                          Nicolette

                          • 25. Re: Form with Report - linking 2 records together on form from DB?
                            Rambo79

                            Hi

                             

                            I have turned debugging on for page 16 from the toolbar, but when clicking on 'view debug'  what should I be looking for?

                            • 26. Re: Form with Report - linking 2 records together on form from DB?
                              Nicolette

                              Rambo79

                              Rambo79 wrote:

                               

                              Hi

                               

                              I have turned debugging on for page 16 from the toolbar, but when clicking on 'view debug'  what should I be looking for?

                              The messages you have included in the by you created process using the apex_debug api to track what is happening in the process.

                               

                              The default messages (aka not the ones you have declared using the api) are for when in the flow a process is run and if it runs.

                              But you already know that the process runs. Otherwise the bride details would be empty to.

                              And since you have written the code for the process you have to add the debug messages to get a more detailed look at what is happening during the running of the process.

                               

                              For instance have a message in the if and another in the else to see if the values are what you think they are.

                               

                              Nicolette

                              • 27. Re: Form with Report - linking 2 records together on form from DB?
                                Rambo79

                                Hi Nicolette

                                 

                                Could you give me an example of what I need to put in (in my code snippet shown on post 21 of this thread) as I don't really understand where it can go reading through it or what I need to specify or check for.

                                 

                                The reality is when clicking through from the interactive report to the form view only the brides details are showing, the groom information is missing.

                                • 28. Re: Form with Report - linking 2 records together on form from DB?
                                  Nicolette

                                  Rambo79

                                   

                                  This is where I would place the debug message. I assumed you're using apex4.2 if you use an earlier version that the call the create the debug message is different.

                                   

                                      declare    
                                           cursor c_mar(b_indent in number    
                                                                    ,b_indvenue in varchar2    
                                                                    ,b_inddom   in varchar2    
                                                                    )    
                                           is    
                                                select bgflag    
                                                ,      id  
                                                ,      indsname    
                                                ,      indfname    
                                                ,      indinit2    
                                                ,      indinit3    
                                                ,      indfthr    
                                                ,      indent  
                                                ,      indvenue  
                                                ,      inddom
                                                ,      rownum              
                                                from   marriages mar    
                                                where  mar.indent   = b_indent    
                                                and    mar.indvenue = b_indvenue    
                                                and    mar.inddom   = b_inddom    
                                      ORDER BY ID  
                                           ;    
                                      begin
                                           apex_debug.message('Start fetch groom and bride');    
                                           for r_mar in c_mar(:p16_indent,:p16_indvenue,:p16_inddom)
                                           loop
                                                apex_debug.message('Loop run: '||r_mar.rownum);
                                                apex_debug.message('Id of person: '||r_mar.id);               
                                                if r_mar.id = '1'    
                                                then  
                                                      apex_debug.message('This is the groom');              
                                                     :P16_INSNAME_GROOM   := r_mar.indsname;    
                                                     :P16_INDFNAME_GROOM  := r_mar.indfname;    
                                                     :P16_INDINIT2_GROOM  := r_mar.indinit2;  
                                                     :P16_INDINIT3_GROOM  := r_mar.indinit3;    
                                                     :P16_INDFTHR_GROOM   := r_mar.indfthr;      
                                                else  
                                                     apex_debug.message('This is the bride');              
                                                     :P16_INDSNAME_BRIDE  := r_mar.indsname;    
                                                     :P16_INDFNAME_BRIDE  := r_mar.indfname;    
                                                     :P16_INDINIT2_BRIDE  := r_mar.indinit2;  
                                                     :P16_INDINIT3_BRIDE  := r_mar.indinit3;    
                                                     :P16_INDFTHR_BRIDE   := r_mar.indfthr;      
                                                end if;       
                                           end loop;
                                           apex_debug.message('End of fetch bride and groom');         
                                      end;
                                  

                                   

                                  While adding the debug messages I notice something that is probably the cause.

                                  You have r_mar.id = '1'  in the if statement that means that only the very first record inserted is a groom and all other records are brides.

                                  Add rownum to the select statement and add an order by on the ID so you know if the bride or the groom is the first row.

                                  I have already added rownum to the select statement.

                                  I left the changing of the if condition up to you.

                                  You should be able to see what the if condition should be with the help of the debug messages.

                                   

                                  Nicolette

                                  1 2 Previous Next