Multiple table lookup / Browse forms on VIEWS

[agnar]

Assume I have the following tables (this example is a bit contrived, but illustrates my problem well):

Code: Select all

product (product_id, prd_name)
price (price_id,prc_product_id,prc_value)
invoice_item (invoice_item_id, iit_price, iid_description)

Now, in an invoice_item subform, I'd like to have a lookup item that will populate the iid_description field with prd_name and iit_price with prc_value.

But when running the lookup AB-code.

Code: Select all

nuSetFormValue( 'iit_description', noLookupRecord()->prd_name);

Nothing happends.

I then got the idea that I could create a database view, and create a browse form on that:

Code: Select all

CREATE VIEW prouct_price AS 
SELECT * FROM price LEFT JOIN product ON price_id = prc_product_id

But, upon creating the FastForm, no record was added in the zzzzsys_form table. I suspect that is because the view does not have a primary key. And as far as I concern, views in mysql cannot have a primary key.

I could make this work in nuBuilder 2.

Does anyone know the trick.

[admin]

Agnar,

nuLookupRecord() will return only 2 things.

- The fields of the table used by the Lookup (price_id,prc_product_id,prc_value)
- ID which is the same as price_id.

So you will need to do something like this...

Code: Select all

$l = nuLookupRecord();
$s = "SELECT * FROM product WHERE product_id '$l->prc_product_id' ";
$t = nuRunQuery($s);
$r = db_fetch_object($t);

nuSetFormValue('iit_price', $r->prc_value);
nuSetFormValue('iit_description', $l->prd_name);

You could also do it the way you suggested with a VIEW but you need to create the Form manually via the Forms Button.

Steven

[agnar]

That worked!

[admin]

.