This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Dec 11, 2013 9:20 AM by Nicolette RSS

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

Rambo79 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points