1 Reply Latest reply on May 7, 2014 7:18 AM by philmodjunk

    Can not figure out the formula

    AdamRoberts

      Title

      Can not figure out the formula

      Post

           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

           Example 

      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 

                    

            

            

        • 1. Re: Can not figure out the formula
          philmodjunk

               You appear to need these relationships:

               Testing>------Clients-----<Programs>------Exercises

               Client::ClientIDPK = Programs::ClientIDFK

               But you also appear to need a single field (not two different fields) in Testing used to identify the specific excersize type (Bench or Squat). I'll Call it ExType.

               Client::ClientIDPK =Testing::ClientIDFK AND
               Client::ExType = Testing::ExType

               Excersizes::ExName = Programs::ExName

               Client::ExType can be set to look up a value from Relationships if that is a text field that stores the correct value to use to match to testing records by ExType.