SET RELATION and index problem 1

[mibosoft]

I have three tables with a nonnumeric relational expression issued with SET RELATION. I would like one of the child tables to be ordered on another field then the common field.

Example:
-Child table 1 holds soccer games.
-Child table 2 holds players.
-Parent table holds soccer tournaments.
-The common field is "tournament-ID"
-Data from the tables above shall be printed via a report layout.

Problem: Howto order child 1 (games) in datetime?

Code:

select child1 && games
*** Shows no games at all
set order to tag TIME

*** Works but shows wrong order
*** set order to tag tourn-ID

select child2
set order to tag tourn-ID

sele parent
set order to tag tourn-ID
set relation to tourn-ID into child1,tourn-ID into child2

 

[Imaginecorp]

As far as I know, you can’t do that as it will break the relationship or give you unpredictable results. Why not create a View, then you can set any order you want...

 

[mibosoft]

In the report layout, I use multiple detail bands with data from the different tables. Can that be done also with views? (Never used it).

Thanks'
Micael

 

[JRB-Bldr]

If the desired Child table is not already in the correct order, you can always do a SQL Query into another temporary table. That query can order the resultant table as you want.

SELECT *;
   FROM Child1;
   WHERE <if parameters needed>;
   ORDER BY Time;
   NOCONSOLE;
   INTO TABLE TempChild

SELECT TempChild
INDEX ON Tourn-ID TAG Tourn-ID

SELECT Parent
SET ORDER TO Tourn-ID
SET RELATION TO tourn-ID INTO TempChild1 ADDITIVE
SET RELATION TO tourn-ID INTO Child2 ADDITIVE

<Do Whatever>

* --- "Clean House" ---
ERASE ChildTemp.dbf
ERASE ChildTemp.cdx
[Code]

True, the results are made using a "copy" of Child1, but its records are in the desired sequence.

Good Luck,
JRB-Bldr

 

[white605]

You can also add the same table to the data environment more than once and set relations and filters differently. This may not be good practice but it has worked in several reports for me.

 

[mibosoft]

Thanks' all for your hints. Will give it a try later today and I think the temporary table solution will work fine.
/Micael

 

[craigber]

You can make a compound index:

INDEX ON ForeignKey + Time + OtherField TAG Linker

Then use the new tag in the relation.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[mibosoft]

Craig,
How do you mean "use this tag in the relation"? As soon I add a second sorting key like:
INDEX ON tourn-ID + Time tag ID_TIME
it won't work.
/Micael

 

[JRB-Bldr]

The most likely reason that Craig's suggestion is not working for you is that you have:
SET EXACT ON
With that on you would need to exactly match the index expression with your SET RELATION TO <expression>.

But since the TIME changes per record in Child1 you would not be able to make an Exact match.

You could do a
SET EXACT OFF
Prior to setting up the Relation and it should work.

There are some risks with that approach in that you might get a "match" with a non-truly-related record.

But it is a simple approach if it can work for you.

Good Luck,
JRB-Bldr

 

[mibosoft]

Many thanks' JRB-Bldr! This solution is just what I want and best of all, there is no need for a temporary table.
/Micael