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

    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.