2 Replies Latest reply on Jan 14, 2014 5:17 AM by SifisoV

# If statement condition not working as expected when creating a summary Table

### Title

If statement condition not working as expected when creating a summary Table

### Post

Good day,

Can someone please help me with this. I am working on a short quiz game. I am having trouble viewing a summary of all the attempts. I am looking for several approaches for this challenge.

I have two tables. Table 1 records a player’s quiz attempts (Players proceed’s to the next level if score is equal or above 50). For each attempt it makes a new record. For example let's say this is my table in filemaker Pro 12

``````Table Name(PLAYER_ATTEMPTS_TB)
Attempts_ID >>Player_ID >> Player_Name >> Game_Level    >> Score
1         Player001 >> John Smith  >> Level 1   >> 30
2         Player001 >> John Smith  >> Level 1   >> 40
3         Player001 >> John Smith  >> Level 1   >> 45
4         Player001 >> John Smith  >> Level 1   >> 55
5         Player001 >> John Smith  >> Level 2   >> 26
6         Player001 >> John Smith  >> Level 2   >> 40
7         Player001 >> John Smith  >> Level 2   >> 45
8         Player001 >> John Smith  >> Level 2   >> 60
9         Player001 >> John Smith  >> Level 3   >> 30
10        Player001 >> John Smith  >> Level 3   >> 40
11        Player001 >> John Smith  >> Level 3   >> 48
12        Player001 >> John Smith  >> Level 3   >> 70
etc…
``````

Attempts_ID is the Primary Key

So what I’m tryin to do is create a summary table that shows all the attempts that are equal or above 50 for each player.

So on the table below I show the summary for all of the levels.

``````Table Name(PLAYER_SUMMARY_TB)
Summary_ID >>Player_ID >> Player_Name >> Level1_Score >> Level2_Score >> Level3_Score
1        Player001 >> John Smith  >> 55  >> 60 >> 70
2        Player002 >> Simon Cowell>> 55  >> 60 >> 70
etc…
``````

Summary_ID is the Primary Key

The two tables have a relationship, Linked by the Player_ID field. In the “PLAYER_SUMMARY_TB” Table, I have made the score fields Calculation type and I have entered the following:

``````If( PLAYER_ATTEMPTS_TB::Game_Level = Level 1 ; Max(PLAYER_ATTEMPTS_TB:: Score))
``````

This shows me the correct results with the above if statement but as soon as I change the condition to “Level 2”, I do not get any results back. I am not sure what I’m doing wrong? The two tables are linked using the Player_ID. Player_ID is the Primary key on the PLAYER_SUMMARY_TB and a foreign key on the PLAYER_ATTEMPTS_TB.

• ###### 1. Re: If statement condition not working as expected when creating a summary Table

When you use a reference to a field to another, related table such as

PLAYER_ATTEMPTS_TB::Game_Level

Your calculation is only referring to data from the first such related record. Data from any other related record is ignored. The only exception to this is when you are using this as the parameter to an aggregate function such as the Max function.

But producing a list of summary results with one row of data for each group of records being summarized does not necessarily require an additional table. This can also be produced with a sub Summary part in a summary report layout that does not have a body layout part.

See: Creating Filemaker Pro summary reports--Tutorial for a tutorial on summary reports.

• ###### 2. Re: If statement condition not working as expected when creating a summary Table

Thanks Phil.