GTRR blind spot

[sandyinlet]

Two tables and two TOs. TO "Users" is based on the Users table; TO "Join.target" is based to a Join table. The link between the two is this:

TO Users TO Join.target

GlobalField1 = Field1

AND

GlobalField2 = Field2.

I need to isolate (perform GTRR) on the Join table to show the single record where Users::GlobalField1 = Join::Field1 AND Users::GlobalField2 = Join::Field2.

Because the Join table has no relation to the User table other than through the two global fields, I've created this script.

Set Users::GlobalField1 and Users::GlobalField2 to desired values.

New Window

Go to Layout: Join.target.

Go to Related Record [show only related records; From table: "Join.target", Using layout: .

No change to the currently displayed records. Not sure where to go from here. Help appreciated.

[David Jondreau]

Drop the Go to Layout[] step and change the GTRR step to use that layout and you should be fine.

DJ

[sandyinlet]

Drop the Go to Layout[] step and change the GTRR step to use that layout and you should be fine.

DJ

Here's the message I get:

"This operation could not be completed because the target is not part of a related table."

I've been trying several experiments and in all I get either no change in the records displayed or I get the above message. No idea why.

With kind regards,

[Fenton]

To use fields in a relationship, the fields on the "originating" side of the relationship must be in the current table (whether global fields or not). As DJ pointed out, once you use Go To Layout [ Join ], the global fields are no longer IN the current table, so the relationship fails. So, you can either drop the Go To Layout [ Join ], and let the Go To Related Record step specify which layout.

Or you can put the globals in the Join table instead, and redo that globals' relationship as a self-relationship. Then you can Go To Layout [ Join ] and Go To Related Record [ self Join globals' relationship; current layout ]

Whichever is better; much the same in most cases. But the method using the self-relationship is more flexible, as it allows you to set those globals from any other table, and it all still works.

[sandyinlet]

To use fields in a relationship, the fields on the "originating" side of the relationship must be in the current table (whether global fields or not). As DJ pointed out, once you use Go To Layout [ Join ], the global fields are no longer IN the current table, so the relationship fails. So, you can either drop the Go To Layout [ Join ], and let the Go To Related Record step specify which layout.

Or you can put the globals in the Join table instead, and redo that globals' relationship as a self-relationship. Then you can Go To Layout [ Join ] and Go To Related Record [ self Join globals' relationship; current layout ]

Whichever is better; much the same in most cases. But the method using the self-relationship is more flexible, as it allows you to set those globals from any other table, and it all still works.

Still having problems. I've opted to use the self-relationship technique. No longer receiving the error message but all records continue to be shown.

Because I'm not sure how to identify the "originating" side of a relationship my test script first tries using one side then the other. Neither selects the record required. All fields are number fields.

I've attached screenshots of both the TO's and the testing script.

With kind regards,

[comment]

The first part of your script is almost correct. However, when you do GTRR, you want to show related records from the OTHER side of the relationship - otherwise it makes little sense.

[sandyinlet]

The first part of your script is almost correct. However, when you do GTRR, you want to show related records from the OTHER side of the relationship - otherwise it makes little sense.

Finally it works. But before closing this issue, a final question. Because I wasn't sure which way to go, I tested both ways, that is a layout based on one side of the relationship and a GTRR based on the other; then reversed both the layout and the GTRR.

I assumed both would work, but only one did. Why is that? Is it possible to predict which pairing will work and which will not?

Much appreciated,

[Fenton]

The target fields of a relationship must be fields which can be indexed (stored). Global fields cannot be, so they will not work properly as targets of relationships. Except when using the Cartesian X operator, in which case it's always all records, no matter what. Something like that.

[comment]

See also:

http://fmforums.com/forum/showtopic.php?tid/192839/