CODEWITHSUNDEEP
crystal-reportsreport
John
John

Reputation: 1

Crystal Reports including multiple records with queries

I have a database table named ORDERS that contains repair order data and invoice data. Each record has an ORDERTYPE (i.e. INVOICE or REPAIR) and all invoice records have a system-generated ORDERID that links them to the repair orders (called the REFORDERID).

I have a second database table called ORDERLN that contains all individual line items for the repair orders and invoices. I need to access this data selectively to retrieve sales and cost information.

I need to limit my selected report data by the INVOICE DATE, which is contained in the ORDERS table. I have been trying to limit the report's record selection by DATE and INVOICE TYPE but that restricts me to only working with INVOICE data. I need sales data, which is on the invoice and I can get this easily, but I also need cost data which is only on the repair order, hence my need to query that data as well.

I need a way to first restrict the invoice close date, then pull in the invoice data AND pull in the repair order data. The link to the repair order data must be made via the REFORDERID records.

Can anyone guide me either with record selection formulas or SQL queries?

Thanks, John

Upvotes: 0

Views: 1998

Answers (1)

Siva
Siva

Reputation: 9101

As per your requirement and explanation.. below is my approach...

  1. Link the ORDERS and ORDERLN tables in database expert.
  2. To limit the data create 3 parameters Start Date, End Date and one parameter for Invoice type.
  3. In record selection formula write below formula.

If you are picking the invoice date form ORDERS then

ORDERS.Invoice Date>={?Start Date} and ORDERS.Invoice Date<{?End Date} and 
ORDERS.Invoice Type={?Invoice Type}

Above selection will pick the user required date and invoice type from database.

  1. Now place the fields you need in the report design

Upvotes: -1

Related Questions