Still having trouble with SET RELATION 
Author Message
 Still having trouble with SET RELATION

I'm still having trouble with the SET RELATION that I have in the Init
of a form. Similar events seem to occur with the following command
window scenario:

Two free tables:

RELTEST1.DBF

id,category
1,colours
2,food
3,names

index set on id (integer)

RELTEST2.DBF

catid,id,name
1,100,red
1,101,green
1,102,yellow
2,103,cheese
2,104,bread
2,105,ham
3,106,jim
3,107,mary
3,108,charles

index set on both id and catid

Now type the following in the command window:

select 0
use reltest2
set order to catid              && foreign key
select 0
use reltest1
set relation to id into reltest2
go 2            && simply to move the record pointer somewhere

Now:

select reltest2
browse

        All records are shown, with the
        pointer pointing at the first relevant record.

Next try:

select reltest1
browse          && DON'T CLOSE THIS WINDOW!!!
select reltest2
browse

now with *BOTH* the browse windows open, by manually selecting a
record from the parent table, the child table will finally only show
the records it should.

This seems to be the only way I can get the correct records to display
in the browse window. Otherwise the whole lot will, with the record
pointer pointing at a relevant record.

Does this happen for you? What's going on? What else do I need to add
to make it work properly without opening browse windows?

I appreciate all heklp - I'm tearing my hair out over this!

ben



Fri, 17 Jan 2003 03:00:00 GMT  
 Still having trouble with SET RELATION
Ben,

It works correctly all the time.

When you open the child window first you see everything because you have the
child table as the current work area.  Then if you open the parent table and
SELECT it, it becomes the current work area and the child records are
"filtered" to show only those which belong to the parent.

My question is, are you trying to display data to a user using browse
windows?  Which version of FoxPro are you using?  In VFP you would be better
off using a grid control on a form to display parent and child data, in FPW
you can use the browse window but it's not easy to coordinate.

--

Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro

Duke Children's Information Systems
Duke University Medical Center


| I'm still having trouble with the SET RELATION that I have in the Init
| of a form. Similar events seem to occur with the following command
| window scenario:
|
| Two free tables:
|
| RELTEST1.DBF
|
| id,category
| 1,colours
| 2,food
| 3,names
|
| index set on id (integer)
|
| RELTEST2.DBF
|
| catid,id,name
| 1,100,red
| 1,101,green
| 1,102,yellow
| 2,103,cheese
| 2,104,bread
| 2,105,ham
| 3,106,jim
| 3,107,mary
| 3,108,charles
|
| index set on both id and catid
|
|
| Now type the following in the command window:
|
| select 0
| use reltest2
| set order to catid && foreign key
| select 0
| use reltest1
| set relation to id into reltest2
| go 2  && simply to move the record pointer somewhere
|
| Now:
|
| select reltest2
| browse
|
| All records are shown, with the
| pointer pointing at the first relevant record.
|
| Next try:
|
| select reltest1
| browse && DON'T CLOSE THIS WINDOW!!!
| select reltest2
| browse
|
| now with *BOTH* the browse windows open, by manually selecting a
| record from the parent table, the child table will finally only show
| the records it should.
|
| This seems to be the only way I can get the correct records to display
| in the browse window. Otherwise the whole lot will, with the record
| pointer pointing at a relevant record.
|
| Does this happen for you? What's going on? What else do I need to add
| to make it work properly without opening browse windows?
|
| I appreciate all heklp - I'm tearing my hair out over this!
|
| ben



Fri, 17 Jan 2003 03:00:00 GMT  
 Still having trouble with SET RELATION
Ben,

That's the way set relation is supposed to work.  Nothing is wrong.

Now, if you only want certain records to appear in the child table you don't
need set relation (although you can use it depending on how you want to set up
the child table).

Most people would probably advise you to consider either SELECT SQL or if you
are not dealing with tens of thousands of records, just use SET FILTER.  Either
way, problem solved...

ie:

select reltest2
set filter to reltest2.catid = reltest1.id
browse

(by the way, your example in your post was very clear and I commend you for
that...)

Quote:
>Subject: Still having trouble with SET RELATION

>Date: 7/31/2000 4:24 PM Pacific Daylight Time

>I'm still having trouble with the SET RELATION that I have in the Init
>of a form. Similar events seem to occur with the following command
>window scenario:

>Two free tables:

>RELTEST1.DBF

>id,category
>1,colours
>2,food
>3,names

>index set on id (integer)

>RELTEST2.DBF

>catid,id,name
>1,100,red
>1,101,green
>1,102,yellow
>2,103,cheese
>2,104,bread
>2,105,ham
>3,106,jim
>3,107,mary
>3,108,charles

>index set on both id and catid

>Now type the following in the command window:

>select 0
>use reltest2
>set order to catid          && foreign key
>select 0
>use reltest1
>set relation to id into reltest2
>go 2                && simply to move the record pointer somewhere

>Now:

>select reltest2
>browse

>    All records are shown, with the
>    pointer pointing at the first relevant record.

>Next try:

>select reltest1
>browse              && DON'T CLOSE THIS WINDOW!!!
>select reltest2
>browse

>now with *BOTH* the browse windows open, by manually selecting a
>record from the parent table, the child table will finally only show
>the records it should.

>This seems to be the only way I can get the correct records to display
>in the browse window. Otherwise the whole lot will, with the record
>pointer pointing at a relevant record.

>Does this happen for you? What's going on? What else do I need to add
>to make it work properly without opening browse windows?

>I appreciate all heklp - I'm tearing my hair out over this!

>ben

Mike


Sat, 18 Jan 2003 03:00:00 GMT  
 Still having trouble with SET RELATION

Quote:
>My question is, are you trying to display data to a user using browse
>windows?  

No, this was purely to try and get to the bottom of what is happening
in my form, explained further below

Quote:
>Which version of FoxPro are you using?  

6.0


Quote:
>Now, if you only want certain records to appear in the child table you don't
>need set relation (although you can use it depending on how you want to set up
>the child table).

I see. Does this make sense for the specifics of my forms, as follows:

The main heirarchy of tables in my database is as follows:

Clients
Sites   (each client can have several sites)
Plots   (each site has several plots)
Orders  (each plot can have several orders)

I have a form for each of these main tables. Sometimes, e.g. if opened
from the menu, I expect to see the entire table of data. So for
instance, if I open the sites form from the menu, I can use the VCR
navigation tools on my form and scroll through every site, regardless
of which client.

However, if I have the 'clients' form open, and click on a button on
the form to open the sites form, I only want this instance of the
sites form to show the sites relating to this particular client. The
form caption will be altered to indicate this.

It seemed to me that a 'SET RELATION' was ideal for this, but then I
ran in to this trouble.

Are you suggesting in this instance to use SET FILTER instead? In
which case, are there any other pitfalls I need to watch out for? For
example, if the little search tool I have on my form allows me to
search for records in the SITE table, and I have SET FILTER set, will
it also only find records in the filter set, or will I need to add
that as an additional condition?

Quote:
>Most people would probably advise you to consider either SELECT SQL or if you
>are not dealing with tens of thousands of records, just use SET FILTER.  Either
>way, problem solved...

I don't know if it will go to tens of thousands of records - it may do
in time. What pracitcal difference does this make?

Quote:
>(by the way, your example in your post was very clear and I commend you for
>that...)

thank you!

Ben



Sat, 18 Jan 2003 03:00:00 GMT  
 Still having trouble with SET RELATION
Hi Ben

The set relation command does not limit the records shown it simply moves to
the first related record when you move in the parent table.  It is just like
doing a Seek in the Child table only it happens automatically when you move
in the parent table.  This means you can browse records in the child table
that are not related to the parent table.

If you want to limit the child records to only those related to the parent
table you can you a number of techniques.  You can use "Set Filter to
Id=ParentID" or in the child table, you can use "Set Key to ParentId" in the
child table or you can use a "Select - SQL" to select the related child
records.

I often use the "Set Key" because it is quick.  Set Relation is faster than
a filter but it only positioning the child table to the first related
record.  it does not prevent you from move to records that are not related
to the parent table.

Simon


Quote:

> >My question is, are you trying to display data to a user using browse
> >windows?

> No, this was purely to try and get to the bottom of what is happening
> in my form, explained further below

> >Which version of FoxPro are you using?

> 6.0


> >Now, if you only want certain records to appear in the child table you
don't
> >need set relation (although you can use it depending on how you want to
set up
> >the child table).

> I see. Does this make sense for the specifics of my forms, as follows:

> The main heirarchy of tables in my database is as follows:

> Clients
> Sites (each client can have several sites)
> Plots (each site has several plots)
> Orders (each plot can have several orders)

> I have a form for each of these main tables. Sometimes, e.g. if opened
> from the menu, I expect to see the entire table of data. So for
> instance, if I open the sites form from the menu, I can use the VCR
> navigation tools on my form and scroll through every site, regardless
> of which client.

> However, if I have the 'clients' form open, and click on a button on
> the form to open the sites form, I only want this instance of the
> sites form to show the sites relating to this particular client. The
> form caption will be altered to indicate this.

> It seemed to me that a 'SET RELATION' was ideal for this, but then I
> ran in to this trouble.

> Are you suggesting in this instance to use SET FILTER instead? In
> which case, are there any other pitfalls I need to watch out for? For
> example, if the little search tool I have on my form allows me to
> search for records in the SITE table, and I have SET FILTER set, will
> it also only find records in the filter set, or will I need to add
> that as an additional condition?

> >Most people would probably advise you to consider either SELECT SQL or if
you
> >are not dealing with tens of thousands of records, just use SET FILTER.
Either
> >way, problem solved...

> I don't know if it will go to tens of thousands of records - it may do
> in time. What pracitcal difference does this make?

> >(by the way, your example in your post was very clear and I commend you
for
> >that...)

> thank you!

> Ben



Sat, 18 Jan 2003 03:00:00 GMT  
 Still having trouble with SET RELATION

Quote:
> The main heirarchy of tables in my database is as follows:

> Clients
> Sites (each client can have several sites)
> Plots (each site has several plots)
> Orders (each plot can have several orders)

> I have a form for each of these main tables. Sometimes, e.g. if opened
> from the menu, I expect to see the entire table of data. So for
> instance, if I open the sites form from the menu, I can use the VCR
> navigation tools on my form and scroll through every site, regardless
> of which client.

> However, if I have the 'clients' form open, and click on a button on
> the form to open the sites form, I only want this instance of the
> sites form to show the sites relating to this particular client. The
> form caption will be altered to indicate this.

Ben,

If you use a SQL-SELECT for your sites, plots, and orders (viewed from a
client context), it would look like this:

SELECT * FROM SITES WHERE sites.ClientID = clients.ClientID

Then, when you open Plots from the Client's context, you'll have the records
for that client. It's very clean, tho' working with SQL takes some getting
used to if you're an old xBase person as I am. :)

You could even set up a local, paramaterized view to do this, and you'll
want to if you want to edit the data.



Sun, 19 Jan 2003 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Having trouble with SET PRINTER TO

2. more trouble w/ set relation

3. SET RELATION TO does not break all relations...Part 2

4. Having trouble updating a 1-Many child browse.

5. Having trouble opening a database obtained from GETFILE command

6. Still having trouble with INSERT INTO

7. Foxpro 2.6 for Mac having trouble launching----HELP

8. Having trouble executing a batch using sql pass through

9. Having trouble switching DBC's in a form

10. Having trouble with providing context sensative help.

11. I'm having trouble using the same table in two forms

12. Newbie having trouble with toolbar

 

 
Powered by phpBB® Forum Software