setting relations

[learnfox2]

How can I set the relation of 'file a' to 'file b' based on account number.

 

[dgrewe]

Requirements
1. The fields in the 2 tables MUST be the same type and length
2. There must be a index on table B on the field the relation is set to.
3. Code

close data all
select 0
use DBFB alias TableB order INDEXNAME
select 0
use DBFA alias TABLEA
set relation to FIELDNAME into TABLEB

As you move around in TABLEA, TABLEB will be on the related Record





David W. Grewe Dave

 

[bborissov]

David,
The first is not necessary, but if it is true that would make your life easier . Imagine that in TableA that field is numeric(6) and in other field is C(6) you could set relation that way:

SET RELATION TO PADR(TRANSFORM(NumericField),6) INTO TableB

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[dgrewe]

Would not want to do it. Take Data Normalization and the reasons for Primary Keys and throw them away.


David W. Grewe Dave

 

[bborissov]

Me too,
Just for the info, you don't have to have SAME type and length for the fields to have relation. Just an example. We have IBAN code here and some BIC codes. Chars from 5 to 8 in IBAN are related with first 4 chars in BIC (and they are unique) So instead of having intermediate table with BIC matching we jus have relation (OK, not a relation, we JOIN these tables) with something like:

SET RELATION TO SUBSTR(Iban,5,4) INTO BIC

and have index to LEFT(BicCode,4) in BIC table.
BIC tables are issued from our National Bank and this is an internal table which can't be changed by user.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP