Including data post-filtering

[Former Member]

Hi all:

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. 

All tables currently have inner joins not enforced.

The data lines for repair orders and invoices are separate in the ORDERLN table and have to be linked via ORDERIDs.  There may be several lines for one repair order or invoice (part data, labor data, comments, etc).  The repair orders have their own line items with COST data and the invoices have their own line items with SALES data.

I need to limit my selected report data by the invoice closed date, which is contained in the ORDERS table. So the problem arises here where I need to include INVOICETYPE="invoice" within a date range, then the repair orders (also in the ORDERS table) get dropped.  I need these repair order IDs to access their corresponding cost information in the ORDERLN table.  The only data point i have to link the invoice and repair order in the ORDER table is via the REFORDERID -- see the image I attached.

I need a way to first restrict the invoice type and close date, then use the REFORDERID on those data records to pull in the associated repair orders (these would already be closed orders). 

I have all the sales data in my report working, displaying sales by part and invoice order ID. However, I can't get the part cost data for the repair order line items to display because the REFORDERID (in the ORDERS table) is nested within each invoice record. If I don't limit the data by invoicetype="invoice" I can get the cost data to work; however, I need to filter the whole dataset by invoice closed date.

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

[Former Member · Accepted solution]

I finally figured out how to solve my issue.  A union command was all that was required.

SELECT "REFORDERID","SHOPID","CUSTID","ORDERNUM"

FROM TFW.dbo.ORDERS

WHERE  "ORDERS"."ORDERTYPE"='invoice' AND ("ORDERS"."CLOSED">={?BEGINDATE} AND "ORDERS"."CLOSED"<={?ENDDATE})

UNION

SELECT "ORDERID","SHOPID","CUSTID","ORDERNUM"

FROM TFW.dbo.ORDERS

WHERE  "ORDERS"."ORDERTYPE"='invoice' AND ("ORDERS"."CLOSED">={?BEGINDATE} AND "ORDERS"."CLOSED"<={?ENDDATE})

[nscheaffer]

If I am understanding your situation correctly you need to join from the ORDERS table to the ORDERSLN table twice; once with the OrderID to get the Invoice line items and once with the RefOrderID to the the Repair line items.  See the link below for an example.

SQL Fiddle

If you needed any details from the ORDERS table for the Repair you could join the ORDERS table back to itself from RefOrderID back to OrderID

To put this into a Crystal Report you would have to use a Command object (Crystal Reports Tutorial Creating SQL Commands Business Objects Training Lesson 17.2 - YouTube).

Does that make sense?

Noel

[nscheaffer]

Or you could go with Dell's approach and then you wouldn't have to dive into the raw SQL.

Noel

[DellSC]

This is really not that difficult because Crystal will let you "alias" tables.  For example, in the Database Expert, you would add a second copy of your ORDERS table.  Crystal will tell you the table already exists and ask if you want to add an alias.  The table will then be added as ORDERS_1.  To prevent confusion, I would then select ORDERS_1 on the right of the screen, press F2, and rename it to REPAIRS.  Do the same thing with ORDERS and name it INVOICES.  You would then link from INVOICES to REPAIRS on the Order ID (or whatever field joins invoices to repair records).  In the Select Expert you would add something like the following criteria:

{INVOICES.INVOICETYPE} = 'invoice' and

{REPAIRS.INVOICETYPE} = 'repair'

To get the line items, you would also add another copy of your ORDERLN table.  To link things together, you would have the invoices table link to one copy of ORDERLN and the repairs table link to the other copy.   The picture below shows how the tables join together.

-Dell

[Former Member]

Thank you.  I have now tried this and I think I am getting closer, but the result is that the cost lines are now repeating for each sales data line item.  The sales data (tied to the invoice) is correct, but the cost lines from the repair order are repeating 5 times since there are 5 part lines on the invoice and repair order. 

Attached is an image of my data base links, the resulting report data.

For the Part Cost column of my report, the formula is:

   IF{REPLN.LINETYPE}='PART' THEN {REPLN.LINETOTAL}

For the Part Sale column, the formula is:

    IF{INVLN.LINETYPE}='PART' THEN ({INVLN.CHGAMT}*{INVLN.QTYREQD})