Join Issues

[Bennie47250]

Using Crystal version 7

Joining 2 tables together Inventory and Card. Using a left outer join as not all the inventory items have at least 1 cards and I always need to show all the inventory records. Pretty straightforward stuff.

However, in the Card table, we have Active and Inactive cards and I only want the information on Active cards. So if in my selection criteria I specify Card.Active = 1, I only get inventory records that have Active cards, so this overrode the reason for the outer join.

Looking for a workaround to this problem.

Not sure if this complicates the issue or not, but this is a many to many join, so I’m also getting to many records returned. (The inventory table is broken down by location and it is possible to have the same part number in 2, 3, 4 or more locations. The card table included the work cells for the parts and a part can be used in several different work cells) To overcome this, I used the grouping method to group on several different fields and hiding the detail records and only showing the summary of the fields on a footer row.

Thanks
Bennie

 

[Bennie47250]

Well I came up with 2 workarounds for this issue

1) Come up with a rule to suppress the group of records where the card table records is Card.Active = 0.

2) Have a view of the Card table that is only showing Active cards.

While I was able to get both methods to work, I going to use option #2