Populating a combo box from Excel
Hi,
Tl;dr: How could I use a lookup table to store a small Excel file (no more than 100 lines) and input items that meet certain critera into combo boxes as items?
I have an automation that requires selecting an item from 2 combo boxes before outputting a 3rd item of information. So you are selecting a Category and then the Title, then the full item.
All of this information is stored in an Excel file. Here is a snippet:
The UI looks like this:
Hi,
Tl;dr: How could I use a lookup table to store a small Excel file (no more than 100 lines) and input items that meet certain critera into combo boxes as items?
I have an automation that requires selecting an item from 2 combo boxes before outputting a 3rd item of information. So you are selecting a Category and then the Title, then the full item.
All of this information is stored in an Excel file. Here is a snippet:
The UI looks like this:
My automation searches through Excel Column 1, for Categories matching the Category selected in combo box 1. Any results are added as items, to Combo box 2. The automation will then search Excel Column 2, for any Titles matching the Title selected by the user in combo box 2. Here's a snapshot of the automation:
This was working perfectly fine, however it is now going very slowly when populating the combo boxes. I assume having the combo boxes repopulated from Excel every time a value is changed, is not the most efficient way to do this.
Could this be done more effectively with a lookup table? If so, please could somebody point me in the right direction?
Thanks,
Ryan
Accepted Solution
Pegasystems Inc.
US
You will need to first alter your spreadsheet to include a column named Index. This should just be a unique value - an ordinal number will do. Also change the other column headings to Category, Title and Text. Now, create a lookup table with four columns - Index, Category, Title and Text.
In your automation, export data from the spreadsheet and do a replace table on your lookup table. The columns names must match the column headers in Excel. Only export a valid range - if you do not choose the exact range the replace table will throw an exception.
To populate the Category combo box (assuming 2 combo boxes and a listbox) you can iterate through the table and find the unique values.
You will need to first alter your spreadsheet to include a column named Index. This should just be a unique value - an ordinal number will do. Also change the other column headings to Category, Title and Text. Now, create a lookup table with four columns - Index, Category, Title and Text.
In your automation, export data from the spreadsheet and do a replace table on your lookup table. The columns names must match the column headers in Excel. Only export a valid range - if you do not choose the exact range the replace table will throw an exception.
To populate the Category combo box (assuming 2 combo boxes and a listbox) you can iterate through the table and find the unique values.
Define a TableView to handle filtering the dataset when the Category is selected. The TableView should be placed on the form in Design mode. Notice that the TableProvider for the TableView is set to our lookup table.
Next set the data source for theTitle combo box to the TableView. Once you have done that, also set the Display Member and Value Member as shown.
Now when the Category combo box SelectedIndexChanged event fires filter the TableView by setting the RowFilter property. Here I am using a StringUtils Format method here to build the filter, but you can use another method if you choose.
This should get you going. You obviously have to handle filtering when no items are selected and on startup. You can apply a RowFilter directly to the TableView at startup like this.
For the Text listbox control, set up another TableView. This would be filtered using a statement like this: Category='{0}' and Title='{1}' where you would replace {0} with the actual category and {1} with the actual title.
Endsleigh
GB
Also, as this application was working perfectly fine before and now it is going slow, would anybody have any suggestions as to some debugging tools to find out where the lag is coming from? I have tried to read runtime logs before, but struggled as they are quite overwhelming. If the only 2 options available are breakpoints and the runtime log however, I will give it another try.
Pegasystems Inc.
US
You can export the contents of the worksheet using the Excel connector ExportData method. This will create a DataTable which you can store in a lookup table. You may need to massage the data a little before loading it into the lookup table. Once there, you can then use a series of TableViews to filter the data for your combo boxes.
Thomas reported a nice way to make Runtimelogs easier to use. See this post:
https://collaborate.pega.com/discussion/quick-log-parsing
ExecutionLink lines are the yellow lines, while propagating are blue lines. If you add the following to the LogParser.txt: Control is matched and Control is detached - you can catch the Created and Destroyed events as well.
Endsleigh
GB
Is there any documentation available on how to use TableViews within Openspan/Pega?
I have stored my data in a lookup table fine, but I have no idea how to use TableViews and I am struggling to find anything online, even looking in C#.
Or could you provide an example of how I could do something like SELECT * WHERE X = Y?
UPDATE:
I think I've figured this out! I have used the Loop method of tableView to achieve the same thing I was doing before and it seems to be working ok :) If anyone does have info on TableViews however, it would always be useful!
Thanks,
Ryan
Pegasystems Inc.
US
The tableview documentation can be found at http://help.openspan.com/80/ (search for TableView).
There are many ways to do what you want, but what the tableview offers is the ability to filter the dataset. Set the Filter property using SQL WHERE clause syntax (https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.110).aspx) and you can either bind that to a combobox or using the ToTable method to output a DataTable which can be bound. ToTable offers a method signature that allows you to remove duplicates from your result set which may be necessary for your first column. Using ToTable, you would store the results in a LookupTable which you then can bind. I assume you will need to do this setup only once, so I would do a LookupTable for column 1, then a separate TableView for column2 and column 3.
Endsleigh
GB
Hi Jeff,
Thanks for all of your help. I know you're providing me with a lot of information here, but with no practical examples available for TableViews on the help section, I'm afraid it's just not clicking in my head.
I've written this statement in the Filter property of one of my TableViews:
SELECT * FROM lookupTable WHERE Category = {0}
I don't know how to action this query. Is the filter applied at Runtime or do I need to use the method ApplyFilter? Is {0} valid to represent a variable and if so, how can I pass in this variable within an automation?
If you wouldn't mind, could you please provide a practical example of how TableViews and filters might be used, with screenshots?
Thanks very much for your help so far, I appreciate your time.
Ryan
EDIT:
Hi Jeff,
Thanks for all of your help. I know you're providing me with a lot of information here, but with no practical examples available for TableViews on the help section, I'm afraid it's just not clicking in my head.
I've written this statement in the Filter property of one of my TableViews:
SELECT * FROM lookupTable WHERE Category = {0}
I don't know how to action this query. Is the filter applied at Runtime or do I need to use the method ApplyFilter? Is {0} valid to represent a variable and if so, how can I pass in this variable within an automation?
If you wouldn't mind, could you please provide a practical example of how TableViews and filters might be used, with screenshots?
Thanks very much for your help so far, I appreciate your time.
Ryan
EDIT:
This is what I currently have to populate my first combo box:
Prior to this, I have a loop that finds the first blank line in the Excel file and so the number of rows. I don't know if this is an efficient way to find number of rows, but it works.
Accepted Solution
Pegasystems Inc.
US
You will need to first alter your spreadsheet to include a column named Index. This should just be a unique value - an ordinal number will do. Also change the other column headings to Category, Title and Text. Now, create a lookup table with four columns - Index, Category, Title and Text.
In your automation, export data from the spreadsheet and do a replace table on your lookup table. The columns names must match the column headers in Excel. Only export a valid range - if you do not choose the exact range the replace table will throw an exception.
To populate the Category combo box (assuming 2 combo boxes and a listbox) you can iterate through the table and find the unique values.
You will need to first alter your spreadsheet to include a column named Index. This should just be a unique value - an ordinal number will do. Also change the other column headings to Category, Title and Text. Now, create a lookup table with four columns - Index, Category, Title and Text.
In your automation, export data from the spreadsheet and do a replace table on your lookup table. The columns names must match the column headers in Excel. Only export a valid range - if you do not choose the exact range the replace table will throw an exception.
To populate the Category combo box (assuming 2 combo boxes and a listbox) you can iterate through the table and find the unique values.
Define a TableView to handle filtering the dataset when the Category is selected. The TableView should be placed on the form in Design mode. Notice that the TableProvider for the TableView is set to our lookup table.
Next set the data source for theTitle combo box to the TableView. Once you have done that, also set the Display Member and Value Member as shown.
Now when the Category combo box SelectedIndexChanged event fires filter the TableView by setting the RowFilter property. Here I am using a StringUtils Format method here to build the filter, but you can use another method if you choose.
This should get you going. You obviously have to handle filtering when no items are selected and on startup. You can apply a RowFilter directly to the TableView at startup like this.
For the Text listbox control, set up another TableView. This would be filtered using a statement like this: Category='{0}' and Title='{1}' where you would replace {0} with the actual category and {1} with the actual title.
Pegasystems Inc.
US
Here is an alternate method for populating the Category combo box. Add another TableView to the form named tvCategory. Attach it to a new lookup table named luCategory. This lookup table will have a single column named Category. Set the data source for the Category combo box to tvCategory and the DisplayMember and ValueMember to Category.
When you are loading the spreadsheet add the following.
The ToTable method of the TableView will allow you to output a data table with distinct values and just the columns you are interested in. Here we are looking for a list of distinct Category values. Since luCategory is the TableProvider for tvCategory and tvCategory is bound to our Category combo box, the combo box is populated and then the RowFilter for tvTitleSource is set to reflect what is selected in the combo box.
One other small change is required - we need to change the event triggering the update of tvTitleSource to SelectedValueChanged.
Here is an alternate method for populating the Category combo box. Add another TableView to the form named tvCategory. Attach it to a new lookup table named luCategory. This lookup table will have a single column named Category. Set the data source for the Category combo box to tvCategory and the DisplayMember and ValueMember to Category.
When you are loading the spreadsheet add the following.
The ToTable method of the TableView will allow you to output a data table with distinct values and just the columns you are interested in. Here we are looking for a list of distinct Category values. Since luCategory is the TableProvider for tvCategory and tvCategory is bound to our Category combo box, the combo box is populated and then the RowFilter for tvTitleSource is set to reflect what is selected in the combo box.
One other small change is required - we need to change the event triggering the update of tvTitleSource to SelectedValueChanged.
Endsleigh
GB
Hi Jeff,
Thankyou very much for all of that, you've been a fantastic help! I really appreciate it.
I have one tiny problem left on this matter - When I select an item from ComboBox1, Combobox2 populates as it should and then automatically selects the first entry in the list. Is there a way to stop it from auto-selecting so that the user has to pick one?
Previously, before the TableViews and LookupTables, I added one item to the start of the list that said "~Please Select~", but as the DataSource is now set, I cannot add in extra values. A blank Combobox2 text field would be sufficient, it doesn't need to say "~Please Select~". I have tried to set text to blank upon load, but when the user then keys down, we end up at item 2 in the list, because item 1 was initially selected and we've just deleted it and then keyed down to 2.
So I'm looking for the list the be populated, but no item initially selected and the user can either click and select or press key down to get to item 1 in the list.
Thanks,
Ryan
Pegasystems Inc.
US
Set the SelectedIndex of the combo box to -1 after the list is populated. -1 mean nothing is selected and should give you what you are looking for.