Index/sort order issues...

[vatchunter2]

I'm viewing 4 tables at one time, Table A is "driving" the data I see on the other tables . Table B, C,and D are all index on a field 123 and the order is ok except for Table D, I would like to use the same index but order the data using fields 1234.

Any suggestions would be appreciated.

Frank

 

[JRB-Bldr]

except for Table D, I would like to use the same index but order the data using fields 1234.

I am not clear on what you want.

Do you want the relation from TableA (Relationship 'Parent' table) into TableD (one of the Relationship 'Child' tables) be working on the field value of TableD.Field1234 ?

If so, then just build a TableD Index expression on Field1234

SELECT TableB
SET ORDER TO F123  && Activate Index built on Field123

SELECT TableC
SET ORDER TO F123  && Activate Index built on Field123

SELECT TableD
SET ORDER TO F1234  && Activate Index built on Field1234

SELECT TableA
SET RELATION TO Field123 INTO TableB ADDITIVE
SET RELATION TO Field123 INTO TableB ADDITIVE
* --- Set up relation between TableA.Field123 & TableD.Field1234 ---
SET RELATION TO Field123 INTO TableB ADDITIVE

< do whatever >

Good Luck,
JRB-Bldr

 

[vatchunter2]

JRB,

Sorry I didn't clarify what I am trying to achieve. Your suggestion works but not exactly the way I hoped. Please see illustraion below:

Table A contains many records about Joe Smith, if I set my relation up with an Index on fields 123, all records are shown in Table D about Joe Smith. I want to be able to see all records about Joe Smith in Table D, with an ascending sort order field 4, see example below for the result am I needing.

Table A
1 2 3 4
Joe Smith Address State Gold


Table D
1 2 3 4
Joe Smith Address State Diamond
Joe Smith Address State Emerald
Joe Smith Address State Glass
Joe Smith Address State Gold
Joe Smith Address State Platinum
Joe Smith Address State Ruby
Joe Smith Address State Silver
Joe Smith Address State Wood

 

[Olaf Doschke]

JRB-Bldr, I suspect there is no Field123 or Field1234, but Vatchunter2 is talking about the first 3 and 4 fields of tables.

That said you can create an index expression concatenating 4 fields and SEEK only the first, two, three or four fields or any partial value as in "starts with" conditions. And that also applies to relations.

So if you have an index on f1+f2+f3+f4 you can make a relation via SET RELATION TO f1+f2+f3 INTO TableD, only using f1+f2+f3 from TableA to lookup records in the index on f1+f2+f3+f4 of TableD, that's possible. And the brows of TableD then will be sorted by f4 via its sort order.

There is only one thing to keep in mind for compound indexes: VFP always needs an index expression, it does not allow specification of a multiple field index, in the form of a field list f1,f2,f3,f4, like you could do in MySQL or SQL Server or others. But you should know that. In the simple case the index expression can be any field name of the table, in the simple case all fields are char fields you can index on f1+f2+f3+f4, if you have mixed types the typical solution is to convet non char types to char, eg dates via DTOS(), integers via BINTOC(), etc and finally concatenate these. The only limit is 240 bytes length for the resuilt value of the expression.

For the index itself it doesn't matter from how many fields the partial values came, it's just one single result value per record, on which the index is built.

You know you can also seek partial field values in a single field index, eg if you INDEX ON lastname TAG last you can SEEK("A","table","last") to find the first record with a lastname starting with A. And as an index node is made up of the index expression value, that also applies to more complex index expressions, the index expression doesn't matter anymore, when you seek or a relation seeks in the cdx, which is built on the single value per record, which resulted from the index expression. Therefore you can find f1+f2+f3 in an index on f1+f2+f3+f4, you can also find f1+Left(f2,1) or any partial value from the left, you can't find f2+f3+f4 in the index, that should also be clear.

Bye, Olaf.

 

[JRB-Bldr]

Olaf - I suspect there is no Field123 or Field1234, but Vatchunter2 is talking about the first 3 and 4 fields of tables.

Upon seeing their example, I guess that I was right when I told them:
I am not clear on what you want.

Field1 - Name <Joe Smith>
Field2 - Address <whatever>
Field3 - State <whatever>
Field4 - Diamond <whatever>

My first recommendation is to not use the data architecture you have shown.

Instead you could add a single CustomerID or ClientID field (most likely an Integer) identifying the individual 'Parent'/TableA records.

Field1 - CustomerID <100>
Field2 - Name <Joe Smith>
Field3 - Address <whatever>
Field4 - State <whatever>
Field5 - Diamond <whatever>

Then instead of having to duplicate all of those fields (1, 2, & 3) in each of the 'Child' tables - B, C, D, you would instead have only the single matching field CustomerID or ClientID

Then you build the Indexes on that 'ID' field - so your indicies need not be so Complex (as Olaf suggests above).

With that more 'normalized' data architecture, you can more easily get your results in whatever manner you need them.

I am not sure how you are planning to use/display the data from the 'related' tables, but if you are only needing the data from TableD in the record sequence you described, when you have a match on the Field1 + Field2 + Field3 (or better yet - CustomerID), then you could do a SQL Query command which would yield your results and you could incorporate the ORDER BY clause to sequence the resultant record set.

SELECT *;
  FROM TableD;
  WHERE CustomerID = 4;
  [b]ORDER BY Field4 DESC;[/b]
  INTO CURSOR TableDResult READWRITE

Good Luck,
JRB-Bldr

 

[NasibKalsi]

JRB's original solution should work as is except there is a typo error

instead of this
.
.
SELECT TableA
SET RELATION TO Field123 INTO TableB ADDITIVE
SET RELATION TO Field123 INTO TableB ADDITIVE
* --- Set up relation between TableA.Field123 & TableD.Field1234 ---
SET RELATION TO Field123 INTO TableB ADDITIVE

should be
.
.
SELECT TableA
SET RELATION TO Field123 INTO TableB ADDITIVE
SET RELATION TO Field123 INTO TableC ADDITIVE
* --- Set up relation between TableA.Field123 & TableD.Field1234 ---
SET RELATION TO Field123 INTO TableD ADDITIVE