Adding Manual Entry in Edit Combo Box to Master Lookup Table

[psamuels56]

Hi there-

I think someone will have fun with this one. I've looked all over, but couldn't find a technique to get this done (even though it sounds like it should be easy). I'd appreciate any suggestions you can offer:

I have a Grid with an "Edit Combo" Lookup field (only a single field is displayed) that's working perfectly. If the user clicks the dropdown arrow, a list of available choices is shown. The user can click one the choices or can manually type an entry that's not in the list. Everything is working the way I'd like, except that I would like the user to be prompted with a question like "Would you like to add that to the Master List?". If they respond "Yes," then whatever was manually entered into the the field would create a new record in the SQL table that supplied the lookup (Ajax Callback), so that it would appear in future lookups. NOTE: The SQL table has only this field... and was created just for this lookup.

I was trying to use the "NotInList" command to call an XBasic function with an "INSERT" SQL statement, but I don't know how to create the "prompt" where the user can choose whether to add the record to the list or not (e.g. it's a one-time description that won't be used again, so we don't want to add it to the Master List). I don't know how to pop-up the "Do you want to..." question and how to translate the response into the XBasic function.

If you could offer some suggestions or guide me in the right direction, I'd appreciate it.

Have a great holiday weekend.

Phil

[Davidk]

Re: Adding Manual Entry in Edit Combo Box to Master Lookup Table

Your OnNotInList event code might look like this...

var newItem = this.value;
//place this Javascript in your code, and then reference the rowNum variable.
var rowNum = {Grid.Object}._selectedRow;

A5.msgBox.show('New Entry','Do you wish add this entry to the dropdown?','ync',function(button){
    if(button == 'yes'){
    	var addlData = "__newItem=" + newItem;
    	{grid.Object}.ajaxCallback('G',rowNum,'myXbasicFunction','',addlData);
        // call your XBasic Ajax Callback
    } else if(button == 'no'){
        // code to close without saving...
    }
});

And your Ajax Callback might look like this...

function myXbasicFunction as c (e as p)

debug(1)
	
dim newItem as c = e.__newItem	
	
end function

You may want to test for empty strings and you also may need to return a refresh of the edit-combo once you've finished adding the new item to the table.

[psamuels56]

Re: Adding Manual Entry in Edit Combo Box to Master Lookup Table

David-

First, THANK YOU for taking the time to respond... especially on a holiday weekend. It was very generous of you to spend your time and I really appreciate it.

I've installed your code into my application. The part where it asks "If I wish to add the entry to the dropdown list" is PERFECT. I'm converting to Alpha from MS Access, so I'm just learning XBasic and Javascript... I never would have figured out how to do that.

My only questions are:
1) Where do I define the specific field in the "._selectedRow"? (FYI: My specific field in the Edit-Combo is called "RateDesc"). I am assuming that I'd define it in the first line variable dimension (e.g. var newItem = 'RATEDESC'), but I don't know how to format the field name.
2) Second, I'm trying to understand the code, how will the Ajax Callback know to "add a record/item to my SQL table (called "tblRateDesc")? When I was trying to make this work, I created a SQL connection in the XBasic code and used an "INSERT" SQL statement... Obviously, that's not the best way to get where I wanted. Also, I couldn't figure out how to refer to the field value of "RateDesc" in the command (I tried a lot of things. FYI, here's what my SQL command looked like, just so you can see what I was thinking: "INSERT INTO tblRateDesc ( RateDesc ) Values (.RATEDESC);").

If you could guide me through those last two issues, I think it will work great for me.

Thanks again for all your help and your valuable time. I really do appreciate it.

Phil

[Davidk]

Re: Adding Manual Entry in Edit Combo Box to Master Lookup Table

When you're working with the OnNotInList event, you get to use "this.value" which is passed into the event automatically. You don't need to reference the Textbox control you're working with.

newItem = this.value;

Gives you the new value typed into the Textbox Edit-Combo control.

What you've tried in your Ajax Callback function is exactly what you need to do. The callback is made to the server, to execute the XBasic function you've defined. The function runs and inserts the record. Here's a kind of template for getting this done. When you run your Grid under LivePreview, and an XBasic function is called, then if you have a debug(1) statement set, the code will break to the debugger and you can step through each line. You'll see in the code where and how the argument is set and then how it's accessed in the SQL Statement.

I can from Access as well... many, many years starting with version 1.0. This is a lot more fun.

function AddNewItem as c (e as p)

debug(1)

dim cn as sql::connection
dim rs as sql::ResultSet
dim args as SQL::Arguments
dim flag as l

flag = cn.open("::Name::conn") 'replace conn with your AlphaDAO connection name

if flag then
	
	args.set("myNewItem",e.__newItem)
	
	dim sqlCommand as c
	
	sqlCommand = <<%str%
	INSERT INTO tblRateDesc (RateDesc) Values (:myNewItem)
	%str%
	
	flag = cn.execute(sqlCommand,args)
	if flag then
			'All ok... nothing else to do
	else
		'there was an error - close the connection and exit
		cn.Close()
		dim msg as c 
		msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
		msg = js_escape(msg)
		dim jscmd as c 
		jscmd = "alert('" + msg + "');"
		AddNewItem = jscmd
		exit function
	end if  		
	
else
	'there was an error - no connection
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	AddNewItem = jscmd
	exit function
end if	

end function

[Davidk]

Re: Adding Manual Entry in Edit Combo Box to Master Lookup Table

Sorry... my copy/paste went bad... I've corrected the code in the previous post.

[psamuels56]

Re: Adding Manual Entry in Edit Combo Box to Master Lookup Table

Hi David-

Ahh... now, it makes sense... even to me! Your code did EXACTLY what I had hoped it could do (and it's already uploaded and "live" on the server). FYI, I added a message, in the XBasic code ("alert"), to tell the user that "the item has been added to the list" (I like it when software "talks" to the user).

I never could have done this on my own! I have to learn the various javascript (js) commands that you used. So, I can't thank you enough. What's really important to me, as I develop my programming skills, is that I actually learn something that I can use in future applications... There are 3 or 4 techniques in this code alone that I know I'll be able to used in the future. I hope others can benefit from this, as well.

THANK YOU, THANK YOU... I really appreciate your help.

Phil

[Davidk]

Re: Adding Manual Entry in Edit Combo Box to Master Lookup Table

My pleasure. I agree... there are some very basic techniques and code that I always utilize.