difficult filtered portal

[Marek P]

Hello,

I often seemed to run up against this kind of problem and I haven't found a satisfactory strategy for dealing with it.

I think this is a pretty clear example, which I've created to hopefully simplify things.

Let's say I'm trying to keep track of baseball statistics.

I have two tables:

player table: playerID_pk, name, Bats_LeftRightOrSwitch

yearly_stats table: playerID_fk, year, batting average percentage (note: we do this out of 1000 (=100%)... we just do, that's why.)

Bats_LeftRightOrSwitch field values can be: left (bats left-handed), right (bats right-handed, or switch (switch hitter- bats either way).

Obviously, it's a one to many relationship--a player can have stats for more than one year.

Player Table:

P001	Brad Harman	right

P002	Dave Nilsson	left

P003	Justin Huber	right





Yearly_Stats Table:

P001	2008	.100

P002	1992	.232

P002	1993	.257

P002	1994	.275

P002	1995	.278

P002	1996	.331

P003	2005	.218

P003	2006	.200

P003	2007	.100

P003	2008	.246

If I want to have a portal of right handed batters and their averages in 2008, what's the best way to do this?

Let's assume the layout is based on a table occurrence other than these two tables.

If I use a calculated field in the yearly_stats table that is Bats_LeftRightOrSwitch_c, and contains the value from the player's table, then it must be unstored (in Filemaker), and so it can't be used on the lower side of a relationship.

If I have a text field with an auto-entry calculation doing the same thing (again in the yearly_stats table), then it won't necessarily be in sync if I made a mistake in the player table and have to modify the Bats_LeftRightOrSwitch field.

I'm hoping for a solution that doesn't involve scripts, because I don't want the user to do an extra action just to edit that one field. But I realize there may not be a way to solve this without scripts (or plugins), and if that's the case, please let me know, so I can consider alternatives.

I've tried to make this as clear as I can. I imagine this scenario is fairly common.

Usually apologies for missing previous posts on the subject.

Thanks for any help you can provide.

Marek

[IdealData]

If I have a text field with an auto-entry calculation doing the same thing (again in the yearly_stats table), then it won't necessarily be in sync if I made a mistake in the player table and have to modify the Bats_LeftRightOrSwitch field.

If you are accepting that data entry mistakes will be made then why not provide a way to correct the data? You could possibly use the same portal as a direct data entry method or a really simple GTRR in a new window.

[comment]

The trick is to place the second filtering field in one of the viewed tables, for example:

First relationship:

Viewer::gLeftOrRight = Players::LeftOrRight

Second relationship:

Players::PlayerID = Stats 2::PlayerID

AND

Players::gYear = Stats 2::Year

A portal from Viewer to Stats 2 will show only the stats that meet both criteria. To display the player names alongside the stats, you can either define an unstored calculation in Stats, or attach yet another TO of Players behind Stats 2 and put a field from there in the portal.

Both global fields can be placed on a layout of Viewer, so it's transparent to the user.

[Fenton]

In case anyone's wondering, I deleted both of my posts, because I didn't notice at first that there was only ONE record per year per player in the statistics table. Hence my solution was more complex than it needed to be for this problem.

[Marek P]

Comment,

Thanks! That did the trick.

Fenton,

Thanks too. I managed to download your example before you removed it. It was helpful.

IdealData,

If I understand you correctly, you're suggesting that the user would edit the field in the related records as well as the field in the Player table.

I would like the user to be able to make the correction in one place, and not have to know about the related fields.

Thanks for all the responses. They saved me a lot of time.

Marek