• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1891
  • Last Modified:

Crystal Reports - Link one field to two different tables

We are using Crystal Reports 7 and I have three tables that I need to link together.  One table has production scrap information.  The other two have the production order information.  They are linked by the production order number.  One of the production order tables has all the past orders, the other one has the current orders.  The scrap table contains the info for all orders.  I would like to be able to link both of the order tables to the scrap table, so that if the production order number from the scrap table is not found in the current order table, it will pull it from the past order table.  Is this possible?  Thanks.

~bruno71
0
bruno71
Asked:
bruno71
  • 4
  • 3
  • 2
  • +1
1 Solution
 
UnifiedISCommented:
You'll want to left join both production order tables to the scrap table, then use a formula to determine which production order number to show on the report.

If ISNULL(Current production order #) = TRUE then
Old Production Order #
ELSE Current production order #
0
 
bruno71Author Commented:
I tried that, but it is still only showing data from one or the other table.

~bruno71
0
 
peter57rCommented:
That is what you said you wanted...

"if the production order number from the scrap table is not found in the current order table, it will pull it from the past order table"
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mlmccCommented:
As stated you need to LEFT OUTER JOIN the tables

The SQL should look like

FROM ScrapTable LEFT OUTER JOIN CurrentProd ON ScrapTable.OrderID = CurrentProd.OrderID LEFT OUTER JOIN PastOrders ON ScrapTable.OrderId = PastOrders.OrderId

In the report just put fields from oth CurrentProd and PastOrders.  If one is NULL you wil not see the data.

mlmcc
0
 
bruno71Author Commented:
When I run the report, it either shows only data from the past orders table or only data from the current orders table.  It depends on which order number field I put in the details section (past or current).  If I put both (or use the formula as stated above) it just pulls from the last table it used.

Here is the SQL from the report...


SELECT
    GW_SCRAP."CutterOp", GW_SCRAP."Shift", GW_SCRAP."CutDate", GW_SCRAP."TotalPcsCut", GW_SCRAP."CutterScrap", GW_SCRAP."TotalScrap", GW_SCRAP."MachineNum", GW_SCRAP."Burn", GW_SCRAP."Lag", GW_SCRAP."Obstruction", GW_SCRAP."CutOff", GW_SCRAP."OpError", GW_SCRAP."Damage", GW_SCRAP."WireBreak", GW_SCRAP."DoveTail", GW_SCRAP."Thin", GW_SCRAP."Other", GW_SCRAP."Comment", GW_SCRAP."WaterSpots", GW_SCRAP."PoorFusion", GW_SCRAP."ColorContamination", GW_SCRAP."Camber",
    sfordfil_sql."ord_no",
    PPORDFIL_SQL."ord_no",
    IMITMIDX_SQL."drawing_release_no"
FROM
    { oj (("DATA"."dbo"."GW_SCRAP" GW_SCRAP LEFT OUTER JOIN "DATA"."dbo"."PPORDFIL_SQL" PPORDFIL_SQL ON
        GW_SCRAP."ProdOrdNum" = PPORDFIL_SQL."ord_no")
     LEFT OUTER JOIN "DATA"."dbo"."sfordfil_sql" sfordfil_sql ON
        GW_SCRAP."ProdOrdNum" = sfordfil_sql."ord_no")
     INNER JOIN "DATA"."dbo"."IMITMIDX_SQL" IMITMIDX_SQL ON
        sfordfil_sql."item_no" = IMITMIDX_SQL."item_no"}
WHERE
    GW_SCRAP."CutDate" >= 20080901 AND
    GW_SCRAP."CutDate" <= 20081031
ORDER BY
    GW_SCRAP."CutDate" ASC,
    GW_SCRAP."Shift" ASC,
    GW_SCRAP."CutterOp" ASC

Open in new window

0
 
mlmccCommented:
That makes sense unless I am missing something.  I would suspect when an order is complete it gets moved from the Current table to the Past table.  Therefore an order only exists in one table.

mlmcc
0
 
bruno71Author Commented:
Sort of...actually we moved to a new type of production system.  So the old system orders are in the "old" table and the new and ongoing orders are in the "new" table.  The production order number is in the same format, but the old numbers are numbered around 125000 and the new ones start at 2000.
Is this just a limitation of version 7.0?

~bruno71
0
 
UnifiedISCommented:
I think you're going to have to change this to an outer join:
INNER JOIN "DATA"."dbo"."IMITMIDX_SQL" IMITMIDX_SQL ON
        sfordfil_sql."item_no" = IMITMIDX_SQL."item_no"}

LEFT OUTER JOIN "DATA"."dbo"."IMITMIDX_SQL" IMITMIDX_SQL ON
        sfordfil_sql."item_no" = IMITMIDX_SQL."item_no"}
0
 
bruno71Author Commented:
Awesome!  That worked!  I didn't realize that that table join would make a difference.  Thanks for your help.

~bruno71
0
 
UnifiedISCommented:
Your welcome, glad it is working for you.

When you inner join a table that has been outer joined, you basically override the outer join and they both act as inners.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now