4 Replies Latest reply on Mar 6, 2012 9:24 AM by JasonPenner

    Using past records to find a calculation

    JasonPenner

      Title

      Using past records to find a calculation

      Post

      Good morning!

      I will be the first to tell you that I'm "green" with the whole filemaker thing, but I have managed to get a database working for my company but now I want to do more.

      I own a gym and we are in the process of deploying our Quality Control Database (makes sure people are training the way they should train), I have set it up using the Invoice Sample Database (where Products became exercises and Invoices became Workouts) I added the fields I needed etc. But now I'm trying to add a testing component to it;

      What I need to do is;

      • Add tests as we grow
      • Calculate Values from certain tests
      • Spit them out into a report that compares their previous test with the current one
      And example of what I'm talking about (and I excuse the rambling but I'm not very comfortable with the lingo just yet). I have it set it up like an invoice as well, so Testable Exercises is similar to products and then an invoice (Test) with Line Items. So I would add Body weight and Body Fat, now I need to have two calculated values from this;
      • Lean Body Mass; (1-Body Fat)*Body Weight
      • Fat Mass; Body Fat*Body Weight
      The problem I get into is how do set up the calculated records? Does "Lean Body Mass" and "Fat Mass" become it's own record?
      Any input is greatly appreciated as I am still wrapping my head around this whole database thing. 
      Cheers,
      Jason

      Screen_shot_2012-03-06_at_6.54.02_AM.png

        • 1. Re: Using past records to find a calculation
          philmodjunk

          The simplest approach (not necessarily the best) is to define Body Weight, body fat and the calculations as seperate fields in the same record. That can be set up as a row arrange like this:

          Body Weight    |    Body Fat   | Lean Body Mass    |   Fat Mass

          I would assume that the difficulty here is that you plan on multiple tests each with different data and calculations and you want to record different measurements all in the same table. Would that be the issue or does a single record as shown above work for you?

          • 2. Re: Using past records to find a calculation
            JasonPenner

            I was thinking about doing it that way but each test has a different formula for example. One that I would like to get is "One Rep Max" that would like this;

            Bench Press Weight | Bench Press Reps | Bench One Rep max (formula; Weight/(1-(0.0278*Reps))

            How do I account for different formulas? Can I have a field as the formula its self such as;

            Field 1: Bench Press Reps | Value

            Field 2: Bench Press Weight | Value

            Field 3: Bench One Rep Max | Calculation; [Field 1] / (1-(0.0278 * [ Field 2] )

            • 3. Re: Using past records to find a calculation
              philmodjunk

              That's the direction in which I was heading.

              I'd use two tables, Data, CalculationDetails

              In the Data table of this record, define some fields: Field1Data (number), Field2Data etc. Define enough fields so that you can enter data for the most complicated calculation of those you will use for your tests. (you can easily add more field pairs after the fact if you need to.

              Add a test name field you can populate with a dropdown list or pop up menu of tests.

              In calculation details define a field: TestName of type text. Also add text fields for to serve as label fields for each of the data fields in the first table and each of the calculations to be performed with this data. In our first example, One such text field would store "Body Weight", another: "Body Fat" and a third: "Lean Body Mass".

              Relate them by TestName. In your portal you can combine the Test name field with the data fields from your first name and the "label" fields from the second.

              Now for the calculations: In CalculationDetails, add a text fields for calculations. In your first example, you have two calculations to be perfromed on the same set of data so you need at least two such fields. If other test do more calcs with the same data set, you'd need more.

              Enter your calculation formulas into these text fields. For the Lean Body Mass calculation, you'd enter this text into a field named "Calc1Formula":

              (1-Field1data)*Field2data

              In your Data table, define a calculation field as Evaluate ( calculationdetails::Calc1Formula )

              This way, you select at test by name, enter data into fields that automatically label themselves with the correct labels and your auto-labeled calculation fields then compute the needed results from the entered data.

              • 4. Re: Using past records to find a calculation
                JasonPenner

                Thanks I will give that a go as soon as I've had a full night of sleep... got a headache just reading that! Thanks again...