services wanted Will pay for formula

[Cazito]

Wondering if anyone could give me a hand on this.

As a hobby I have this database that keeps track of all the matches of the soccer team I support. See attached photo (It's in portuguese, my native language).

There's mainly two tables: a table called Match with fields like Date, Goals Scored, Goals Allowed, Opponent, Tournament, Venue, City, Referee, etc..; and a table called LineUp with fields like Number (1, 2, 3...), Position (G for Goalkeeper, Z for Defender, M for Mid-fielder, A for Forward and S# for substitutes), Player, Goals Scored, Time of Goals, Yellow Cards and Red Cards.

The table LineUp is linked to the table Match by a key field called kp_jogo_id in the Match table and kf_jogo_id in the LineUp table (but not by the Date field as I can have two matches played on the same day) in a one-to-many relationship. For each match there's many players that have played that match. The table LineUp acts as a portal in the form above (see attached photo).

Now for my problem.

I would like to create a calculation field called LineUp in the Match table (not in the LineUp table) that would return the line-up in the following text format (for the example shown) that I could use as a merge field in reports:

Fluminense: Diego Cavalieri, Mariano, Gum, Márcio Rosário and Carlinhos; Edinho, Diguinho (Valencia), Souza and Marquinho (Fernando Bob); Rafael Sobis (Ciro) and Rafael Moura.

As you can see, I have ";" separating the different sectors of the field and substitutions in parenthesis.

I'm thinking of creating some auxiliary fields (for use in the LineUp calculation field I'm creating) in the Match table that would count the number of players in each sector of the field for each match. In the example shown it would be Z=4, M=4 and A=2 but it could be any arrangement (4-3-3, 4-2-4, etc...).

Maybe I could use some If or Case functions in the formula as the following relations hold true:

If N=11 would return "." (although there's one instance in the beginning of the last century where only 10 players lined-up for a game).

If N=Z+1 or N=Z+M+1 would return ";"

If N=Z or N=Z+M or N=Z+M+A would return "and"

Else if would return ","

In the example shown

After player 11 comes "."

After players 5 and 9 comes ";"

After players 4, 8 and 10 comes "and" after all the other players comes ","

Don't know how to work the substitutions though, as sometimes you have a player replacing a substitute in which case the text should look like: "original player (substitute, substitute of the substitute)"

I'm trying to put all this reasoning into a formula that would start like this:

"Fluminense: " & ...

This is not for any professional use, it's just a hobby but I'm willing to pay thru PayPal for a final working formula or solution.

My e-mail: [email protected]

[Vaughan]

It looks as hough you want a comma separated list of the player names. If the panel on the right of the screen shot is the players names, and they are in related records, then use the List function to generate the players in a paragraph dlimited list:

List( Players::Playername )

The Substitute function can be used to replace the return with commas.

Substitute(

List(Players::Playername ) ;

"¶" ; ", "

)

[comment]

An interesting lineup of problems, I'd say...

I think I got the more difficult parts - the issue of "and' is left as an exercise for the reader.

LineUp.zip

[Vaughan]

Blah, I somewhat missed the complexity.

[comment]

somewhat

LOL

[Cazito]

Thanks for all your trouble and your quick response Comment. Will study your solution, try to understand it and apply it to my DB. Will let you know how it goes. Thanks once again.

[Fitch]

Nice job as always, Michael. Cazito, if it turns out that a calculation won't cover every exception, keep in mind that you can always write a script that loops through the portal and creates the text. Boa sorte!

[Cazito]

I must say this worked without a glitch in my db. Kudos for Michael for devising such an elegant solution.

This is excellent for whenever you want to lay down in text format subsets of names punctuated by commas, periods, etc...

Here's how I've done it:

1) First I created in the LineUp table a field called Replaced# defined by the calculation " Filter ( Position ; "0123456789" ) " which is basically the Positon field without the letter. So "G", "Z", "M" and "A" becomes a blank and "S#" becomes only the number (S10 becomes 10 for example).

2) Then I created a table ocurrance of the LineUp table on the Relationships Graph called Sub and linked it to the LineUp table by the fields SerialID=SerialID and Shirt#=Replaced#.

3) I then created two new fields in the LineUp table defined as follows:

cSubs = Case ( Sub:SerialID ; List ( Sub::Name; Sub::cSubs ) )

cLineUpName = Case ( not Replaced# ; Name & Case ( Sub::SerialID; " (" & Substitute ( cSubs; ¶ ; ", " ) & ")" ))

4) And last I created a field called cLineUp in the Match table defined as follows:

cLineUp = "Fluminense: " & Let ( [

players = List ( LineUp::cLineUpName );

posiitions = List ( LineUp::Position );

countplayers = ValueCount ( players );

countD = ValueCount ( FilterValues ( posiitions "G" ) & FilterValues ( posiitions "Z" ) );

countM = ValueCount ( FilterValues ( posiitions; "M" ) );

countA = ValueCount ( FilterValues ( posiitions; "A" ) ) ];

Substitute (

LeftValues ( players; countD - 1 ) & "#" &

MiddleValues ( players; countD; 1 ) & "+" &

MiddleValues ( players; countD + 1; countM - 1 ) & "#" &

MiddleValues ( players; countD + countM; 1 ) & "+" &

MiddleValues ( players; countD + 1 + countM; countA - 1 ) & "#" &

RightValues ( players; 1 ) & ¶ ;

[ "¶#"; " and " ]; [ "¶+"; "; " ]; [ "¶¶"; "." ]; [ "¶"; ", " ]

)

)

The end result for the record at the top of the page is:

Fluminense: Diego Cavalieri, Mariano 2, Gum, Márcio Rosário and Carlinhos 6; Edinho 5, Diguinho (Valencia), Souza 3 and Marquinho 3 (Fernando Bob); Rafael Sobis (Ciro) and Rafael Moura.

I now need to find a way of eliminating the numbers from the player names which I use only for differentiating purposes in the original table.

Maybe I can accomplish that with a formula like Filter ( Name ; "AaBbCc...") but probably there's an easier way as I would have to use all the letters of the alphabet plus accented letters in that formula.

Thanks again Michael for all your trouble.

[comment]

There is one important point that you may have missed: the sort order applied to the Matches -< LineUp relationship.

LeftValues ( players; countD - 1 )

You should watch out for sections with only one player. I know it's not very likely, but still.

find a way of eliminating the numbers from the player names

These numbers, if required, should be in another field. Incidentally, so should be the substitutions. They have nothing to do with the "position".

BTW, it seems a case of a defender replacing a mid-fielder would be misrepresented in this method.

[Cazito]

Thanks Michael.

Yes there are some instances where there is only one forward. And the result did came wrong. Glad you pointed it out. Will correct that. Will take a look at the sort order too.

I find it easier to differentiate two players wth the same name by different numbers after the name, instead of creating another field (a player id# for example) but I understand your point. Also as I obtain all kind of statistics on players from the LineUp table it doesn't really matter if he started the match or entered as a substitute during the game. Statistically for me it's the same, either way it's one more match played for him. The only purpose for the Position field is to separate the players for the cLineUp field and for me to know if the team played in 4-2-4 or a 4-3-3, etc... formation. In my Player table I do have have another Position field populated with each player's most common position historically, as a forward can play some games as a mid-fielder for example.

Thanks for pointing out all this to me.

All the best.

[Cazito]

Hi.

I eliminated the number from the players name with the formula:

Substitute ( cLineUpName ; [" 1" ; ""] ; [" 2" ; ""] ; [" 3" ; ""] ; [" 4" ; ""] ; [" 5" ; ""] ; [" 6" ; ""] ; [" 7" ; ""] ; [" 8" ; ""] ; [" 9" ; ""] )

It works fine as long as I don't have any player with a number greater than 9 in its name. Wish that I could use wildcards in formulas as I use in Finds.

The problem of the sections with only one player I solved with the formula:

If ( countA = 1; ""; MiddleValues ( players; countD + 1 + countM; countA - 1 ) & "#" & ) &

substituting

MiddleValues ( players; countD + 1 + countM; countA - 1 ) & "#" &

and so on for the other sections.

The sort order I didn't understand but it doesn't seem to be affecting the end result which came right to all matches I looked.

Now I'm trying to create a field in the Match table that lists the yellow cards received by the players at each game in the following text format (for the example of the photo):

Yellow cards: Diego Cavalieri and Carlinhos.

obs: The yellow cards is the CA field (second column from right to left in the LineUp table portal) on the photo.

I can pretty much get the punctuation right at this point but was wondering if anyone give me a hint of how can I obtain a List (LineUp::name) that would include only the players whose CA (Yellow Cards) field is 1.

I'll then apply the same logic to goals and sent-offs.

Have I told you guys I love FileMaker?

Thanks in advance.

[comment]

It works fine as long as I don't have any player with a number greater than 9 in its name.

Why do you need to include the spaces before the digits in the formula?

how can I obtain a List (LineUp::name) that would include only the players whose CA (Yellow Cards) field is 1.

I'll then apply the same logic to goals and sent-offs.

Perhaps it would be better to use a custom function. Otherwise you will need a calculation field in the LineUp table for each category, e.g.

Case ( CA ; Name )

and a corresponding List() calc in the Matches table.

[Cazito]

If I eliminate the space from the formula I will have a space after each players name (the ones that originally had a number after the name) in the final cLineUp field.

Thanks, used cYellowCardsList = Case (CA=1; Name) in the LineUp table and List (LineUP::cYellowCardsList) in the Match table and it worked fine. Was trying to create a single field in the Match table and it wasn't working.

All the best.

[comment]

If I eliminate the space from the formula I will have a space after each players name

That's what the Trim() function is for.

[Cazito]

Will try that. Thanks again.

[Cazito]

Hi.

I created a calculation field called GoalScorersList in the Match table, to get a list of the goal scorers of each match, that's defined as follows:

Let ( [

GoalScorers = List (LineUp::GoalScorer);

CountGoalScorers = ValueCount (GoalScorers) ] ;

Case ( CountGoalScorers=1 and GoalsFor > 1 ; "Goals: " & Substitute (RightValues ( GoalScorers; 1 ) & "¶" ; [ "¶¶" ; "" ] );

CountGoalScorers=1 ; "Goal: " & Substitute (RightValues ( GoalScorers ; 1 ) & "¶" ; [ "¶¶" ; "" ] );

CountGoalScorers=2 ; "Goals: " & Substitute ( LeftValues ( GoalScorers ; 1 ) & RightValues ( GoalScorers ; 1 ) & "¶" ; [ "¶¶" ; "" ] ; [ "¶" ; " and " ] );

CountGoalScorers>2 ; "Goals: " & Substitute ( LeftValues ( GoalScorers ; CountGoalScorers - 2) & MiddleValues ( GoalScorers ; CountGoalScorers - 1 ; 1 ) & "+" & RightValues ( GoalScorers ; 1 ) & "¶" ; [ "¶¶" ; "" ] ; [ "¶+" ; " and " ] ; [ "¶" ; ", " ] ) ) )

where the GoalScorer field is a text field in the LineUp table withe name of the player with the time he scored the goal in parenthesis

and the GoalsFor field in the Match table is the number of goals the team scored in each match.

It's working fine and a typical result I can get would be like this:

Goals: PlayerA (67), PlayerB (39), PlayerC (47 and 62p) and PlayerD (90+1)

obs.: At this point I don't really care for who scored the goals against my team so I only list my team's goals.

Although the above list is alright what I really want is to sort the list by the time the first goal of each player was scored and not by the players order in the LineUp table so that it would look like this:

Goals: PlayerB (39), PlayerC (47 and 62p), PlayerA (67) and PlayerD (90+1)

Does anyone know how can I achieve this?

I tried sorting the LineUp table by the MatchID and Time fields concurrently and it did put the GoalScorer field in the order I wanted but nothing changed in the GoalScorersList from the Match table.

Do I need a script for this?

I'm using this GoalScorersList field in only one report layout called MatchDetails and If I do need to sort the LineUp table I would like to revert the sort order when I leave this report.

obs: The MatchDetails report gives me a list with all details of each match (see example below in portuguese) that's what I'm really trying to achieve here.

Jogo 5137

04.ago.2011

2 x 0 INTERNACIONAL (RS)

Competição: Campeonato Brasileiro

Local: Estádio Engenhão

Cidade: Rio de Janeiro (RJ)

Árbitro: Nielson Nogueira Dias (PE)

Renda: R$ 155.385,00

Público: 4.637

Fluminense: Diego Cavalieri, Mariano, Gum, Márcio Rosário e Carlinhos; Edinho, Diguinho (Valencia), Souza e Marquinho (Fernando Bob); Rafael Sóbis (Ciro) e Rafael Moura.

Técnico: Abel Braga

Gols: Souza (56) e Rafael Moura (87p)

Any insight will be much appreciated. Thanks in advance.

[comment]

I tried sorting the LineUp table

The List() function follows the sort order of the relationship.

the GoalScorer field is a text field in the LineUp table withe name of the player with the time he scored the goal in parenthesis

IMHO, you should have a table where each goal is an individual record.

[Cazito]

The List() function follows the sort order of the relationship.

Then a new Table Ocurrance of the LineUp table might solve my problem.

IMHO, you should have a table where each goal is an individual record.

Will try that.

Thanks again.