AnsweredAssumed Answered

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

Question asked by SifisoV on Jan 10, 2014
Latest reply on Jan 14, 2014 by SifisoV

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.

     Please help.

     Thanks in advance.

Outcomes