I have been banging my head for most of today trying to get this to work. It seems way more simple than I am making it.
I have a database table of machine names. I have a named query which I collect all of the names in the database and it works fine.
I want to dynamically build the data for the table using a custom method script on a dropdown component. I simply want to populate the dropdown data with the dataset created from the custom method script. The reason is I want to add another row to this dataset.
I have created a BuildMachineDataset custom method.
My code on the custom method is:
#create list of dataset headers
headers = ["machinename"]
#create empty list to house data
data = []
machines = []
#get number of machines
totalmachines = system.db.runNamedQuery("GetNumberOfMachines", {})
#get all machines
data = system.db.runNamedQuery("AllMachines", {})
#iterate through loop to fill in dataset
for i in range(1, totalmachines + 1):
data.append([machines])
#return dataset
return system.dataset.toDataSet(headers, data)
The named query GetNumberOfMachines correctly gives me the number of rows for the number of different machines. The named query AllMachines correctly gives me all of the machine name in their own separate row.
I then bind the dataset of the dropdown object to an expression binding which runs this script using runScript(āself.BuildMachineDatasetā, 1000)
The result is a correct number of machines but an empty dataset just showing Dataset [5R x 1C].
What am I doing wrong to not get the actual values of the machine names and am I going overboard in this code? I essentially want to create an empty dataset, fill it with the all of the machine names on their own row, then be able to add another row by using system.dataset.addRow
Why do you say the dataset is empty when it returns "Dataset [5R x 1C]" That is telling you it isn't an empty dataset but if you print what you returned with system.dataset.toDataSet() it should return something similar to that. That tells me you have 5 rows(5 machines) and you put them in as one column which matches your append. Have you tried returning the dataset into the data property of a table?
With this are you trying to build the dropdown from your machine list or are you trying build data for use in a table? If your building it to you in a table it should work as is. If you want to use it in a dropdown, the dropdown data property needs a Value and Label. Your only returning one column so your not meeting that requirement. I would change your headers variable to:
headers = ["Value","Label"]
Then I would change your data.append() to:
data.append([i,machines])
For this part, how are you trying to add the row? Are you trying to add it from the drop down or from something else? I'm sure there is something I'm missing from what you posted.
I agree the results are there but they are not showing me the actual values, just the correct number of rows in the dataset being returned from the query.
I am trying to build the dropdown from the machine list (values coming from the database from the named query). This way if more machines are added the work is already done.
I did what you recommended and added the value and label and this is what I got as a result:
Is it a matter of using getValueAt in order to access the actual value from the dataset?
One other thing I didnāt notice before. Your using data to return your second named query but then you are telling it to append empty rows to the end of it equal to the number from your total machines. But because of how your doing it, I could see it giving you an error when you run your loop. RunNamedQuery will return a dataset similar to what your creating at the end with your return but in order to append more rows to it, you would need to use system.dataset.toPyDataSet() to convert it to a python dataset which would then allow you to append rows to the data. The way it is currently I would expect to throw and error for you that you should be able to see in your console.
1 Like
I did actually think about that and was going to do it that way. Also I thought is would be easier to change the dataset āmachinesā to a pydataset. This way I can access say row 0, column 0 with machines[0][0] and can use this as my actual value from the dataset and use that value in my data.append
I marked up some notes/question. I commented out any code I put in. Depending on the answers to the questions would decide if any of it helps.
#create list of dataset headers
#### may need to change this like I mentioned earlier
headers = ["machinename"]
#create empty list to house data
data = []
#### machine is never filled in below
machines = []
#get number of machines
totalmachines = system.db.runNamedQuery("GetNumberOfMachines", {})
#get all machines
#### does this return the rows you really want or is there more you need to add to this that isn't shown?
data = system.db.runNamedQuery("AllMachines", {})
#### this can be converted to a python dataset using one extra row
# data = system.dataset.toPyDataSet(data)
#iterate through loop to fill in dataset
#### is there a reason you need this totalmachine number?
#### If machines above doesn't have a value added in here then this loop isn't needed.
for i in range(1, totalmachines + 1):
data.append([machines])
#### If this is needed and you use the toPyDataSet above then you would still need a list of machines to append
#### If this is just adding in an value/ID then you can simplify if you use toPyDataSet
# x = 0
# fData = []
# for m in data:
# fData.append([x,m])
# x += 1
#return dataset
return system.dataset.toDataSet(headers, data)
#### if you use my loop above make sure to rename data in you return
If I got what your trying to do right then it would end up looking like:
#create list of dataset headers
headers = ["Value","Label"]
#create empty list to house data
data = []
#get all machines
data = system.db.runNamedQuery("AllMachines", {})
data = system.dataset.toPyDataSet(data)
#iterate through loop to fill in dataset
x = 0
fData = []
for m in data:
fData.append([x,m])
x += 1
#return dataset
return system.dataset.toDataSet(headers, fData)
1 Like
Thanks! I only needed the values of the machine names in the dropdown list so no need for two columns. Your idea of converting it to a pydataset was the key. Here is the code that worked for the custom method:
#create list of dataset headers
headers = ["machinename"]
#create empty list to house data
data = []
allmachines = []
#populate allmachines dataset from DB named query and change to pyDataset
allmachines = system.db.runNamedQuery("AllMachines", {})
allmachinesPyData = system.dataset.toPyDataSet(allmachines)
#get total number of machines from DB named query
totalmachines = system.db.runNamedQuery("GetNumberOfMachines", {})
#define "All Machines"
all = "All Machines"
#iterate through loop to fill in dataset
for i in range(0, totalmachines):
machine = allmachinesPyData[i][0]
data.append([machine])
#add All Machines option at end of dropdown once all machine names have been listed
data.append([all])
#return dataset
return system.dataset.toDataSet(headers, data)
If totalmachines is just a count of allmachines then you donāt need it. Also if thats the case I donāt think you need the loop either since it looks like your just looping through allmachines and feeding it right back in to a dataset so you can append an entry that says āAll Machinesā to the end of it. Would it work if you did:
#create list of dataset headers
headers = ["machinename"]
#populate allmachines dataset from DB named query and change to pyDataset
allmachines = system.db.runNamedQuery("AllMachines", {})
data = system.dataset.toPyDataSet(allmachines)
#add All Machines option at end of dropdown once all machine names have been listed
data.append(["All Machines"])
#return dataset
return system.dataset.toDataSet(headers, data)