Calculate Field using data from the next record

564
3
Jump to solution
10-18-2012 06:31 AM
BradCabe
New Contributor
I am having trouble trying to figure out how to use the calculate field function to calculate a field based on the next record. I have a lines database that are organized based on a particular ID field (MMSI_Date, a text field). For example, 1000 lines have MMSI_Date ABC, 1100 lines have MMSI_Date DEF, etc. The problem is that the last record in each MMSI_Date group should be associated with the next group. Its a problem with the tool I used to generate this data. So this is what I have tried to do:

Pre-Logic
Static lastTid as variant Dim newVal as Short  If (lastTid = [MMSI_Date]) Then    newVal = 1 Else    newVal = 0 End If lastTid = [MMSI_Date]


Expression
lastTid


Needless to say, this isn't working. In fact, I am getting a general 999999 Error. Note: I have some beginner-level experience with VB in field calculations, but have never done anything at this level.

Any help or insight are appreciated.

Thanks!
0 Kudos
1 Solution

Accepted Solutions
BradCabe
New Contributor
derklauer, thanks, that helped a lot.

Wayne, I actually figured out a way to kind of fudge it enough for my purposes. I used the Sort tool to reverse sort on a copy of the
objectID field. Then I used my little script (modified with help from the doc derklauer provided). Works like a charm and can probably be all thrown together in one python script.

Thanks much!

View solution in original post

0 Kudos
3 Replies
DanielErklauer
New Contributor III
I am having trouble trying to figure out how to use the calculate field function to calculate a field based on the next record. I have a lines database that are organized based on a particular ID field (MMSI_Date, a text field). For example, 1000 lines have MMSI_Date ABC, 1100 lines have MMSI_Date DEF, etc. The problem is that the last record in each MMSI_Date group should be associated with the next group. Its a problem with the tool I used to generate this data. So this is what I have tried to do:

Pre-Logic
Static lastTid as variant
Dim newVal as Short

If (lastTid = [MMSI_Date]) Then
   newVal = 1
Else
   newVal = 0
End If
lastTid = [MMSI_Date]


Expression
lastTid


Needless to say, this isn't working. In fact, I am getting a general 999999 Error. Note: I have some beginner-level experience with VB in field calculations, but have never done anything at this level.

Any help or insight are appreciated.

Thanks!



Here is a good field calculator reference that will tell you how to do what you are asking.  Source

http://nrm.salrm.uaf.edu/~dverbyla/nrm638/
0 Kudos
T__WayneWhitley
Frequent Contributor
Can you attach a sample of your data?...and a better description of the calculation you intend to perform?

Based on your initial description, it doesn't sound possible because that isn't the way the field calculator works - however, it may be possible to 're-sort' your table or read the values in memory and perform the calculation via Python.  Also, are you open to a script solution instead?
0 Kudos
BradCabe
New Contributor
derklauer, thanks, that helped a lot.

Wayne, I actually figured out a way to kind of fudge it enough for my purposes. I used the Sort tool to reverse sort on a copy of the
objectID field. Then I used my little script (modified with help from the doc derklauer provided). Works like a charm and can probably be all thrown together in one python script.

Thanks much!
0 Kudos