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

 

[David Higgs]

Why did you ever consider such an index?

See Topic Link

Regards,
David

 

[Olaf Doschke]

Well, then you can use this for sorting, but not for seeking data.

You also did not pay attention to additional comments, eg I said:

If you'd like xxx333 to be sorted as 333 stripe off the letters with a CHRTRAN operation removing all letters or all non digits

As said INT(VAL("D22903")) is 0, the smae goes for ANY number after a letter. This is not even working for sorting.

Also note, that you can have as many indexes as you want for different purposes, but you have to use them correctly, and SEEKING a string in an index on the INT(VAL(field)) expression is wrong index usage. It's a wonder you find anything, you profit from some automatic conversions here, but you are more a victim of them.

Bye, Olaf.

 

[Olaf Doschke]

As you can only have one active index and want to use it both to reidentify/find child records and to sort them, the best solution is to right align your data. You have to change the SEEk value then and pad it to the field width to the left with spaces. The index expression then is simply the field, the right alignment of the data will sort it correctly and keeping the data as is will make it a unique seek and find value.

Bye, Olaf.

 

[David Higgs]

Hi Olaf,

I have a lot to learn and have got into some bad habits!

I have changed the Indexing to:-

PADL(ALLTRIM(loco_num),6)

and the SEEK to:- " 42105" i.e to right align the data

Thanks again for your help.


Regards,
David

 

[Olaf Doschke]

The devil is in the detail, as we say it. It's hard to always see or describe the full problem. It reveals itself later, as it now did.

You could generalize it as PADL(RTRIM(loco_num),LEN(loco_num)), but indeed the index expression rather should be LOCO_NUM only and you simply REPLACE ALL LOCO_NUM WITH PADL(RTRIM(LOCO_NUM),LEN(LOCO_NUM)), that should make the index usage easier, even though you have to take caution in the editing of that data, as future data tends to be left aligned again. So you have some pros and cons about making the index expression simpler and instead change the data itself.

Bye, Olaf.

 

[Mike Lewis]

David,

I see you have had a lot of good advice in this thread, and you appear to have solved the original problem.

I just wanted to add that anyone who is developing an application that includes a table of "BR Steam Locomotives Data" will surely always be an interesting visitor to the forum.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Hello Mike,

Making good progress thanks to the help of Tek-Tips members.

Unfortunately because of my sporadic Database Programming spanning over many years I have managed to pick up many bad habits and have not kept pace with latest developments. I first started compiling Railway Data on a SHARP MZ80A machine and then progressed to dBaseII+ and then finally VFP. Hopefully with the help of this forum I will be able to improve my limited programming skills.

My railway database contains over 125,000 entries and additional information is being compiled in Excel and then imported to VFP.


Regards,
David

 

[dbMark]

Back when my cousins and I were younger (1980s) they took 35 mm photos of every locomotive they encountered. I don't know if they've kept up with it. Me, I detoured into family genealogy in the pre-computer days (even visited the National Archives for records). I figure that's why I've enjoyed programming so much, both fields involve thinking and detective work.

 

[Mike Lewis]

I detoured into family genealogy

Given the title of this thread, I'm sure there's a bad pun in there somewhere.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads