Multiple table lookup / Browse forms on VIEWS

Questions related to using nuBuilder Forte.

Multiple table lookup / Browse forms on VIEWS

Postby agnar » Fri Feb 09, 2018 11:14 pm

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.
agnar
 
Posts: 37
Joined: Mon Apr 22, 2013 6:28 am

Re: Multiple table lookup / Browse forms on VIEWS

Postby admin » Sat Feb 10, 2018 12:30 am

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
admin
Site Admin
 
Posts: 3289
Joined: Mon Jun 15, 2009 9:53 am

Re: Multiple table lookup / Browse forms on VIEWS

Postby agnar » Tue Feb 13, 2018 1:27 am

That worked!
agnar
 
Posts: 37
Joined: Mon Apr 22, 2013 6:28 am

Re: Multiple table lookup / Browse forms on VIEWS

Postby admin » Tue Feb 13, 2018 5:35 am

.
admin
Site Admin
 
Posts: 3289
Joined: Mon Jun 15, 2009 9:53 am


Return to General