SET RELATION TO issue

[David Higgs]

Hi,

I have two Free Tables that I'm trying to "Link" using SET RELATION TO and can't seem to get it to work.

Both TABLES have a FIELD called NUMBER and have exactly the same INDEX, same FIELD Type & Length.
When I run the following code it finds the NUMBER in "steam_data" TABLE but reaches the EOF in "steam_alloc" TABLE

	use \BR-STEAM\data\br_steam_alloc IN H order NUMBER ALIAS steam_alloc

	use \BR-STEAM\data\br_steam_data IN J order NUMBER ALIAS steam_data	
	
	SELECT steam_data	&&  BR Steam Locomotives Data
	
	SET RELATION TO NUMBER INTO steam_alloc 
	
	SEEK "47500"
	
	BROWSE
	
	SELECT H	&& BR Steam Locomotive Allocations
		
	BROWSE

ANy help would be most appreciated.


Regards,
David

 

[Dan Freeman]

For style points, stop using workarea letters. It's a concept that was obviated in the late 1980's.

Use <table> In 0 Order Whatever

Are the fields involved in the relation the same length and data type?

Did you know that without the relation you could

BROWSE FOR NUMBER = "47500"

 

[David Higgs]

Hello Dan,

Sorry for using "workareas", old habits!

The BROWSE commands are there just for my de-bugging; to see if both tables are "linked/related"

Regards,
David

 

[JRB-Bldr]

If, during development, I have a question regarding how and/or if a Relation is working as intended, I open both the Parent and Child (or multiple Child) table(s) each into their own BROWSE window.

Then, once the Relation is established, as I move the record pointer through the Parent table I can visually see what is occurring (or not) in the Child table(s).

Once you have gained some confidence on how Relations work you should most likely not need to do this often.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

If you name a field number, why would it be a char field? Shouldn't you SEEK 47500 instead of "47500"?
If you want to verify the seek goes somewhere in the main table and a related record is found, then you can just look at FOUND() or EOF() of both aliases.

What I prefer is first BROWSING, then position somewhere (same as JRB-Bldr), not the other way around, but indeed a BROWSE does not reposition the record pointer, so the likely reasaon is you don't find the string "47500", you'll perhaps find the number 47500. Clicking in the browse of the parent table also has the advantage you know you specify an existing root record, the one you click on.

The match may also not work, if numbers are in char fields, and one table has them left aligned, the other right aligned or centered. If you have string fields you do string comparisons, not numeric comparisons.

Bye, Olaf.

 

[David Higgs]

Hi Olaf,
The reason why I use the field "Number" is because some "locomotive numbers" also contained Characters i.e DEP50
The SEEK "47500" is working in the parent table but the child table is at EOF().
All the Locomotive Numbers are "Left Aligned".

Regards,
David

 

[Olaf Doschke]

As you seem to use IDX indexes, did you ensure the child table index is up to date? Only CDX indexes in the main cdx file (named same as a dbf) are maintained automatically, an index not set is not updated, that may cause your EOF().

Bye, Olaf.

 

[David Higgs]

Both Indexes are CDX.

Another reason for using a CHARACTER Filed for numbers is some locomotives also have a preceding zero i.e "08629"

Regards,
David

 

[Olaf Doschke]

Well, simply SEEK "49500" in the child table to see, if the index is correct. If you expect to find it, but don't do so, a REINDEX should solve that. Somehow your index is not up to date, if the SEEK goes to EOF().

Bye, Olaf.

 

[David Higgs]

The SEEK in the Parent table works fine, it's the CHILD table where the pointer is at EOF().

If I open each indexed table separately and do a SEEK all is ok it's just appears that the relationship is not working.

Regards,
David

 

[JRB-Bldr]

If I open each indexed table separately

When you set up the secondary, terciary, etc. RELATIONs are you using the command Option ADDITIVE?

If not, then setting up the 2nd RELATION will eliminate the 1st and so on.

USE ParentTable IN 0
USE ChildTable1 IN 0 ORDER Key1
USE ChildTable2 IN 0 ORDER Key1
USE ChildTable3 IN 0 ORDER Key1

SELECT ParentTable
SET RELATION TO KeyFld INTO ChildTable1 ADDITIVE
SET RELATION TO KeyFld INTO ChildTable2 ADDITIVE
SET RELATION TO KeyFld INTO ChildTable3 ADDITIVE

* --- Now ALL of the Relations are maintained ---
* --- Without the ADDITIVE option, only the last single Relation is in effect ---

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

>If I open each indexed table separately and do a SEEK all is ok it's just appears that the relationship is not working.

Well, JRB-Bldr has an idea with the ADDITIVE, but as far as you show your code you only have one SET RELATION.
Then just make triple sure you have the data aligned in the same way and with same collations and code pages in the dbfs.

Also please change the order of things, as you browse the child data last, the browse of it isn't influenced by the parent data determining the relation. The relation gets active, when you click into the parent table browse window, not when you click into the child table window.

Bye, Olaf.

 

[dbMark]

Rather than browse the tables separately, you can try this, do both tables display as expected in both columns?

BROWSE FIELDS steam_data.number, steam_alloc.number

In your initial example only one table, steam_alloc, required an index for SET RELATION TO for success. Activating another index might be okay, just not required for the SET RELATION.

Also, I believe "number" is a reserved code word in VFP. Could that be the issue? Can you choose another field name to eliminate a risk that there is some conflict with the VFP environment? (I did a quick test and "number" seemed to work okay in this scenario, but just saying... Years ago we had to change a field "name" originally developed in FP-DOS because it conflicted with classes or screens.)

With both tables open can you DISPLAY STATUS and visually compare the index expressions|keys for each table & index, making sure they're identical?

 

[JRB-Bldr]

NUMBER" is indeed a VFP Reserved word.

https://msdn.microsoft.com/en-US/library/xztfc506(v=vs.80).aspx

That field name should be changed to maybe something like: LocoNumb (or whatever)
It can still be a Character type.

Good Luck,
JRB-Bldr

 

[NasibKalsi]

I think there is no problem with the NUMBER being the FieldName, in this situation. However, NUMBER being the CDX name in both instances is questionable. I think he is using the NUMBER in the CDX index qualifier, else dbMark' browse statement should have worked.

        * Code with suggested changes
        use \BR-STEAM\data\br_steam_alloc   order NUMBERS ALIAS steam_alloc  && NUMBERS for Slave
	use \BR-STEAM\data\br_steam_data    order NUMBERM ALIAS steam_data   && NUMBERM for Master		
	SELECT steam_data	&&  BR Steam Locomotives Data	
	SET RELATION TO NUMBER INTO steam_alloc 
        browse field steam_date.NUMBER, steam_alloc.NUMBER

 

[David Higgs]

Many thanks for the contributions, here is a quick update.

I have changed the "NUMBER" field to "loco_num" and when I run the following code "s_data.loco_num" the Record Pointer is at "46245" but the "s_alloc.loco_num" column is blank, no entries at all.

*
*			TEST PROGRAM
*

Set Path To  R:\PC-Raildata
Close Databases

&&
&&	Steam Locomotive Database
&&

Use \BR-STEAM\Data\br_steam_alloc Order lnum_s Alias s_alloc  IN 0 && NUMBERS for Slave

Use \BR-STEAM\Data\br_steam_data Order lnum_m Alias s_data  IN 0 && NUMBERM for Master

Select s_data	&&  BR Steam Locomotives Data

Set Relation To loco_num Into s_alloc

SEEK "46245"

Browse Fields s_data.loco_num, s_alloc.loco_num

This is a copy of DISPLAY STATUS


Processor is Pentium
Currently Selected Table:
Select area: 2, Table in Use: R:\PC-RAILDATA\BR-STEAM\DATA\BR_STEAM_DATA.DBF Alias: S_DATA
Code page: 1252
Structural CDX file: R:\PC-RAILDATA\BR-STEAM\DATA\BR_STEAM_DATA.CDX
Master Index tag: LNUM_M Collate: Machine Key: INT(VAL(LOCO_NUM))
Lock(s): Exclusive USE
Related into: S_alloc
Relation: LOCO_NUM

Select area: 1, Table in Use: R:\PC-RAILDATA\BR-STEAM\DATA\BR_STEAM_ALLOC.DBF Alias: S_ALLOC
Code page: 1252
Structural CDX file: R:\PC-RAILDATA\BR-STEAM\DATA\BR_STEAM_ALLOC.CDX
Master Index tag: LNUM_S Collate: Machine Key: INT(VAL(LOCO_NUM))
Lock(s): Exclusive USE

File search path: R:\PC-RAILDATA\
Default directory: C:\PROGRAM FILES (X86)\MICROSOFT VISUAL FOXPRO 9
Print file/device:
Work area = 2
Margin = 0
Decimals = 2
Memowidth = 50
Typeahead = 20
Blocksize = 64
Reprocess = 0
Refresh = 0, 5.000 SECONDS
DDE Timeout = 2000
DDE Safety = on

Code page: 1252
Collating sequence: Machine
Compiler code page: 1252
Date format: British
Macro Hot Key =
UDF parameters are passed by: VALUE
Textmerge Options
Delimiters: Left = << Right = >>
Show

Alternate - off Fullpath - on
ANSI - off Heading - on
Asserts - off Help - on
Bell - on Intensity - on
Blink - on Lock - off
Brstatus - off Logerrors - on
Carry - off Mouse - on
Century - on Multilocks - off
Clear - on Near - off
Color - on Null - off
Compatible - off Optimize - on
Confirm - off Print - off
Console - on Readborder - off
Cursor - on Safety - on
Deleted - off Space - on
Device - scrn Status Bar - on
Echo - off Sysmenus - on
Escape - off Talk - on
Exact - off Textmerge - off
Exclusive - on Title - off
Fields - off Unique - off
Fixed - off





Regards,
David

 

[Olaf Doschke]

One more thing to check is what EOF("steam_alloc") is right after the SEEK in the steam_data table before any browse.
The thing is, the record pointer of steam_alloc is not locked, the record pointer can freely move there after the SEEK in the parent table.

If you even have EOF then, please take a closer look at the data, it does not match then, the secondary seek in steam_alloc is not a SEEK "49500", the relation does a SEEK of steam_data.number, including trailinng spaces, taking into account the code page.

Bye, Olaf.

 

[Olaf Doschke]

Ouch!

Your indexes are on INT(VAL(LOCO_NUM))
Then SET RELATION TO INT(VAL(LOCO_NUM)) into s_alloc

You have to let the relation look up the index expression. Either that or just have a index on LOCO_NUM.

Bye, Olaf.

Edit: You will want an index on LOCO_NUM only, as the values are as you earlier said, contain numbers with letters, too, but those would result in 0, as in INT(VAL("D22903")=0.
Why did you ever consider such an index? It would only make sense, if values are numbers only, and even in that case you would rather make it an int field. If you have a char field for some reasons, then simlpy index it itself.

 

[David Higgs]

One more thing to check is what EOF("steam_alloc") is right after the SEEK in the steam_data table before any browse.

? EOF("s_alloc") = .T.



Regards,
David

 

[David Higgs]

Your indexes are on INT(VAL(LOCO_NUM))
Then SET RELATION TO INT(VAL(LOCO_NUM)) into s_alloc

Olaf, you've "hit the nail on the head!!" All working fine now!

Many thanks, much appreciated!

Regards,
David