get previous record
Forums
Good evening, I'm trying to recreate a balance but it depends of some previous values, theres any way to get a value from the previous record with IDEAScript (i know there's a GetPreviousValue in the EquationEditor).
I will appreciate your help.
Thank you.
Hi Brian,
Hi Brian,
I have the same problem as aamparo...
Say, I have field like this:
1
0
0
2
0
3
0
and I want to create a new field where records with "0" value get a previous value that not "0".
GetPreviousValue function gives me just one record above.
Is there any way to do this with IdeaScript?
Thank you.
Here you go, you first have
Option Explicit
Sub Main
Call addField()
Call addData2()
End Sub
Function addData2()
Dim db As Object
Dim RS As Object
Dim table As Object
Dim rec As Object
Dim i As Long
Dim amountfield As Variant
Dim tempField As Variant
' Open the database
Set db= Client.OpenDatabase("Field test-Sheet1.IMD") 'name of database
' Allow the field to be editable
Set table= db.tableDef
table.GetField("TEST").Protected = FALSE 'field to make changes to
' Get a RecordSet object for the Access database
Set RS= db.RecordSet
' Point to the first record in the database
RS.ToFirst
' Loop through every record in the database
For i = 1 To RS.Count
Set rec = RS.ActiveRecord
' Move the pointer to the next record
RS.Next
amountfield = rec.GetNumValue("FIELD") 'field where the changes are coming from
'if the first time through the loop get the temp number
If i = 1 Then
tempField = amountfield
End If
If amountfield = 0 Then
rec.SetNumValue "TEST", tempField
Else
rec.SetNumValue "TEST", amountfield
tempField = amountfield
End If
'update the changes
RS.SaveRecord rec
Next i
' Put the protection back on the unprotected fields.
Set table = db.tableDef
table.GetField("TEST").Protected = TRUE
' Clear the memory
db.close
Set rec = Nothing
Set RS = Nothing
Set table = Nothing
Set db = Nothing
'reopen the database to see the changes
Set db = Client.OpenDatabase("Field test-Sheet1.IMD")
End Function
'create a field to hold the changes
Function addField()
Dim db As database
Dim task As task
Dim table As table
Dim field As field
Dim eqn As String
Set db = Client.OpenDatabase("Field test-Sheet1.IMD")
Set task = db.TableManagement
Set table = db.TableDef
Set field = table.NewField
eqn = "0"
field.Name = "TEST"
field.Description = ""
field.Type = WI_NUM_FIELD
field.Equation = eqn
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set table = Nothing
Set field = Nothing
End Function
Hello,
Hello,
I'm trying to read data from a IMD file using syntax similar to the above rec.getNumvalue but for a string- [rec.getStringValue("FieldName")]. Each time I do this, howver, i get the error "Object does not support this property or method."
I have no problems reading a numeric value with the getNumValue function, but can't seem to read a string. Any thoughts?
Hello, I'm trying to come
Hello, I'm trying to come with two different balances after performing some computations as follows:
1. At the Beginning I have got two balances ie. Internal and external
2. The opening balance of the external balance is zero at the beginning (is to be affected by any forex movements)
3. The Internal balance is to be affected by any local transactions movement
4. When some1 receives forex it has to increase the forex balance and when some1 makes a a forex it has deplete the forex balance. In a scenario where some1 wants to make a forex expenditure and the forex balance is zero ...his or her account should be reduced by the expenditure figure ite -10
5.If some1 wants to make local transaction he/she uses the internal account balance, however if the internal doesnt have sufficient funds , the remainder amount should be deducted fom the external balance
my objective is to come up with a closing balance for each customer account (internal and external)
I have attached an excel file as an exmple.I will appreciate your help.
Hello Binwel and welcome to
Hello Binwel and welcome to the site.
Thanks for the spreadsheet. Can you redo the spreadsheet and show me what information you have available and what output you are expecting. I am assuming that you would also have a customer number and such for this? Also it you could include a couple of customers that would be good, it makes it easier to understand what you are looking for.
Thanks
Brian
Yes there is but it means
Yes there is but it means reading each record one by one and can get complicated depending on what you are doing. Depending on what it is I would try the GetPreviousValue function first before going the script route, if you do need to do it by script can you give more detail so I can help you out more.