How to Add Records to Dynamic Look-up

[forskare]

I have a field set to Look-up and did not check the User Must Select From List check box there by allowing the user to enter a value that is not stored in the Look-up table. The look-up definition also adds data to the corresponding fields once the value is selected.

My question: Is it possible to add the new record or the new data to the Look-up table once it is entered in the table in question? If so how? I know I could go to the look-up table and re-enter it but I'm hoping there's a way to copy the data to the look-up table to avoid redundancy.

Thanks much,

kenn

[Davidk]

Re: How to Add Records to Dynamic Look-up

I'd have a look at this but just can't find the same stuff sometimes... like now. Is this using a Grid with a DBF table? I can't seem to find the option "User Must Select From List". Point me in the right direction.

[MarionT]

Re: How to Add Records to Dynamic Look-up

DavidK -
the UserMustSelect from List option is within the dialog window for:
Grid.Field.Lookup.EditCombo
and/or
Grid.Field.Lookup.AutoSuggest


Kenn
if you are using SQL, you can find out which are the naughty records that are in the table and not in the lookup table, and then add them to the lookup table (and set a relationship between the two fields, so it can't happen again); select Fieldx from TableBlah where Fieldx not in (select FieldY from LookupTable)

to facilitate adding the naughty records to the lookup table:
Select 'insert into Lookup Table (FieldY) Select '+Fieldx
from TableBlah
where Fieldx not in (select FieldY from LookupTable)

that will give you a result set which is the code for doing the inserts

then you can do an update to fill in the other fields as needed

[Davidk]

Re: How to Add Records to Dynamic Look-up

Thanks Marion... I was looking at a fieldtype of Dropdownbox... no wonder...

[forskare]

Re: How to Add Records to Dynamic Look-up

Hello David and Marion,

It used to be that it wasn't hard to explain where things are and how to get to them. Now, there are so many properties, it almost takes a GPS to find them. Once something really cool is found, I can't remember where I found it a half hour later. Hopefully, this will be a better explanation. In the Detail View part of the grid, click on the Properties and go to the Lookup section' check the Lookup box and then choose Auto Suggest List in the Lookup Type. Next, open the Lookup Definition and you'll see the Auto Suggest Field settings window. The 4th setting from the top is the User Must Select From List check box. In my case, the user must have the ability to enter data if there is not a matching value in the Lookup table.

What I need, is the ability to add the new value to the Lookup table w/o having to go to the Lookup table and re-enter it.

Also, the Lookup table is a dbf table. In the Lookup Definition, there is nothing which would allow me to add the fields to the look-up table.

Thanks

kenn

[Davidk]

Re: How to Add Records to Dynamic Look-up

I thought the Javascript AfterSelect event in the Lookup Definition might help out here... but, alas, it does not. This event only fires when an item is selected from the dropdown list.

So, I think you'd have to use the Field, Javascript, OnChange event. I put an Ajaxcallback in this event. I'm using a MYSQL table for the Lookup table.
Overall, try to find the Item being added... if it's there then all is ok. If it's not there, then add it.

EDIT: Just read your post, Ken, so you'll need to change stuff a bit for the DBF table.

var rowNum = {Grid.RowNumber};
{grid.Object}.ajaxCallback('G',rowNum,'UpdateLookupTable');

And, the Xbasic function is...

function UpdateLookupTable as c (e as p)
'This function will handle the Ajax callback. 
'It must return a string of Javscript commands that will be executed in the Browser.	

'The 'e' object contains all of the data that is submitted in the Ajax callback.

dim cn as sql::connection
dim args as sql::arguments
dim sqlStmt as c
dim rs as sql::resultset

flag = cn.open("::Name::YOURNAMEDCONNECTION","YOURSQLACCOUNT","YOURPASSWORD")

'Is the Item selected in the Lookup table?
dim sqlStmt as c
sqlStmt = "SELECT ProdName FROM tblProducts WHERE ProdName = :argsItem"

args.add("argsItem",e._currentRowDataNew.Account)
flag = cn.execute(sqlStmt,args)

rs = cn.resultset
flag = rs.nextRow()
if flag then
	'the item selected was already there, so don't do anything
else
	'the item is not in the lookup table, so add it.
	sqlStmt = "INSERT INTO tblProducts (ProdId,ProdName) VALUES (999,'" + e._currentRowDataNew.Account + "')"
	flag = cn.execute(sqlStmt)
end if

UpdateLookupTable = "alert('You inserted a value of: " + js_escape(e._currentRowDataNew.Account) + "');"

end function

[forskare]

Re: How to Add Records to Dynamic Look-up

Hi David,

I'm not sure if the changes I made are correct and there is more to change but I don't know what to change them to. My comments are in caps except for what you capped.

Thank you for your help.

kenn

This is what I did:

function UpdateLookupTable as c (e as p)
'This function will handle the Ajax callback.
'It must return a string of Javscript commands that will be executed in the Browser.

'The 'e' object contains all of the data that is submitted in the Ajax callback.

dim cn as dbf::connection ‘DON’T KNOW IF ‘CONNECTION’ IS NEEDED ?
dim args as dbf::arguments
dim dbfStmt as c
dim rs as dbf::resultset

flag = cn.open("::Name::YOURNAMEDCONNECTION","YOURSQLACCOUNT","YOURPASSWORD") ‘IS SIMILAR NEEDED? IF SO, WHAT?

'Is the Item selected in the Lookup table?
dim dbfStmt as c
dbfStmt = "SELECT Employer FROM tblt_Employers WHERE Employer = :argsItem"

args.add("argsItem",e._currentRowDataNew.Account) ‘DON’T KNOW WHAT NewAccount means?
flag = cn.execute(sqlStmt,args)

rs = cn.resultset
flag = rs.nextRow()
if flag then
'the item selected was already there, so don't do anything
else
'the item is not in the lookup table, so add it.
dbfStmt = "INSERT INTO tblt_Employers (Employer) VALUES (999,'" + e._currentRowDataNew.Account + "')"
flag = cn.execute(dbfStmt)
end if

UpdateLookupTable = "alert('You inserted a value of: " + js_escape(e._currentRowDataNew.Account) + "');"

end function

[Davidk]

Re: How to Add Records to Dynamic Look-up

For DBF tables it's all different. You'll need to adjust for your field names, but this should get you close.

'The following code is for DBF Tables
'debug(1)
dim tbl as p
tbl = table.open("accountlookup")
query.filter = "acctname = '" + e._currentRowDataNew.Account + "'"
indx = tbl.query_create()
records = indx.records_get()
If records = 0 then
	'Add the item input because it's not in the table
	tbl.enter_begin(.T.)
	tbl.ACCTID = 888
        tbl.ACCTNAME = e._currentRowDataNew.Account
	tbl.enter_end(.T.)
	UpdateLookupTable = "alert('Item input added to lookup table');"	
end if
	
tbl.close()