2 Replies Latest reply on Aug 28, 2013 12:50 PM by SamuelWinkler






           OK so i am new to the File Maker format.  I am a Drill Sergeant and I have privates take a PT Test and what I want to do is create fields that compare age and gender and compare them with number of repetitions of push ups.  I am trying to figure out how to take to pieces of data as though it was a spread sheet and find the value of where the two meet.  I may just be using the wrong logic thought process.  

                                                               MALES AGE                                           FEMALES AGE



                          17                     22                     27                     32                     17                     22                     27                     32
                          55                     72                     73                     75                     76                     90                     91                     92                     93
                          56                     73                     74                     76                     77                     91                     92                     93                     94
                          57                     74                     75                     77                     78                     92                     93                     94                     95
                          58                     75                     76                     78                     79                     93                     94                     95                     96
                          59                     76                     77                     79                     80                     94                     95                     96                     97

           If the record for PVT JOE SNUFF  reads

           AGE: 23  GENDER: MALE  and REPS: 57   so I want the Points Field to return a value of 75.

           and if its 

           AGE: 34  GENDER: FEMALE  and REPS: 58   so I want the Points Field to return a value of 96.

           IF this was EXCEL I would use Index(REPS, A1:A100, Match(AGE, B1:I1,0) or something to that effect.

           Can anyone help me?


                    IF this was EXCEL

               That's the thinking that can sometimes get you in trouble in FileMaker. It's NOT Excel and thus does things differently.

               If I understand you correctly, you want to use the data shown as a look up table where you enter a recruit's age, reps and gender and then a score is returned.

               I recommend that you resructure your table to have just the following columns:

               Gender | Age | Reps | Score

               Then you can define a relationship where you match your recruit Data to this Scores table:

               RecruitData::Age > Score::Age AND
               RecruitData::Gender = Score::Gender AND
               RecruitData::Reps = Score::Reps

               In Manage | Database | Relationships, specfiy a sort order for this relationship that sorts the Scores table by Age in Descending order. That way, your relationship can return the score for matching gender and reps for the largest age value that is less than or equal to the recruit's age.



                     Thanks I appreciate that... It makes sence and I then had a Slap forehead moment. I am working to make a 200 sheet wookbook function better... and become more user friendly and cross platform capable so thanks for the assistance.