We help IT Professionals succeed at work.

Set Relation/Set skip

Dan Moerman
Dan Moerman asked
I have an issue with something that has worked many times before, but not so much now.  I have a large database of uses of plants by native peoples of north america, 44,691 uses.  (Database is online at naeb.brit.org if you'd like to see what it's like}  It's native form is FoxPro.  I got a request from some college kids in Oregon who want to do a virtual tour of useful trees on their college campus. I said I'd help.  I got a list of all the tree species in the county in Oregon where the college is.  113 species.  I put their names (latin binomials) in a small database, named MarionORTrees.dbf.  The main listing of all the plants is titled bothitem.dbf Here's the list of commands I used. Sciname is the names of the species in Marion County.  Synthname is the latin names of all the species in the database.

USE "d:\data files\databases\synth99\MarionORTrees.DBF"
USE "d:\data files\databases\food\bothitem.DBF"
BROWSE FIELDS a.sciname,b.synthname,b.comname,b.tribe,b.use,b.descrip
BROWSE LAST FOR b.synthname#"  "

The last command should show all the relevant info for the items in the main database for trees in Marion County.  But it doesn't. I have various outcomes, some really loony, none really right.

I'm using what I believe to be the latest version of Windows 10 Pro: Build 15063

Any suggestions?
Watch Question

The program stops on the
BROWSE FIELDS a.sciname,b.synthname,b.comname,b.tribe,b.use,b.descrip
then it waits for BROWSE window closing
and then the next BROWSE (the last command) displays the data for given condition.

Use following command:
BROWSE FIELDS a.sciname,b.synthname,b.comname,b.tribe,b.use,b.descrip NOWAIT
It was just incorrect question understanding maybe... Try the following:


Another option would be to display results in two BROWSE windows.
If you just have problem to display the last BROWSE window with the format equal the previous BROWSE command then you should look at your resource file.

To obtain the resource file name look at SYS(2005). If you empty the resource file then it should start working again.
Dan MoermanEmeritus Professor


The third time I set the problem up with 2 browse windows, it worked; not easily, but it did work.  Got the results into an excel file and sent it on to the students in Oregon.  Still puzzled why a technique I've used for years didn't work this time without a lot of fiddling around. Ordinarily, I set a relationship between the two files, set skip on, and open one browse window with all the information matching the smaller file.  Then do with it whatever I want.  No longer apparently.  One day FoxPro is not going to work at all any more, and I'm going to mourn.  Thank you pcelba. None of your other suggestions worked, but that one did.  Thank you.
If the two BROWSE windows work then SET SKIP should also work.

If you would post some data sample then I could investigate more. Your code should work and I am sure the problem is at your side not at FoxPro side... This does not mean FoxPro is bug free.

To investigate more I would need to know several settings etc.:
FoxPro version
Code page (both FoxPro and tables) and collation used at run-time and in indexes
The two tables structure (you may create it by LIST STRUCTURE TO FILE )

You should check the sizes of columns used in the relation, you should reindex your tables and you should better describe what means "it does not work". I don't know what the BROWSE shows or whether it even appears... EE allows to post images so you may create some hardcopy of your screen and tell what's wrong with it.

BTW, the build 15063 isn't the latest version but that's irrelevant in this case.
Dan MoermanEmeritus Professor


I have a busy day;  I can't do this all today.  I can say that the browse "did not work" several ways.  First, I seemed to have a working one browse window with a query made from two data files (dbfs). However when I moved the cursor from one item to the next, lines wiggled, and disappeared.  Never ever seen anything like that before.  On other occasions, I'd get the first column (from the smaller file), and nothing at all from the larger file.  It varied rather dramatically (randomly?) from one attempt to the next.

Not sure what a "code page" is. Please advise.  I'll try to do the rest tomorrow.

Thanks for your help.

Wiggled lines could be caused by certain FxoPro - Windows 10 incompatibility. FoxPro had certain problems with video drivers ever...

Also,  SET SKIP is old fashioned approach how to display data and to avoid this way was good decision. If you don't like two browse windows then much better approach is to create just one cursor from the data and display this one cursor in one browse window.

The code could be like this:
SELECT a.sciname,b.synthname,b.comname,b.tribe,b.use,b.descrip ;
  FROM ("d:\data files\databases\synth99\MarionORTrees.DBF") a ;
  LEFT JOIN ("d:\data files\databases\food\bothitem.DBF") b ON b.synthname = a.sciname ;
 ORDER BY a.sciname ;
  INTO CURSOR cOneOutput


Open in new window

If you use INNER JOIN instead of LEFT JOIN then the output should contain just MarionORTrees rows which are present in the bothitem table.
Of course, some additional tuning is still possible.  If you need blank instead of NULL values display then use SET NULLDISPLAY, if you would like to avoid repeating value in the a.sciname column then it needs some additional code etc.

Important is b.synthname and a.sciname must have the same size and both tables do have the same Code Page. If you know nothing about Code Pages then it is OK most probably. Look at CPDBF() and CPCURRENT() FoxPro functions. Values 0 and 1252 are OK for the correct processing.

Code Pages and collations are used for proper accented characters processing in FoxPro.

Above query does not show records from bothitem.DBF which do not have corresponding sciname in MarionORTrees.DBF.
Dan MoermanEmeritus Professor


I had already responded to this, but, it seems not to be here.

Many thanks to pcelba.  The SELECT command worked beautifully, very clear, got everything in a few seconds.

CPBFD() = 0
CPCURRENT() = 1252

Regarding SET SKIP being "old fashioned."  I started using FoxBase ca1985.  I had been using dBASE, but I learned early on that, with data sets of a few thousand items, what dBASE took overnight to do, FoxBase could do in a few seconds.  At the time, the software was produced by a group of kids in southern Ohio who actually answered the phone themselves when I needed help. The documentation was clear, and I used it to create a very elaborate data entry system for what was to become my database of 44,000+ uses of plants by native American people for drugs, foods, dyes, fibers, and, occasionally, "Others." As noted, to see the online version, try http://naeb.brit.org .   The upshot of all this is that most of my experience with Fox* was done before most folks on Experts Exchange were born.  That's my apology for being "old fashioned."  

I deeply appreciate the expertise of folks here.  Odd stuff ordinarily gets cleared up in a few minutes.  Thanks to all of you.  dan
Yah... I still have dBase 2  on 8" diskette somewhere...

The problem with old fashioned commands is not our age but the fact these commands were loosely tested in the new VFP OO world so you may observe C000005 error easily when using them intensively and in combination with object elements.