Table Linking

[ajr1705]

Hi

Simple question, when linking multiple tables which table should be the primary table.

E.g. . Header, Detail, Detail1

Provided all the above tables are linked by key is it best to start with Detail or Header

Thanks

 

[Madawc]

You've misunderstood. Crystal works by 'rows', with each row including one record from each linked table. It can be the same record in different rows, of course.

Each 'row' is a detail line. If you group detail lines, then you have a group header and footer, which relates to something shared by all of the detail lines.

There's also a report header and page header, functions as the names suggest.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Madawc]

Tables can sometimes be linked several different ways and it will not matter, it depends what you do. The norm is equal joins but you can do a 'left outer', where you link tables A and B and you want each record on A even when there is no B for it. In this case, the values on B will still be on the detail line but as null values.

Having come to Crystal from mainframe languages, I got a 'cultural shock' when encountering null. It means 'no data': Mainframe languages mostly treat this as the same as zero.
It is actually a finer shade of meaning, the difference between 'Yes, we have no bananas' and 'I don't know how many bananas we have, it could be some, it could be zero'. In Crystal, the entry is 0 or null and can be tested for.
Note that Crystal assumes that anything with a null means that the field should not display. Always begin with something like

if isnull({your.amount}) then 0 
		         else {your.amount}

or

if isnull({your.date}) 
or {your.date} in [{Start-Date} to {End-Date}) then "OK"
else "not"

Or else

if isnull({your.amount})  
then "no value found" 
else ToText({your.amount})

The 'ToText' allows you to mix numbers and text, and also has interesting format options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ajr1705]

Apologies for not making my self clear. My question is if
Table 1 has 1000 records
Table 2 has 10000 records
Table 3 has 10000 records

And they are related by key then which table should be the left most. What I am asking does it make a difference on how fast the report will run (optimization) depending on the link.

 

[Madawc]

Sorry, I've always assumed it didn't matter. Does anyone know?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Generally, it makes sense to start with the table with the least records, since values in related tables will be evaluated for each row in the first table, but it does depend upon what you are trying to do.

-LB

 

[ajr1705]

Ibass, this is what I tought , but if your reports parameter or groupings are based on fileds on the larger table is it better to start with the larger table.

Do you know any document I can look at on this with differebt examples of linking.

Thanks

 

[lbass]

I understand what you mean, but I think it still has to test the parameter against all rows, so again I think the smallest table first is better--you could test this.

The CR help has info on linking, and the George Pect CR books also give examples of the impact of various ways of linking.

-LB

 

[ajr1705]

Thanks Ibass