Manually doing a lookup and populating fields.

[aporta]

Hello all,

The current version of a data entry App uses the customer code (a 6 letter string) as the PK of the Customer table. The Invoice table's customer id field uses a lookup based on that string, so that when users are entering new invoices they type in the customer code and press tab, after which relevant fields from the customer record are copied to the invoice record.

The app is being ported to SQL backend, where for reasons of efficiency the link between Invoices and Customers is an integer instead of a string. I successfully added a lookup to the Invoices table/customerid field to get the relevant information to the invoice record from the Customers' table, so that during data entry users can either enter the (integer) customer id or do a lookup for the customer if the id is unknown.

To minimize the transition between the two version of the App, I would like to add a way for users to enter the customer code (string) on the Invoices form and have the relevant data pulled from the Customers table, just like it is done by the lookup.

I am not sure how to do that in Alpha. The farthest I got was adding an unbound field to a form backed by a variable. I added an OnChange event handler that read in the customer code contained in the variable, and then queried the Customer's table for the records whose customer codes are like the value that was entered in the field (the full code is below).

However, the next step would be to display a window that would allow the user to select which customer they want, and then populate the invoice's record with the relevant data.

Is it possible to do that in Alpha? If so, how? Is there a better alternative?

Any help is appreciated.

Thank you,

Armando

'Opens the default Form or Browse layout, displaying all, or selected records in the layout.
'Check to see if you are running the script from within a Form

dim flag_error as l
flag_error = .f.
if is_object(topparent.this) then 
	p = topparent.this
	
	if eval_valid("P:Customerlookuptext.value") then 
		DELETE Parameter1 
		'Set the Parameter variable to the object's .value property
		Parameter1 = eval("P:Customerlookuptext.value")
	else
		flag_error = .t.
	end if 
else
	flag_error = .t.
end if 

if flag_error then 
	ui_msg_box("Error","Customerlookuptext does not exist. This script cannot be run in this context.",UI_STOP_SYMBOL)
	end 
end if 

filter = "[varC->parameter1] $ CUSTOMERDISPLAYNAME"
Dim filter2 as C = replace_parameters(filter,local_variables())
ui_msg_box("Filter2", filter2)

query.filter = filter2
query.order = ""

'Open the default Form showing just the records that satisfy the filter
DIM Shared varP_layout as P
DIM enable_show_all as l 
'The enable_show_all flag turns makes the Show-All button on the form toolbar gray.
'This means that the user cannot turn off the base query for the form.
enable_show_all = .f.   
varP_layout =  a5_open_default_Form("customers",query.filter,query.order,"Temporary Query","",enable_show_all)