Self Join problem

[raymanj]

I am trying to create a self join relation.

Setup:

Table Occurences:

"Table", "Table SelfJoin" and "Table SelfJoin Global"

Fields:

marked_b = number can be 0 or 1

constant_n = number auto enter set to 1

constant_gn = global number set to 1

Relationships (2)

"Table" >> "Table SelfJoin", when marked_b = constant_n

"Table" >> "Table SelfJoin Global", when marked_b = constant_gn

Problem:

Neither of these two relations work either they show all records or none. No matter if I create five records in "Table" and set the marked_b field to 1's and 0's.

can anyone tell me what I am doing wrong, I have attached a sample file of what I have exampled above.

FM 7.0v3

Table.zip

[djgogi]

If you want to see all recs with marked_b set to 1 than the relationship should point from constant_n to marked_b and not viceversa (as you have set).

Otherwise if you need to see all records with values of marked_b depending on current record value, you should create a relationship on the marked_b field itself

Dj

[raymanj]

If you want to see all recs with marked_b set to 1 than the relationship should point from constant_n to marked_b and not viceversa (as you have set).

This is one point in FM 7 that I have never understood, how do you set the direction of a relationship? I thought in Filemaker all relations are bidirectional; therefore, it doesn't matter which side they are on. If it does matter which side, then how do I set the fields to thir proper sides.

Otherwise if you need to see all records with values of marked_b depending on current record value, you should create a relationship on the marked_b field itself

I am not looking for this.

What I need to see is all the records that have marked_b set to 1.

I am still a bit confused on doing this.

[Ugo DI LUCA]

Hi,

Create a new TO, then pick constant_n in new TO2 to marked_b in TO

Then, delete the newly created TO and do the reverse.

Create a new TO, the pick constant_n in TO to marked_b in TO2

See it ?

[raymanj]

I must be explaining this wrong, because I did what you said and I still don't get the results I want.

When I do the marked_b = constant_n relation the portal shows nothing when no records are set to marked_b = 1, this is ok. When I set one of the records to marked_b = 1 then all records are shown even if they are set to 0, this is not what I want. I only want the the records with marked_b set to 1.

I still don't see how to set a certain field on either side of the relationship. I I try setting fields to certain sides Filemaker always sets them to its way, after I click the OK button.

I try setting constant_n on the left = marked_b on the right. Then FM flips them when I come back to the "Edit Relationship" window.

[-Queue-]

Relationships are bi-directional, assuming no unstored calculations or globals are involved in either key field. However, the TO you are currently in or referencing from determines how the relationship behaves.

If your layout is based on {Table} and your relationship is from {Table}:marked_b to {Table} SelfJoin::constant_n, then the relationship can only be evaluated from marked_b to constant_n from that layout. If you were in a layout based on {Table} SelfJoin, then it could only be evaluated from constant_n to marked_b. So, to make it easy, you want to define your relationship from {Table}::constant_n to {Table} SelfJoin::marked_b. This way you can base your layout on {Table} and your portal will only display records where marked_b matches constant_n.

Does that make a little more sense?

As long you don't click Cancel after redefining your relationship (opening the existing one, 'flipping' the fields, clicking 'Change' then 'OK' and 'OK' to close the relationship definitions, you should have no problems with FM reverting the change.

[raymanj]

If your layout is based on {Table} and your relationship is from {Table}:marked_b to {Table} SelfJoin::constant_n, then the relationship can only be evaluated from marked_b to constant_n from that layout. If you were in a layout based on {Table} SelfJoin, then it could only be evaluated from constant_n to marked_b. So, to make it easy, you want to define your relationship from {Table}::constant_n to {Table} SelfJoin::marked_b. This way you can base your layout on {Table} and your portal will only display records where marked_b matches constant_n.

Thank very much. This section explained what I was misunderstanding. When everyone was talking about setting fields on a side of a relation. The part where:

{Table}::constant_n to {Table} SelfJoin::marked_b

Showed me that I had to pick the fields for the tables. I thought I had to have the TOs on their respective sides. So I tried getting the TOs to show up on the left or the right sides of the relation. This is where I was confused about bi-direction relations. This has nothing to do with that.

Thanks a lot guys. You have enlighten me about Filemaker.