10 Replies Latest reply on Sep 17, 2015 1:46 PM by echosteve

    alculation using data from the current record and the previous record

    echosteve

      I am trying to do a calculation using data from the current record and the previous record but I am unsure of how to set it up.  The formula I would use is (Pressure from current record - Pressure from previous record)/(Depth from current record - Depth from previous record).  Any help would be appreciated.

        • 1. Re: alculation using data from the current record and the previous record
          Mike_Mitchell

          The GetNthRecord function, in combination with Get ( RecordNumber ), can perform this function. However, such a setup is inherently wobbly, because it's entirely dependent on the sort order. Exactly what do we mean by "previous record"? Previous in what sense? Order of entry? Some date or time field? Current sort order?

          • 2. Re: alculation using data from the current record and the previous record
            echosteve

            Mike,

             

             

             

            The records would be sorted by date and time and then the calculation of (casing pressure (current record) – casing pressure (previous record))/depth to fluid (current record)- depth to fluid (previous record).

             

            I am new to filemaker so the more detail you could provide would be helpful.\

             

            Steve

            • 3. Re: alculation using data from the current record and the previous record
              rrrichie

              You can also use the insert from last visited, which works like the command-" command.

               

              Check out the FileMaker help for instructions.

               

              Happy Coding

              • 4. Re: alculation using data from the current record and the previous record
                Mike_Mitchell

                You might be better served to set up a self-joining relationship for this, sorted by date / time (descending) and excluding the current record. (In other words, your relationship criterion is recordID <> current recordID.) Then the "previous" record is simply the first related record.

                 

                However, such a setup is vulnerable to performance problems if the number of records becomes large. (Sorting large numbers of records creates problems, especially over a WAN.) A similar problem exists if you try to use GetNthRecord (which would be an unstored calculation in order for it to evaluate properly as you add new records).

                 

                Therefore, you might have better luck using a scripted approach and storing the calculation using a Script Trigger. You could set up the script to perform the calculation and insert the value into the appropriate field. That way, you pay the calculation overhead just once for each record (at the point when the result is inserted).

                 

                Now, all that background to say, the calculation itself isn't that difficult. The methodology associated with the script would depend on whether you wanted to set up the relationship or not. Let's go with "not" just for this example, and, since you're new to FileMaker, we'll use simpler methods that do have some drawbacks, but are easier to understand. The script might look something like this:

                 

                Sort Records [ Date / Time Ascending ]

                If [ Get ( RecordNumber ) > 1 ]

                     Go to Record / Request / Page [ Previous ]

                     Set Variable [ $prevCasingPressure ; table::CasingPressure ]

                     Set Variable [ $prevDepthToFluid ; table::DepthToFluid ]

                     Go to Record / Request / Page [ Next ]

                     Set Field [ table::FieldForResult ; ( table::CasingPressure - $prevCasingPressure ) / ( table::DepthToFluid - $prevDepthToFluid )

                End If

                 

                You would set this script to run using the OnObjectSave trigger on either the Casing Pressure or Depth to Fluid field. That way, whenever either of those fields is changed, the corresponding calculation is updated. You get to the Script Triggers dialog by right-clicking on the field in Layout mode.

                 

                Why do I have the If statement in there? Because if you're on the first record, there is no previous record, so you don't want the script to do anything in that case. (If you do want it to set a certain value, you can add an Else clause after the Set Field step.)

                 

                Now, what do I mean by this method having some drawbacks? Whenever you leave the current record to go to another record, FileMaker automatically commits the current record. That can, in cases, have negative consequences for workflow. Another problem with this simplified method is that flipping back and forth between records while on a given layout can sometimes cause screen flicker, which is a somewhat unpleasant user experience. But these are refinements you can work with as you gain experience.

                 

                HTH

                 

                Mike

                • 5. Re: alculation using data from the current record and the previous record
                  echosteve

                  Mike you will have to excuse my ignorance but how do you enter the correct information in the Set Variable and Set Field steps.  Do I enter it in name or value?  I am have trouble replicating your suggestion.

                  9-17-2015 11-46-26 AM.jpg

                  • 6. Re: alculation using data from the current record and the previous record
                    siplus

                    Independently of sort order and found set, if your records have an incremental serial number, you can use executeSQL to get values from the record having ID = currentID - 1, then do your math.

                    • 7. Re: alculation using data from the current record and the previous record
                      Mike_Mitchell

                      In the FileMaker environment, the semicolon ( ; ) is used to separate parameters. The parameter before the semicolon in both Set Variable and Set Field is the name of the object (the variable or field, respectively). The value after the semicolon is the value. You need both.

                      • 8. Re: alculation using data from the current record and the previous record
                        echosteve

                        Mike,

                         

                        When I try to enter

                         

                        prevCasing Pressure ; Fluid Levels::Casing Pressure

                         

                        I continually get error messages.  I cannot enter it as a name or value.  Not sure what I am doing wrong.

                        • 9. Re: alculation using data from the current record and the previous record
                          Mike_Mitchell

                          Enter the part on the left in the name, and the part on the right in the value.

                           

                          dialog.png

                          • 10. Re: alculation using data from the current record and the previous record
                            echosteve

                            Worked like a charm Mike.  Thanks for all of your help.