One to many relation

[pkohli88]

Hi,
I have 3 tables. Main table , secondary_loction table and
secondary_label table.

Now Main table and Secondary_location has one to many relation. e.g

NAME               LOCATION   
trip245            New York
trip245            Ohio

Main Table and Secondary_label also have one to many relation. e.g

NAME               LABEL   
trip245            Software
trip245            Hardware

Now I want to create a report. Split LOCTION & LABEL on sep rows. And Allow user to select wheather to show LOCATION or LABEL or BOTH)

So result . in case user select BOTH

ROW      NAME          LOCATION     LABEL
1        trip245       New York
2        trip245       Ohio
3        trip245                    Software
4        trip245                    Hardware

In case user select only LOCATION then i want to show only first 2 rows

In case user select LABEL want to show only last 2 rows.

Thanks

 

[MrBillSC]

I think you will need to use a subreport.
Example:
In main report link from Main table to Location table with left outer join.
Group on Name.
Add name and location to detail section.
Create subreport that reads the Label table. Sort on Name.
Add Name and Label to detail section leaving a blank space where the "Location" field will be in the main report.
Insert subreport into Main report group footer for Name and link by Name.
In the main report conditionally suppress the group footer for Name (containing the subreport) or conditionally suppress the field Location depending on the user's response to your parameter.

MrBill

 

[synapsevampire]

Post your software version and the database used.

This is a fairly simple UNION query for a command object, which will prove faster than usinga subreport.

But we can't giveyou quality help you if you won't post the basics.

The query would be something like:

select 'location' MySource, name, location from secondary_location
union all
select 'label' MySource, name, label from secondary_label

Now you in essence have a table with them joined together, and a field which designates it's origin (MySource).

You could even join to the main table in the query, but that's sql dependent and you didn't share that.

-k

 

[pkohli88]

Thanks for your reply,
I am using crystal version 10 and oracle 10g database.
yes, i can use union query in the command. but how can i incorporate parameters in the report. for example whether user wants to display LOCATION only or LABEL only or BOTH.

 

[lbass]

I think you could do the following, using a parameter created within the command:

select 'location' MySource, name, location
from secondary_location
where '{?choice}' in ('LOCATION','BOTH')
union all
select 'label' MySource, name, label
from secondary_label
where '{?choice}' in ('LABEL','BOTH')

When creating the parameter, add prompt text that instructs the user to enter "LOCATION","BOTH", OR "LABEL".

If you think they might not enter it in upper case, then change '{?choice}' to ucase('{?choice}').

-LB.