Retrieve values from another table

[markh28]

I must have my search terminology wrong because I cant find this answer and it is probably an easy thing to do.

I have a customer table, with state tax code in it. When I go to create an invoice for this customer, I pull down a combo box and pick my customer. Once I do this, I need the state tax code in the invoice header file to be updated with the state tax code from the selected customer.

[Stan Mathews]

Re: Retrieve values from another table

That's called a lookup. There are several ways to use lookups depending on what you want to do with the result.

[MikeData]

Re: Retrieve values from another table

Is the code A tax amount?
If so, all you have to do is add more fill-in choices to your dropdown box.

[markh28]

Re: Retrieve values from another table

Thanks to all who helped. I solved it by coding the following in the OnChange event of my customer drop-down record list:

:frmInvoiceHeader:state_tax_code.value = lookup("customer.dbf","Customer_Id="+:frmInvoiceHeader:customer_id.value,"State_tax_code")
:frmInvoiceHeader:city_tax_code.value = lookup("customer.dbf","Customer_Id="+:frmInvoiceHeader:customer_id.value,"City_tax_code")
:frmInvoiceHeader:other_tax_code.value = lookup("customer.dbf","Customer_Id="+:frmInvoiceHeader:customer_id.value,"Other_tax_code")
:frmInvoiceHeader:Terms_days.value = lookup("customer.dbf","Customer_Id="+:frmInvoiceHeader:customer_id.value,"Due_days")


This works. But if there is a better way please advise.

[Al Buchholz]

Re: Retrieve values from another table

I must have my search terminology wrong because I cant find this answer and it is probably an easy thing to do.

I have a customer table, with state tax code in it. When I go to create an invoice for this customer, I pull down a combo box and pick my customer. Once I do this, I need the state tax code in the invoice header file to be updated with the state tax code from the selected customer.

Have you tried a field rule lookup and assigned values from the lookup table like the example from AlphaSports attached?

Note that when a product is picked the retail price is placed in the invoice_items price field.

[Mike Wilson]

Re: Retrieve values from another table

What Al wrote gets you there as directly as possible. Even so, I would like to make this one comment. Your code uses lookup() 4 times. This opens a table 4 times. If you are needing to gather several fields of record data, I would recommend opening the table once and getting all the data with that one opening:

[COLOR="Blue"]' get customer_id selected[/COLOR]
	dim vcid as C
	vcid=topparent:customer_id.value

[COLOR="blue"]' open table filtered to the customer_id and fill in the form fields [/COLOR]	dim t as p
	t=table.open("customer")
	query.filter="Customer_Id="+quote(vcid)
	query.order=""
	xi=t.query_create()
		topparent:state_tax_code.value = t.State_tax_code
		topparent:city_tax_code.value = t.City_tax_code
		topparent:Other_tax_code.value = t.Other_tax_code
		topparent:Terms_days.value = t.Due_days
	xi.drop()
	t.close()