Output calculation into new field

maxsjo

Forums

Hey, I've been trying to create a script which will perform a calculation to verify that a list of swedish social security numbers are valid. This is done by a mathematical process.
What I am trying to do is to get the first value from the field containing the social security numbers and output the verification in a new field that has been created in the script. What happens when i run the script is that the field is created correctly, but the output doesn't get revealed/updated until i actually touch/edit the cells manually within the database. Also, if you run the script twice, the first field gets filled correctly. I'm quite the beginner in regards to scripting, so the solution might be very simple. 
I will attach the script and an excel-file containing a couple of sample numbers which should work and output "Yes". I hope you understand what I'm trying to do and that you are able to find a solution.
Thank you!

Brian Element Fri, 09/29/2017 - 09:27

Hi maxsjo and welcome to the site.

That is one of the quirks of the software that you need to close and reopen your database to see the updated values.

Make the following modifications to your script and it should fix your problems.

Add a global variable to hold the filename and then after you call the createField() function close and reopen your database.  You will also need the to get the filename.  Here is the additional code, adding these four lines should fix your problem.

 


Dim sFilename As String

Sub Main
	
	Call createField()
	Client.CloseDatabase(sFilename)
	Client.OpenDatabase(sFilename)
End Sub

Function createField
    ' Open the database.
    Set db = Client.CurrentDatabase()
    sFilename = Client.CurrentDatabase.Name()

All the other code stays the same. 

Good luck with your project.

Brian

maxsjo Fri, 09/29/2017 - 09:58

Thank you for your quick reply Brian!
I tried the solution you suggested but ran into some problems on both mine and my colleague's computer. It seems to corrupt the database before restarting it somehow, and most often it resulted in IDEA crashing. I don't know if it has something to do with it selecting the full file directory as sFilename when it should simply be ("sampledatabase.IMD"). We couldn't seem to solve that.
I however found a workaround where if i, after the whole function, create a new field with the same name as the first, and then right afterwards remove the duplicate, it seems to update the field correctly. 
 
So basically we first create NewField which looks empty even after the RecordSet.When we then create another NewField it gets named NewField1 and NewField is updated with the RecordSet data.We then remove this empty NewField1 and we're left with the correct field.
It's not pretty, but it looks to work as intended.
Thank you!

Brian Element Fri, 09/29/2017 - 10:09

That is strange.  I was having the same problem with IDEA crashing but when I moved the open and closing of the database to another function that solved the problem.  Seems strange that having the open and closing of the database in the sub main function for you still causes a crash.  Glad you found a work around.