How do I populate more than one column with data from lookup table?

Applications Forms
#1

I have two tables: order and product. I use a lookup to select the product fro the product table. The lookup inserts the product name into the order table record. But the product has price and VAT attributes that I would also like to copy into the order table.

How can I perform the product lookup and also add the price and VAT values from the product record into the order record on the form? As the form record is not complete I cannot use an after-insert event. There must be a way as this must be a common operation. I didn’t find anything in the knowledge base or videos but maybe I did not use a good search pattern.

#2

That cannot be done with a simple lookup. You need to define an additional onchange event and perform your own seach for additional fields after changing the lookup value.

#3

By additional onchange event do you mean I create a New Ajax Event (in Form Settings->Ajax Events)?

Do I have write some PHP or Javascript to do this?

If I am just updating fields on the form can I just reference the fields from within PHP or do I need to write dome real Ajax? (PHP and SQL I’m good but I have little JavaScript experience)

If I just want to execute some SQL can I just use sc_exec_sql(…)?

Ok, I think I found it but there is a bug there.

The Parameters(Fields) cannot be fully display and there is no scroll bar to move them. If you minimize the Module_Code that doesn’t help; if you open the Parameters block then the Module_Code block opens up as well. Ditto closing the block .

#4

If you create a lookup link you can only return one value afaik. But it will trigger an onchange event so if you create an ajax event on this you can get the value from the listbox and perform the select to obtain the other values and assign them to you formfields.

#5

Ok, I can now see how I can use some SQL in the Ajax event (using sc_exec_sql(…)) to get the extra data. And to access the form field data I assume that am going to require some javascript. Are there some basic (ie simple) examples of this?