Grid Component - New Method to Set Choices in DropdownBox Control

[kingcarol]

I wrote to Selwyn to ask if there was a way to re populate a dropdown box when it gets the focus, and he very nicely responded with info below, but I'm not experienced enough to actually use it. Here's his info:

From Selwyn:----------------------------------------------------------
you can only repopulate a dropdown box using an ajax callback. here is an excerpt from the release notes.

Grid Component - New Method to Set Choices in DropdownBox Control - A new Grid method has been added to allow you to dynamically reset the choices in a DropdownBox control.
{Grid.object}.populateDropdownBox(part,columnName,rowNum,data,clearExisting)

part G or D (depending on whether the control is in the Grid or Detail View part
columnName Name of the field. (Must be uppercase)
rowNum Row number of the control. For Detail View is 1.
data Array of choices. Each entry in the array can be an item, or an array with a display and stored value.
Examples:
['alpha','beta','gamma']
[ ['alpha','a'], ['beta','b'], ['gamma','g']

clearExisting if true, the existing choices in the control are removed before the new choices are added
End of Selwyn info--------------------------------------------------

Here's some of my dropdown box Xbasic properties. Can anyone give me a clue as to how to fill in the line that Selwyn suggested for an ajax callback? I think he wants me to figure it out myself, but still learning....

DropDownBox.Datasource = "CS"
.DropDownBox.maxchoices = 0
.DropDownBox.CS.ConnectionString= "::name::DynamicConnection_CHS"
.DropDownBox.CS.SQL = "SELECT DISTINCT DOC_TYPE FROM TblDoxTypes ORDER BY DOC_TYPE"
.DropDownBox.DisplayValueField = "DOC_TYPE"
.DropDownBox.DisplayValueFieldType = "C"

[kingcarol]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Well, I'm half guessing may way through this. Using info in previous post and info here: http://wiki.alphasoftware.com/SQL+Re...xtRow+Function

Here's my little Jing about what I'm trying to do: http://screencast.com/t/bL4RTiKnF

I have the following code in the OnFocus event for the field DOC_TYPE that is a dropdown box field. When I debug, I can see that my variable result is filling with a list of the data in the DOC_TYPE field, but my dropdown box does NOT repopulate. I've tried putting the {grid.Object}.populateDropdownBox line both inside and out of the while loop. Any ideas?

dim cn as sql::connection
dim cs as c 

'get the connection string that is defined in this Grid component
cs = e.session.__protected__xxx
cn.open(cs)

dim sql as c 

sql = "Select DOC_TYPE from TblDoxTypes"

cn.Execute(sql)

dim rs as sql::resultset
rs = cn.ResultSet
		
dim result as c

while (rs.NextRow())
     result = result + rs.data("DOC_TYPE") + crlf()
    	
end while
	
dim jscmd as c 
jscmd = <<%js%
{grid.Object}.populateDropdownBox('G','DOC_TYPE',({Grid.RowNumber}),result,true);
%js%

[mumfie]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

I think it needs to be a javascript array but i have not tested the latest version.
it looks similar to
A5.form.populateSelect

You can generate an xBasic variable in required format then replace the result reference in jscmd using stritran.

[kingcarol]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

I think I give up. Will probably just use the Auto Suggest. Edit Combo isn't good for heavy accounting data entry because of the choice switching when you type 2nd letter. I was hoping that users could see all selections in a drop down list without being limited to ones for first letter typed (as in Auto Suggest), but if I have a button to right of field that displays the whole list, I guess that's good enough. Plus I have a feeling I'm going to make use of a lot of the features available with auto suggest.

[bea2701]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Is the selector type [begins,contains etc] not the criteria todo this?

[kingcarol]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

I apologize for being such a javascript novice, but I don't know what you mean by 'selector type'??

[kingcarol]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Asked Selwyn to look at this thread, but he replied that I needed to open a support incident, so will just go on and use auto-suggest instead. ALTHOUGH, now I have to figure out how to make auto-suggest work for looking up numeric account numbers, etc. Is going to be a long, long job trying to move a full accounting, job costing application to web, methinks.

[bea2701]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Carol I setup a Jing after the weekend , oke?

[kingcarol]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

That's wonderful, Eric! I'm finding that the auto suggest is giving me some features that I like, but I think that as I get more into developing the accounting data entry grids, I'm going to want the dropdown box, so I will look forward to your Jing.

[Davidk]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Here's a sample database using the populateDropDownbox method in an ajax callback.

LASTNAME on the grid is a dropdown with an Onfocus event calling an ajax callback which calls the xbasic function popDropDown.

The xbasic function popDropDown grabs lastnames from employees table and builds a simple array for populateDropDownbox.

[kingcarol]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Thanks so much, David! As a result of studying your code, I have rewritten mine to the example shown below... AND IT'S WORKING! This message board ROCKS!

dim cn as sql::connection
dim cs as c 

'get the connection string that is defined in this Grid component
cs = e.session.__protected__xxx
cn.open(cs)

dim sql as c 

sql = "Select DOC_TYPE from TblDoxTypes"

cn.Execute(sql)

dim rs as sql::resultset
rs = cn.ResultSet
		
'Build the result set in the format needed for the populateDropDownBox method.
'Format example from Alphapedia:  result = "['alpha1','beta1','gamma1']"
		
dim result as c
result = "["

while (rs.NextRow())
     result = result + "'" + rs.data("DOC_TYPE") + "',"
end while

'The above will result in a string that ends in a comma after the last Doc Type, so we need to get rid of that last comma.
'Put a ] at the end of the line before we do strtran so that we can identify where the unwanted ending comma is.
result = result + "]"

'Now replace the ,] at the end of the line with just the ]
result = strtran(result,",]","]")
	
PopulateDropdownDocType = "{grid.Object}.populateDropdownBox('G','DOC_TYPE',({Grid.RowNumber}),"+result+",1);"

[Selwyn Rabins]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

very nice, although the code that turns a resultset into a javascript array could be optimized as follows:

dim cn as sql::connection
flag = cn.open("::Name::SQLServer_Northwind")
flag = cn.Execute("select distinct city from customers order by city")
dim txt as c 
txt = cn.ResultSet.ToString()
txt = JS_list_to_array(txt)

if you examine txt, you will see that it looks like this:

['London','Madrid','Paris']

[kingcarol]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Cool, Selwyn, thanks for the tip!

[Steve Wood]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Thanks for this post. It got me started on a similar solution. I had to populate three dropdowns all based on the selection in the first dropdown. I also needed to use a Diaplay and Stored Value, so I converted my results to the required format using long hand like Carol.

I apologize in advance for naming one of my fields "object" as you can see below.:o

dta = "alltrim(name)+'|^|'+id"
result_size 	= table.external_record_content_get("[PathAlias.ADB_Path]\object_size",dta,"name","object_id="+e._currentRowDataNew.object)
result_type 	= table.external_record_content_get("[PathAlias.ADB_Path]\object_type",dta,"name","object_id="+e._currentRowDataNew.object)
result_material = table.external_record_content_get("[PathAlias.ADB_Path]\object_material",dta,"name","object_id="+e._currentRowDataNew.object)

dim lst as c

lst = "["
for each foo in result_size
	lst = lst + "['" + word(foo,1,"|^|") + "','" + word(foo,2,"|^|") + "'],"
next	
lst = lst + "]"
result_size = stritran(lst,"],]","]]")

lst = "["
for each foo in result_type
	lst = lst + "['" + word(foo,1,"|^|") + "','" + word(foo,2,"|^|") + "'],"
next	
lst = lst + "]"
result_type = stritran(lst,"],]","]]")

lst = "["
for each foo in result_material
	lst = lst + "['" + word(foo,1,"|^|") + "','" + word(foo,2,"|^|") + "'],"
next	
lst = lst + "]"
result_material = stritran(lst,"],]","]]")

'save_to_file(result_size,"c:\si.txt")

getChoices = "{grid.Object}.populateDropdownBox('G','OBJECT_SIZE',({Grid.RowNumber}),"+result_size+",1);{grid.Object}.populateDropdownBox('G','OBJECT_TYPE',({Grid.RowNumber}),"+result_type+",1);{grid.Object}.populateDropdownBox('G','OBJECT_MATERIAL',({Grid.RowNumber}),"+result_material+",1);"

[Davidk]

Re: Grid Component - New Method to Set Choices in DropdownBox Control

Such good work in this thread... I can see turning this code into a much used function.