Solved

Crystal Reports - Link one field to two different tables

Posted on 2008-10-24
10
1,871 Views
Last Modified: 2011-09-20
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
Comment
Question by:bruno71
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
Comment Utility
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
 

Author Comment

by:bruno71
Comment Utility
I tried that, but it is still only showing data from one or the other table.

~bruno71
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:bruno71
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:bruno71
Comment Utility
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
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 500 total points
Comment Utility
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
 

Author Comment

by:bruno71
Comment Utility
Awesome!  That worked!  I didn't realize that that table join would make a difference.  Thanks for your help.

~bruno71
0
 
LVL 18

Expert Comment

by:UnifiedIS
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now