Skip to main content

array to hold multiple fields

Hi all
I've had a look at the script here to get an idea of how to hold multiple fields in an array
https://ideascripting.com/snippet/array-hold-all-field-information
 
Tried adapting it to my own script, attached (quite new to this so sorry if my script is hideous)
note I've trimmed out some of the stuff from my script like menus/functions that aren't applicable to this question
 
I think the issue is that I'm not actually populating the data into the array properly and it might be related to the part in bold below.
Can anyone give me some pointers to fix this?
 
In summary I want the values in SQLDataBaseList.IMD (2x columns of data) to be loaded into a variable so that I can create conditions for each database "name" depending on the "compatibility_level" value, I don't think I'll have problems creating this once the variables can actually be loaded.
 
Thank you for your thoughts on this :)
 
 
Function DBArrayGen
Dim db,rs,rec,task As Object
Dim CountRecords,CurrentRecord As Integer
Set db = Client.OpenDatabase("SQLDataBaseList.IMD")
CountRecords = db.Count
ReDim arrDBList(CountRecords)
Set rs = db.RecordSet
For CurrentRecord = 1 To CountRecords
rs.GetAt(CurrentRecord)
Set rec = rs.ActiveRecord
arrDBList(CurrentRecord-1).db_name = rec.GetCharValue("name")
arrDBList(CurrentRecord-1).db_version = rec.GetNumValue("compatibility_level")
Next 'CountExtract
Set db = Nothing
Set rs = Nothing
Set rec = Nothing
End Function

Brian Element Thu, 02/09/2023 - 12:23

Hi Joel,

From what I can see you are using the array that was defined to hold your database in the drop down for your dialog.  You never defined an array to hold the fields, so this array isn't going to work as it hasn't been properly defined.

What you need to do is define an array for your types, such as:

Dim tFields() As Fields 'define the array to hold all the field information

If you want to make this array global you need to add it before the sub main line.

So ReDim arrDBList(CountRecords) would become ReDim tFields(CountRecords) and 

arrDBList(CurrentRecord-1).db_name = rec.GetCharValue("name")
arrDBList(CurrentRecord-1).db_version = rec.GetNumValue("compatibility_level")

 

would become 

tFields(CurrentRecord-1).db_name = rec.GetCharValue("name")
tFields(CurrentRecord-1).db_version = rec.GetNumValue("compatibility_level")

Hopefully this will fix your problem. 

Also I noticed that you are defining multiple variables on the same line.  IDEAScript has a bug that only the last one on the line will be properly defined and all the others will be defined as variants.  I recommend that you define each variable separately on each line so that the variable are defined with what you expect.

Joel Edwards Fri, 02/10/2023 - 04:39

In reply to by Brian Element

Thank you Brian
I'm still a little lost as you mention I never defined the array. In the dialog portion of the script I had attached the list to dialogmenu2 
DropListBox 31,1,42,10, tFields(), .DropListBox1
Upon doing this, the array is defined on the first line of the script
Dim tFields() AS fields
If I move this line to anywhere else in the script above sub main then it just automatically reverts back to line 1 as soon as I save the script. How do I define it above submain if the attached list forces it to be assigned at the top, is this the expected behaviour or was I wrong to assign the array directly to the dialog?
 
The droplist seems to work if I only store one field but of course this doesn't help my requirements.
eg: tFields(CurrentRecord-1) = rec.GetCharValue("name")
Is there anything else you can suggest?
I've also split my multi-definitions onto distinct lines as per your suggestion however that didn't resolve my current issue.
 
Thank you

Joel Edwards Thu, 02/16/2023 - 02:05

In reply to by Brian Element

Hi Brian
So I wasn't able to get the array to work, but not a big deal as I found a way around it by joining both values together in the array and later splitting them apart to get the values, something like this:
 
tFields(CurrentRecord - 1) = rec.GetCharValue("name") & "," & rec.GetNumValue("compatibility_level")
 
DBSelected = tFields(dlgSelection.DropListBox1) 'holds list of databases gathered in DBArrayGen
database = isplit(DBSelected,"", ",",1,0) 'split the value of database from array
dbversion = isplit(DBSelected,"", ",",1,1) 'split the value of dbversion from array