Hi, everyone! Greetings form Brazil.

    I have this database that keeps track of all games of a basketball team. I have a table called GAME that has info like date, opponent’s name, score (for and against), venue, etc... and a table called LINEUP that have individual statistics (pts, rebounds, assists, etc...) for each player for each game (I only keep statistics of the team I support as I’m not interested on opponent’s data).

   I recently added a field to the GAME tabel called “Type of Game” which has a drop down list with two options: competitive or friendly. The GAME table links to the LINEUP table in a one to many relationship thru the “Date” field. I also have a PLAYER table (with info like full name, birth date, photo, height, etc...) that links to the LINEUP table thru the “Name” field.

    I created a report that counts the total number of games each player has accumulated. I achieved this by creating a calculation field on the PLAYER table called “Total games” whose formula is “Count ( Lineup::Name )”.

    So now I want to change this report so that it would count the total number of competitive games for each player as opposed to counting both competitive and friendly games which is what it is doing right now. Can anyone please tell me how can I achieve this? Thanks in advance.
 

10 hours ago, Nowhere Man said:

count the total number of competitive games for each player

If I follow your description correctly, you could do:

ValueCount ( FilterValues ( List ( GAME::Type of Game ) ; "competitive" ) )

This could be simplified by using a Boolean field (a Number field containing 1 when the game is competitive, zero or empty otherwise) instead of a Text field with two mutually exclusive values. Then you could do just: 

Count ( GAME::IsCompetitive )

if using empty values to signify friendly games, or:

Sum ( GAME::IsCompetitive )

if using zeros and/or empty.

--
Unrelated to your question: using a date as the match field between GAME  and LINEUP precludes the possibility of having two (or more) games on the same day. It is good practice to use a meaningless GameID instead. Similarly, use PlayerID for the other relationship, because eventually you can have two players with the same name.

 

Thanks for this, I tried the first option and it worked beautifully.

I understand the double name, double date issue. As I don't have two games played on the same date that shouldn't be a problem. As for players with the same name I use a number after the name to differentiate them, but I understand your suggestion is a better practice.

Best regards.

Hello there. Me again.

I have a report that adds up the total number of points scored by a player thru all his games. It’s simple enough, I use a calculating field called “Points_Total” on the PLAYER table whose formula is 

Sum ( LINEUP::Points )

I’m now trying to get the total number of points scored by a player only on competitive games as opposed to ALL games.

I tried 

Sum ( IF ( GAME::Type of Game = "Competitive" ; LINEUP::Points ; 0 ) ).

But I’m getting some odd results.

Can someone help me solving this?

Filemaker does not have a SUMIF() function like Excel does. You could construct a recursive calculation that loops over the related records - but for this you would need either the Advanced version to install a custom function or version 18+ to use the While() function. Without these you will need to do it in 2 steps: add a calculation field to the LINEUP table =

If ( GAME::Type of Game = "Competitive" ; Points )

and then sum this field using your existing calculation field in the PLAYER table.

Note that the traditional Filemaker method is to use a report produced from the LINEUP table, with a summary field totaling the player's points. Then you can either find only records that relate to "competitive" games, or sub-sort the records by game type to produce separate sub-summaries for each type.

 

Thanks again. It worked like a charm.

Best regards.