How to Get Next Record Value by IDEA Script?

Hi Foster,

You can try out this code.  I broke it down into two sections, the first creates the field, you will notice that I am creating an IDEA field (WI_NUM_FIELD), the reason being is that the code to update fields doesn't seem to work on anything but IDEA fields, if you try it I think you get an error or something, took me a while of head banging before I figured that out.

The second function does the update.  I use the rs.GetAt function to get the different records, put the values in a variable and then compare them.  I do the previous one first so that when I add on the information to the new field it gest added to the current one.  I also added some code so that if we are looking at the first record it defaults to 1.  Now I haven't tested the script much, the example file I used was limited so you might get some errors if so let me know what they might be. 

As for not being able to use the iGetNextValue, this is a new function within IDEA and for what ever reason IDEA decided not to implement the new equation functions in IDEAScript.

Good luck.

Brian

Option Explicit

Sub Main

	Call createField()
	Call updateField()
	
End Sub

Function createField()
	Dim db As database
	Dim NewField As Object
	Dim ThisTable As table
	Dim MyField As field
	
	Dim currentValue As Integer
	Dim nextValue As Integer
	
	Set db = Client.OpenDatabase("TEST.IDM")
		Set NewField = db.TableManagement
			Set ThisTable = db.TableDef
				Set MyField = ThisTable.NewField
					
					MyField.Name = "LINE_ITEM"
					MyField.Description = ""
					MyField.Type = WI_NUM_FIELD
					MyField.Equation = "0"
					MyField.Decimals = 0
					
					NewField.AppendField MyField
					NewField.PerformTask
				Set myField = Nothing
			Set ThisTable = Nothing
		Set NewField = Nothing
	Set db = Nothing

End Function

Function updateField()
	Dim db As database
	Dim table As table
	Dim field As field
	Dim rs As Object
	Dim rec As Object
	Dim i, j As Long
	Dim n As Long
	Dim currentValue As Integer
	Dim prevValue As Integer
	
	Set db = client.OpenDatabase("TEST.IDM")
	Set table = db.TableDef
	Set Field = table.GetField("LINE_ITEM")
	Set rs = db.RecordSet
	
	field.Protected = FALSE
	
	For i= 1 To db.Count
		'get the next value
		If i = 1 Then
			rs.GetAt(i)
			Set rec = rs.ActiveRecord
			n = 1
		Else 
			j =i - 1
			rs.GetAt(j)
			Set rec = rs.ActiveRecord
			prevValue = rec.GetNumValue("DOCUMENTNO")
			rs.GetAt(i)
			Set rec = rs.ActiveRecord
			currentValue = rec.GetNumValue("DOCUMENTNO")
			  
			 If currentValue = prevValue Then
				n = n + 1
			 Else
				n = 1
			End If
		End If
		rec.SetNumValue "LINE_ITEM", n
		rs.SaveRecord rec
	Next i
	
	field.Protected = TRUE

End Function