Extract unique records into a new database

danywesley

Forums

Good morning,I'm new to the site but also to programming. I train myself on the creation of scriptIdea in order to automate repetitive tasks. Inspired by messages found on your site, I tried to develop the following script to 1) write information in a virtual field to determine unique records based on an ID and a date of action(oldest) see extract unique records directly into a new file. Nothing works. Despite the instruction field.protected= false I get an error message: "Impossible to establish the value of this field. Is the field virtual or protected?".
Thank you in advance for any help you want to give me.
Here is my script:
Function getValue()    Dim db As database    Dim rs As RecordSet    Dim rec As Record    Dim unique As Long        Set db = Client.OpenDatabase(sFilename)    Set table = db.TableDef    CountRecords = db.Count            Set oListeDossiers = CreateObject("Scripting.Dictionary")    For CurrentRecord=1 To CountRecords        Set rs = db.RecordSet            ' Obtain the CurrentRecord from the RecordSet.            rs.GetAt(CurrentRecord)             Set rec = rs.ActiveRecord            sId = rec.GetCharValueAt(1) '  colonne ID            sValue = rec.GetCharValueAt(2) ' colonne Date_Action            currentDate=rec.GetCharValueAt(2)  ' colonne Date_Action                                                                        If Not oListeDossiers.exists(sID) Then                              key = sID                        valeur = sValue                        oListeDossiers.Add key, valeur                         updateField(CurrentRecord )                                            Else                                                     If  currentDate<= oListeDossiers(Sid) Then                                                               updateField(CurrentRecord)                                                        End If                                      End IfNext CurrentRecord            Set rec = Nothing        Set rs = Nothing    Set db = NothingEnd FunctionFunction updateField(ByVal activeRec As String)    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 vAnneeRole As String    Dim vDateMer As String    Dim vNom As String    Dim vAction As String    Dim vUnique As String    Dim Flag As Long     Flag = 0    Dim currentValue As Integer    Dim prevValue As Integer        Set db = client.OpenDatabase("TEST.IMD")                ' Allow the field to be editable     Set table = db.TableDef        ' Get a RecordSet object for the Access database         Set Field = table.GetField("UNIQUE")    Set rs = db.RecordSet      field.Protected = FALSE        'get the value        rs.GetAt(activeRec)    Set rec = rs.ActiveRecord        vAnneeRole = rec.GetCharValueAt(1) ' colone ID        vDateMer= rec.GetCharValueAt(2)     ' colonne Date_Action        vNom=rec.GetCharValueAt(3)            ' colonne Nom        vAction= rec.GetCharValueAt(4)         ' colonne Action            Set Field = table.GetField("UNIQUE")    field.Protected = FALSE    rec.SetNumValue "UNIQUE", Flag                rs.SaveRecord rec            field.Protected = TRUEEnd Function

Brian Element Sun, 04/16/2023 - 07:31

Hi Wesley,

I looked through your code, it is hard to test since I don't have a database.  The first obvious problem is your are using the GetCharValueAt to write the results, look in the language browser, namely under the Record object, as there are examples on how to write out items.  You are looking for the SetCharVal, SetNumVal, SetDateVal, each one has to match with the field type you are working with.

This area is probably the most complex part of the IDEAScript as if you don't do it correctly you can easily corrupt your database and cause IDEA to crash, so always make sure you save your script before testing it and make sure you are working on a backup of your database or a copy of it so that if anything goes wrong you don't loose your original data.

danywesley Sat, 07/08/2023 - 18:38

First of all really sorry to answer so late but I was prevented. Then a big thank you for answering me. As I told you I am really beginner in programming.  I don't have a proper database. I am a simple idea user and I wanted to discover the script part by myself. To do this I wanted to learn to read in an ideascript database ( It's done thanks to reading your posts). I then wanted to learn to write in a database. As you said it is indeed the most complex part. But how read a file, save the data in an array in memory and write in the idea database only filtered datas. For example, I only write exclusive duplicate data to the database ? Do  you have a basic script model (something very simple) that I can just study? Thanks in advance.
Dany