AnsweredAssumed Answered

Can not figure out the formula

Question asked by AdamRoberts on May 7, 2014
Latest reply on May 7, 2014 by philmodjunk


Can not figure out the formula


     I am new to filemaker so might be looking at this the wrong way, but it has me stumped. I am fine with doing this in excel, but can't work it out in filemaker 

     I have Four tables 

     Programs                                ClientIDFK          Exe 1(List from EXNAME), Exe1RM,  Exe2(List from EXNAME), Exe2RM
     Clients                                    ClientIDPK 
     Exercises                               EXNAME, Relationship, Percent 
     Testing                                  ClientIDFK  Squat , Bench 

     For the Programs Table / Form 

     When I select ClientIDFK, then Exe1 (from pull down) I want it to Generate Exe1RM


PROGAM TABLE                                    EXERCISE TABLE 
     ClientIDFK    12                                       EXNAME = Front Squat         Testing  CientIDFK = 12    
     Exe 1          =  Front Squat                       Relationship = Squat                           Squat = 100                                                                                                                                                                                                                          .                                                                                                    Percent = 0.8                                       Bench = 120


     Exe1RM will see "Front Squat" in the PROGRAM TABLE, "Exe 1", then check the EXERCISE TABLE , locate "Front Squat" and go to the next column "Relationship" and see it relates to "Squat", then find the clients Squat 1RM by going to TESTING TABLE ,finding the "ClientIDFK" and moving to the "Squat" column,then as it knowns its a front squat ,divide that number by percent found in the next column "Percent" on the "Front squat" row in the EXERCISE TABLE  =  0.8, so in Exe1RM there would be 80 

     Normally I use Lookup formulas in Excel, but my attempts in filemaker so far have come up well short, appreciate any input