Linking tables with conditions

[tuigvlieg]

Hi I am a new with Crystal report

I have 2 tables for the same database. The location and pallet table. I want to show only the locations for which no pallet is available. In the table pallet are pallet from history that have a value zero. If such a record is available the location should show that no pallet is available.

Situation:
Location table Pallet table Result
A Pallet: I Qty 2 Do not display
B Pallet: II Qty 0 Display loc on rep
C No record Display loc on rep

How should I do this in a report?

 

[lbass]

Use a left join from the Location table to the Pallets table, and use a record selection formula like:

isnull({Pallet.ID}) or
{Pallet.Qty} = 0

Place the location field in the details section.

-LB

 

[tuigvlieg]

Hi lbass,

This what I did and thought should work but is not.

The result I had is that the table pallet had a filter so only showed when qty > 0. If the qty was > 0 then the location table only showed.

The location table should always show. My understanding of a 'left outer join' is that when no record is found in the table pallet the location table should show and not the pallet table. But this is not working in my report.

Mvg,

 

[lbass]

Use the left join and remove the selection criteria. Create a formula:

if isnull({Pallet.ID}) or
{Pallet.Qty} = 0 then {Location.location} else ""

-LB