13 Replies Latest reply on Mar 23, 2014 3:28 PM by JohnAbenaim

How to calculate the field value difference from and the prior record.

Title

How to calculate the field value difference from and the prior record.

Post

Hi.  using Filemaker pro 10.

I'd like to add to a report a field that is the difference in a field value from one record and the prior record.

Example:

File    Difference

1

2           0

5           2

6           0

10         3

Difference is the field I need to create.

Is this possible as a field calculation?

• 1. Re: How to calculate the field value difference from and the prior record.

From the description, I would think the desired values you want would be:

File    Difference

1

2           1 (2 -1)

5           3 (5 -2 )

6           1 (6 -5 )

10         4 (10 - 4 )

In any case, GetNthRecord(fieldName;Get ( RecordNumber ) - 1 )

Can refer to a field in the previous record--provided you have sorted your records so that the desired record truly is the previous record.

• 2. Re: How to calculate the field value difference from and the prior record.

Thanks.  that is giving me odd results that don't make alot of sense.

The desired values are

File   Diff

1

2        0

5        2

6        0

10      3

numbers aren't consistent but the previous is always greater in value.  Could be 24, 48, 1, or 0 missing between file numbers.

I appreciate the effort.   I'll keep trying.  Any other suggestions or references greatly appreciated.

• 3. Re: How to calculate the field value difference from and the prior record.

your example is a little unclear...

can you give a more detailed example with the actual values and field names?

and as Phil touched on: are these records always sorted a particular way?

• 4. Re: How to calculate the field value difference from and the prior record.

GetNthRecord should work for you. How are you getting the values 0, 2, 0, 3?

They aren't strictly the "difference" of the current record minus the previous or they would be larger by one.

This expression should work in an unstored calculation field:

Let ( N = Get ( RecordNumber ) ; If ( N > 1 ;  File - GetNthRecord (File ; N - 1 ) - 1 ) ; "" )

This assumes that you have your records sorted so that they will appear in a list or table view layout sorted in the order you've posted (Ascending by File ).

Edit note: edited expression to fix an error.

• 5. Re: How to calculate the field value difference from and the prior record.

Try:

```Let (
n = Get ( RecordNumber )
;
Case ( n > 1 ;
File - GetNthRecord ( File ; n - 1 ) + 1
)```

• 6. Re: How to calculate the field value difference from and the prior record.

Thank you again..   The results were a bit odd.

example:

 2 "7" 3 "24" 1 4 "30" -5 5 "42" -14 6 "57" -38 7 "63" -56 8 "64" -71 9 "78" -82 10 "84" -97

Its the second column that  I need to calculate and have the results in the 3rd column.   Should be .....

 2 "7" 3 "24" 16 4 "30" 5 5 "42" 11 6 "57" 14 7 "63" 5 8 "64" 0 9 "78" 13 10 "84" 5

Any other ideas are greatly appreciated !

• 8. Re: How to calculate the field value difference from and the prior record.

Note: there's a typo in my last post. The calculation should read: Let ( N = Get ( RecordNumber ) ; If ( N > 1 ;  Value - GetNthRecord ( Value ; N - 1 ) - 1  ; "" ) )

• 9. Re: How to calculate the field value difference from and the prior record.

Hello.    I was replying to whomever was interested..

I was using a field from a different table and it wasn't working properly.

(Something I need to study)

Greatly appreciated!

• 10. Re: How to calculate the field value difference from and the prior record.

Hello,

Is it possible to make this calculation work between a record and the one previous to it within a found set instead of within the entire table as a whole?

• 11. Re: How to calculate the field value difference from and the prior record.

GetNthRecord references either another record in the found set (what you requested) or the Nth record linked to the current record via a relationship.

PS. these older posts no longer float to the top of Recent Issues when you post a message to them. This makes them easy to miss. you may want to open a new thread and include a link to this thread if you want to make it easy for others to read back to the beginning post that started the discussion.

• 12. Re: How to calculate the field value difference from and the prior record.

I have used the calculation you specify above in a layout, but it is referencing the previous record in the table as a whole, instead of the previous record in the found set that I have generated before triggering the script for the layout. Am I missing a step?

• 13. Re: How to calculate the field value difference from and the prior record.

Hey Phil,

I have a similar project that is as follow:

I want to find out the difference between the current record and previous one to calculate the net balance so that i can charge the correct amount to clients.

My database has 2 tables: Client & Transaction

1. Client

1.           Client ID (Auto Entered Serial)
2.
3.           Client Name (Text)

2. Transaction

1.           Date (date field)
2.
3.           Client ID (Number)
4.
5.           Current Balance prints (Number)  (amount printed since client was added on the printer)
6.
7.           Lookup prints (calculation)  = Let ( N = Get ( RecordNumber ) ; If ( N > 1 ; Balance of BW - GetNthRecord (Balance of BW ; N - 1 )  ; "" ) )
8.
9.           Net Prints (calculation) = Balance of BW - Lookup Total BW
10.
11.           Prints Charge (calculation) = (Net Prints * .10)

I setup 1 relationship being Client ID between the two tables.

The problem is that for the the 2nd record i get nothing for lookup field.

Any thought?