1 Reply Latest reply on Mar 8, 2012 10:01 AM by philmodjunk

    Lookup value based off previous record



      Lookup value based off previous record


      I have used the invoice starter template and modified it to work with my gym in monitoring workouts. Essentially Products have become Exercises and Invoices have become Workouts. I've added a couple of fields to the Exercises (formally Products) 1RM and Previous 1RM. 1RM is a calculated value, I've got this working great but I'm trying to get Previous 1RM to show the calculated value from the last time the customer did the exercise.

      It would be similar to pulling the Quantity for a Product on a New invoice from the previous time the Customer bought that product.

      Thanks in advance.


        • 1. Re: Lookup value based off previous record

          And the previous "purchase" of that product was on a different "invoice"? Do you still have a LineItems table?

          Like This?


          You'll have to use your table name for the join (lineitems) table in place of mine...

          If you add a self join relationship on WorkOut_Excercise, you can match by ExcersizeID and a ClientID field to match to all previous "purchases" of that specific exercisze by that specific client. Then it's simply a matter of linking to the "most recent" instance of that record.

          Add a field to WorkOut_Excersize that auto-enters the ClientID from WorkOuts or Clients. (Do not use a calculation field as it will be unstored and that doesn't work for this purpose.)

          In Manage | Database | relationships, make a new table occurrence of WorkOut_Excersize by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as WorkOut_ExcersizeSameClientExcersize.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add it to your relationships like this:


          WorkOut_Excersize::__pk_WorkOut_ExcersizeID ≠ WorkOut_ExcersizeSameClientExcersize::__pk_WorkOut_ExcersizeID AND
          WorkOut_Excersize::_fk_ExerciseID = WorkOut_ExcersizeSameClientExcersize::_fk_ExerciseID AND
          WorkOut_Excersize::_fk_ClientID = WorkOut_ExcersizeSameClientExcersize::_fk_ClientID

          Specify a sort order for this relationship that sorts the records by __pk_WorkOut_ExcersizeID in descending order.

          Then you can use a looked up value field option to copy from a field WorkOut_ExcersizeSameClientExcersize to get the data from the last time the client performed that specific excersize.