1 2 Previous Next 28 Replies Latest reply: Dec 11, 2013 11:20 AM by Nicolette RSS

    Form with Report - linking 2 records together on form from DB?

    Rambo79

      Hi

       

      I have an interactive report that brings back all marriage records from a table that is using a link column which brings up the record detail i.e. P16_ID in the name column and #ID# in the value column.

       

      How can I group 2 records together so when you click on either the bride or groom record in the interactive report when the form loads it will bring back both the bride and groom details placing the groom's records in the first section of the form and the bride's in the second section of the form.

       

      There are unique column values associated with both the bride and groom records that associates them together.  These are wedno, venue and weddate.  Also the grooms details always appears before the brides in the sequential number in the database table.

       

      I'm not sure what would be the best where clause to use and where it would actually go in the link column so when you click through from either record in the interactive report it brings back both associated records.

       

      Hope an Oracle expert can help?

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

          any ideas on if this can be done with Apex?

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

            On your report link pass through the id that is shared by both bride and groom.

            Then on the next page have 2 different sql statements to retrieve the 2 sets of data

             

            Gus

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

              Hi

               

              For the bride and groom there is no single ID that is shared by both the bride and groom.

               

              There are multiple columns in the table that combine these being wedno, venue and weddate to make the link between the two people

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

                Rambo79 wrote:

                 

                Hi

                 

                For the bride and groom there is no single ID that is shared by both the bride and groom.

                 

                There are multiple columns in the table that combine these being wedno, venue and weddate to make the link between the two people

                It is not clear from the fragments of information you are providing exactly how (or if) the parties are linked. Please provide create table DDL and sample data INSERT scripts to illustrate this (or reproduce the problem on apex.oracle.com and provide guest developer credentials for the workspace).

                 

                It sounds like the data model is incorrect for the relationships you are trying to represent.

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

                  Rambo79

                   

                  You are already saying which values you should pass.

                  Rambo79 wrote:

                   

                   

                  There are multiple columns in the table that combine these being wedno, venue and weddate to make the link between the two people

                  And then in the where you should have these 3 columns.

                   

                  To be more precise in how the where should look like we need to see your datamodel.

                  The easiest way to do that is to set up an application of what you have so far on apex.oracle.com.

                   

                  Nicolette

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

                    Thanks

                     

                    But where (what page and in what section) would the where clause go?  In the link column section there is nowhere to specify the where clause

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

                      Rambo79 wrote:

                       

                      Thanks

                       

                      But where (what page and in what section) would the where clause go?  In the link column section there is nowhere to specify the where clause

                      We don't know. Possibly none of it should go anywhere due to the data model being an incorrect representation of the entities involved.

                       

                      Spot the emerging pattern:

                      fac586 wrote:

                       

                       

                      It is not clear from the fragments of information you are providing exactly how (or if) the parties are linked. Please provide create table DDL and sample data INSERT scripts to illustrate this (or reproduce the problem on apex.oracle.com and provide guest developer credentials for the workspace).

                       

                      It sounds like the data model is incorrect for the relationships you are trying to represent.

                       

                      Nicolette wrote:

                       

                      To be more precise in how the where should look like we need to see your datamodel.

                      The easiest way to do that is to set up an application of what you have so far on apex.oracle.com.

                      Provide the requested information or set up an example on apex.oracle.com.

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

                        Hi here is the create table script. It is based on data imported from a legacy lotus notes flatfile database which is why the data is not relational causing these problems

                         

                        Sample Data

                        ID,INDDOM,INDENT,INDVENUE,BGFLAG,INDSNAME,INDFNAME,INDINIT2,INDINIT3,INDFTHR,DELDOC,GENDER
                        
                        
                        643,29/10/1953,2055,BOOKINDEX1,,BLOGGS,JOE,,,,,M
                        644,29/10/1953,2055,BOOKINDEX1,,CAPP,SUSAN,,,,,F
                        

                         

                         

                        CREATE TABLE MARRIAGES
                        (
                          ID        NUMBER                              NOT NULL,
                          INDDOM    DATE                                NOT NULL,
                          INDENT    NUMBER                              NOT NULL,
                          INDVENUE  VARCHAR2(100 BYTE),
                          BGFLAG    VARCHAR2(50 BYTE),
                          INDSNAME  VARCHAR2(100 BYTE),
                          INDFNAME  VARCHAR2(100 BYTE),
                          INDINIT2  VARCHAR2(100 BYTE),
                          INDINIT3  VARCHAR2(100 BYTE),
                          INDFTHR   VARCHAR2(150 BYTE),
                          DELDOC    VARCHAR2(50 BYTE),
                        GENDER       VARCHAR2(10 BYTE)
                        )
                        
                        
                        
                        
                        
                        
                        
                        • 9. Re: Form with Report - linking 2 records together on form from DB?
                          Gus C

                          Is INDENT is a shared ID.

                          If so, pass that through on your column link to a new page to populate a page item, eg P2_INDENT

                          You could then write 2 page load processes

                          select *******

                          into

                          "your page items"

                          from MARRIAGES

                          where INDENT = :P2_INDENT

                          and gender = 'M'

                           

                          select *******

                          into

                          "your page items"

                          from MARRIAGES

                          where INDENT = :P2_INDENT

                          and gender = 'M'

                           

                          Not ideal, but might work

                           

                          Gus


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

                            Hi Gus

                             

                            Thanks for your advice

                             

                            INDENT is a shared ID between the groom and bride but its not unique, as another bride and groom may have the same INDENT number.  What makes them unique is when the INDENT, INDVENUE and INDDOM columns are combined.

                             

                            As each bride and groom when they get married their entry goes into a registry book(INDVENUE) in this book each marriage entry (INDENT) shows the the page number/entry that the bride and groom are on in that book along with the date of their marriage (INDDOM)

                             

                            The structure is very flat file as this is what was extracted from an old legacy system.

                             

                            I just need to find a way of combining these 3 values so in the link column when clicked into the form from the interactive report it brings back the 2 records (bride and groom) associated with each other.

                            • 11. Re: Form with Report - linking 2 records together on form from DB?
                              Gus C

                              In the link you are able to pass multiple values.

                              So put INDENT, INDVENUE and INDDOM into the link and pass to 3 fields on the next form.

                              Then use those values in the sql statements

                               

                              Gus

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

                                Hi Gus

                                 

                                I have used the 3 name / values in the link report

                                 

                                P16_INDENT #INDENT#

                                P16_INDVENUE #INDVENUE#

                                P16_INDDOM #INDDOM#

                                 

                                But this would not link the bride and grooms records together?  Would this be set in the SQL statement under the 'condition type' section

                                 

                                And how would you then define from the resultset which INDSNAME and INDFNAME etc is the groom and which is the bride when displaying the output in the 'source' form field

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

                                  Rambo79

                                   

                                  Rambo79 wrote:

                                   

                                  Would this be set in the SQL statement under the 'condition type' section

                                   

                                  No the condition section is for conditional displaying the page item.

                                   

                                  And how would you then define from the resultset which INDSNAME and INDFNAME etc is the groom and which is the bride when displaying the output in the 'source' form field

                                  If you want the bride and groom information in a form:

                                  If you haven't already create 2 sets of page items for INDSNAME and INDFNAME etc one set for the groom and one set for the bride.

                                  Then create a process to be run on either before or after header process point.

                                  In this process you use a select statement using the P16_INDENT, P16_INDVENUE and P16_INDDOM page items in the where clause. And set the results into the corresponding bride and groom page items.

                                   

                                  If you want the bride and groom information in a tabular form.

                                  Create a tabular form region in which you select all the personal information. And after creation add the where statement using the P16_INDENT, P16_INDVENUE and P16_INDDOM page items.

                                   

                                  But as I have said before helping you is far easier and more constructive if we are able to see your application.

                                  So set up your application on apex.oracle.com.

                                  When we have developer access to your application we could even show you what we mean.

                                   

                                  Nicolette

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

                                    Thanks

                                     

                                    Could you clarify what you mean by 'header process joint'  and 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).

                                     

                                    And the select statement would be like?

                                     

                                    SELECT * FROM Marriages

                                    WHERE INDENT = :P16_INDENT

                                    AND

                                    P16_INDVENUE = :P16_INDVENUE

                                    AND

                                    P16_INDDOM = :P16_INDDOM

                                    AND

                                    GENDER = 'M'

                                     

                                    and

                                     

                                    SELECT * FROM Marriages

                                    WHERE INDENT = :P16_INDENT

                                    AND

                                    P16_INDVENUE = :P16_INDVENUE

                                    AND

                                    P16_INDDOM = :P16_INDDOM

                                    AND

                                    GENDER = 'F'

                                     

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

                                    1 2 Previous Next