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

# alculation using data from the current record and the previous record

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

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

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

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

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

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.

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

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

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

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

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

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

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