Calculation to enforce Child uniqueness

[Wickerman]

Hi -- Let's say I have a Team table in which each team gets a unique number, like Team 101, 102, 103 etc.

I have a simple child table for Players, so that each player is a member of one team (each team has many members). Each player gets a number, too, 01, 02, 03 etc. and these numbers must be unique *within* the team.

I want each player to have a unique let's call it "League Number" that is a concatenation of his Team # and Player # joined by a period:

101.01

101.02

101.03

102.01

102.02

etc . . .

My question is how to create the validation calc on the Player # field so that it allows many player to have the '01' number (since every team will have a player '01') -- but prevents any two players on a single team from having the same number.

My thought was to create a Self-join (a second TO of the Players table) with the relationship set for matching foreign key fields,which would relate record that share a common parent Team record. Then a Validation calc on the PlayerNumber like this:

ValueCount ( FilterValues (List ( 2_Players::PlayerNumber ; PlayerNumber) ) ) > 0

I'm thinking that this should create a list of all player numbers of team members for this player's team, filter that list for the ones that are the same as the one I'm adding, count how many and return a "fail" if there is more than 0 (no teammate should have that # already).

Doesn't work, no matter how I've tinkered. So, I'm thinking I must be going about this simple task the wrong way, probably getting the behavior of the filtervalues wrong?

Any help appreciated.

[comment]

My question is how to create the validation calc on the Player # field so that it allows many player to have the '01' number (since every team will have a player '01') -- but prevents any two players on a single team from having the same number.

How about making League Number a text field with auto-entered calculation, and validating it as Unique, (Validate always)?

[Wickerman]

I need the user to be able to change the Player number if necessary -- he may need to change them from time-to-time . . .

Also, we may at times need to change a Team number and have the players numbers automatically change as a result, which the concatenation approach does nicely. . . .

[comment]

I meant auto-entered calculation with replacing existing value.

[Wickerman]

Sorry, to be slow, but I still don't understand.

Do you mean an auto-enter calc that replaces the entered Player number with a concatenation of the team number and this newly-entered player number? If so, how does this auto-entry get re-triggered when I change a team number?

Otherwise, I don't understand how setting this "Unique" validation works, since many players (on different teams) will have the same Player number.

I'm wondering, too, what's wrong with my initial approach -- I'm trying to understand why my strategy isn't working in order to better understand FMP calcs.

Thanks again . . .

[comment]

I guess you had to be there...

ValidateUniqueChild.zip