From the description, I would think the desired values you want would be:
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.
Thanks. that is giving me odd results that don't make alot of sense.
The desired values are
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.
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?
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.
Let ( n = Get ( RecordNumber ) ; Case ( n > 1 ; File - GetNthRecord ( File ; n - 1 ) + 1 )
Thank you again.. The results were a bit odd.
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 !
Who are you replying to?
Download this demo and compare: http://www.4shared.com/file/_acIPv7v/NthRecordDifference.html
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 ; "" ) )
Hello. I was replying to whomever was interested..
The calculation is now working. Thank you for the download.
I was using a field from a different table and it wasn't working properly.
(Something I need to study)
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?
Thank you for your help!
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.
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?
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
- Client ID (Auto Entered Serial)
- Client Name (Text)
- Date (date field)
- Client ID (Number)
- Current Balance prints (Number) (amount printed since client was added on the printer)
- Lookup prints (calculation) = Let ( N = Get ( RecordNumber ) ; If ( N > 1 ; Balance of BW - GetNthRecord (Balance of BW ; N - 1 ) ; "" ) )
- Net Prints (calculation) = Balance of BW - Lookup Total BW
- 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.