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

# UUGHHH TRANSITIONING

### Title

UUGHHH TRANSITIONING

### Post

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

Reps

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?

• ###### 1. Re: UUGHHH TRANSITIONING

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.

• ###### 2. Re: UUGHHH TRANSITIONING

Phil,

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.