Looking for alternative data after finding no data

[Viscount]

i do not know if this is dificult or not - but I cannot seem to get my head around it.

I have 3 tables:

Customer Info
First Order
Follow-up Orders

What I want to do is create a report with a date parameter that firstly looks to the first order field and checks to see if there is an order for that date. If there is no order (either there has been no orders at all - or the date of the first order is passed [or yet to come])then it needs to look to the follow-up orders table to see if there is any data there for the customer and return it.

The fields in the first order and follow-up orders tables are similar.

An example of the data is...

table.customer

ID Name

12345 Bob Jones
54321 Tom Smith
23456 Billy Bob

table. first order

ID Date Description

12345 01/01/2003 xyz

table. follow-up orders

ID Date Description

54321 01/01/2003 abc
23456 02/01/2003 def

What I want is....

Date Parameter: 01/01/2003

ID Name Description New / Follow-Up

12345 Bob Jones XYZ New
54321 Tom Smith abc Follow-Up

Any ideas..?

Thanks Vis

 

[dgillz]

write a simple formula to check for the existence of an order as follows:

If Isnull({Orders.Data}) then {FollowupOrders.Data} else {Orders.data}

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Ngolem]

I don't think it is quite as simple as you lay out.

How are you linking these tables?

Does the {?date} parameter value apply to both tables??? What happens if data exists in both tables for a given ID/Date

I suppose you could have a left outer join on Table 1 to Tables 2 & 3 on the basis of ID but the minute you place a resrtiction on the date that kills the outer join

My approach would be to create a Main report based on Table 1

Then you would have 2 subreports based on Tables 2 and 3
in separate sections.



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lbass]

Jim is right that knowing the table links would be helpful. I'm assuming you have left joins from customer to each of the other tables.

You might try the following, assuming you have a discrete date value. For your record selection formula use:

{?date} = {firstorder.date} or
{?date} = {fuporder.date}

I think that if a record satisfies the first condition, the second will not be evaluated--this means that the parameter would return the correct date for the customer in the table that had the date and all dates for the other table. Then lay out the fields for each table in separate detail sections as follows:

{cust.ID} {cust.name} {firstorder.date} {firstorder.desc} //detail_a
{cust.ID} {cust.name} {fuporder.date} {fuporder.desc} //detail_b

Then format detail_a to "underlay following sections" and also format detail_a to suppress using the following formula:

{firstorder.date} <> {?date}

Then format detail_b to suppress with this formula:

{fuporder.date} <> {?date}

This should work fine as long as there can't be both a first and a followup order on the same date.

-LB

 

[Viscount]

Jim

Thanks for your comments - currently I have the advantage of linking the tables in any way I want to - the only field that needs to be linked is the customer ID.

The date parameter will apply to both tables. With regards to the same date data being in both tables this will not (should not..!) be an issue as the first order table also contains important information used for other purposes.

-LB

Thanks for your help and i will try to write it up today and let you know how I get on.

Vis.