5 Replies Latest reply on Oct 20, 2014 11:10 AM by rwforce

    Field Calculation of the previous record

    rwforce

      I have a database that tracks movement of set pieces on a Broadway-style show. In each cue, set pieces move at a certain speed to a target position. I would like to add a calculation field that determins the starting position of the set piece based on the target position from the last time we moved that set piece. Does anyone know how to do that? Thanks.

        • 1. Re: Field Calculation of the previous record
          erolst

          You can use a self-join for this (or at least some other technique, but let's use this one…)

           

          <speculation>If you have a Movements table with (at least) fields for pieceID, cue#, and of course the position</speculation> …

           

          Create a new TO as Movements_previousOfPiece.

           

          Define a new relationship as

          Movements::pieceID = Movements_previousOfPiece::pieceID

          Movements::cue# > Movements_previousOfPiece::cue#.

           

          Sort Movements_previousOfPiece by cue#, descending.

           

          This gives you access to the fields and data from one, several or all previous record(s) of the respective piece, in terms of cue#, and lets you perform all kinds of arithmetics with them.

           

          If you can provide some sample data and the expected results, we can suggest something more specific.

          1 of 1 people found this helpful
          • 2. Re: Field Calculation of the previous record
            rwforce

            I'm don't really follow. 

             

            What is "TO"?

             

            Would this solution be live updating?

             

            Thanks

            • 3. Re: Field Calculation of the previous record
              erolst

              A TO is a table occurrence; an instance of a table that you use to create relationships – which you do in the Relationship Graph.

               

              Did it really take you 9 months to realize that you need more info?

               

              rwforce wrote:

              Would this solution be live updating?

               

              Whatever you calculate via this relationship would par force be unstored, so – yes.

              • 4. Re: Field Calculation of the previous record
                taylorsharpe

                You can do table occurrence joins like mentioned above.  But something like this works well with SQL too where you do a search the piece ID for the previous cue.  Are cue number just in numerical order?  If so you could do something like

                 

                SQL = "SELECT LOCATION FROM TableOccurrence WHERE CUE = ? ORDER BY CueNo DESC FETCH FIRST 1 ROWS ONLY"

                 

                ExecuteSQL ( SQL ; ¶ ; ¶ ; CurrentCue - 1 )

                1 of 1 people found this helpful
                • 5. Re: Field Calculation of the previous record
                  rwforce

                  Thanks.  I did get it working useing the solution from erolst.