Question from a newbie: Get data from related table

[jules]

Hi, I've just discovered Dataface and it looks like just what I need.

However, I am struggling with the documentation I'm afraid.

I have two tables: products and manufacturers. When I create a new product, I want to read some data from the manufacturers table and put it into the new product record. So I want to present the manufacturer name list to the user and, upon selection of a manufacturer, populate the manufacture code into the product.

Can anyone tell me the best way to do this? The documentation seems to imply that this is pretty straightforwards but I'm afraid that I haven't been able to work it out.

Cheers,
Julian.

[shannah]

Currently the best way to do this is with a select widget and a valuelist for the manufacturers.

e.g.

in your valuelists.ini file (for the Products table - or for the whole app) create a valuelist for manufacturers.
e.g.

[manufacturers]
__sql__ = "select id, name from manufacturers order by name"

Your products table should have a column to store the manufacturer id.. make the widget for this column a select widget with the following settings in your fields.ini file.

[manufacturer_id]
widget:type = select
vocabulary = manufacturers ;; This references the manufacturers valuelist from your valuelists.ini file.

Then the user will be able to select the manufacturer from a select list.

Hope this helps.

-Steve

[jules]

Thanks for that Steve.

Is it possible to pick up an additional field from the manufacturers table too? I know this is not fully normalised but that's just the way the table is right now and it isn't possible to change it at present.

Or do I have to do some kind of sql for each products field that I want to pick up from the manufacturers table based on the selected id?

Regards, Julian.

[shannah]

I'm not sure I understand fully what you want to do, but I suspect that the answer is 'yes'.
If you want your select list to show you more information than just one field, you can use the SQL CONCAT() function to concatenate two fields.
e.g.

[manufacturers]
__sql__= "select id, concat(name,' - ', city) as nameAndCity from manufacturers order by nameAndCity"

Is this what you had in mind?

-Steve

[jules]

Sorry, I'm not explaining myself very well. My manufacturers table has the following:

id, name, postcode, ...

My products table has:

id, name, mid, mname, ...

Where mid is the manufacturer id and mname is the manufacturer name.

So I need to be able to pick a manufacturer from the manufacturers table when creating a new product but I also want to then populate the mname field as well without having to select it twice (once for the id field and once for the name).

Regards, Julian.

[njw]

Hi Julian

Why do you need the information copied? If you have the manufacturer id in your products record, that allows you to look up the name etc.

You can easily set Dataface up to show the manufacturer name on a select tab rather than the id, although the id is what is actually stored in the record.

Have a look at Example 3 here - http://framework.weblite.ca/documentation/tutorial/getting_started/valuelists

Neil

[shannah]

Okay.. by your previous comment that this is not normalized I assume that you understand how unadvisable this sort of structure is. Better to not include the manufacturer name in the products table - just the id. If you use a select list for the mid field, then your user will end up seeing the manufacturer name for that field anyways... not sure what the benefit of having both stored twice would be - but certainly see the drawbacks.

In any case, to answer the question in general (if you need some fields of a table automatically populated based on the input in other fields), You can do this in two ways:

1. Using the widget:attsnchange (or other relevant widget:atts: methods) you can assign your own custom javascript functions to be run when certain events occur on the widgets of your app.. You could write a javascript function that would automatically populate fields when one of the fields is changed.

2. You could set up the other fields as hidden fields (so that the user doesn't input anything), then create an afterSave() trigger to update these fields to the correct value each time the record is saved.

Best regards

Steve

[jules]

Steve, many thanks for your responses.

I do understand about normalising data but in this case, the table design was inherited from elsewhere and as the table is small, it is not worth normalising - in any case, having the data in one place simplifies all of the various applications that look-up the info so the savings in application development time easily outweigh the minor performance and storage downsides.

Anyway, thanks for the pointers. I can now go away and put something together.

Cheers,
Julian.

[jules]

Hi Julian

>

Why do you need the information copied? If you have the manufacturer id in your products record, that allows you to look up the name etc.

>

You can easily set Dataface up to show the manufacturer name on a select tab rather than the id, although the id is what is actually stored in the record.

>

Have a look at Example 3 here - http://framework.weblite.ca/documentation/tutorial/getting_started/valuelists

>

Neil

Thanks Neil, I need to keep the data there because a number of applications use it from that table and it is not worth it to redevelop the applications simply to "normalise" some fairly small tables.

I am looking for a quick (and dirty?) method of managing the data in these tables without having to develop full management interfaces - the data isn't worth it - however, I need to be able to hand-off management of the information to admin staff so I need a basic UI to do this, Dataface seems perfect for this.

Regards, Julian.

[jules]

>1. Using the widget:atts" title="Surprised" />nchange (or other relevant widget:atts: methods) you can assign your own custom javascript functions to be run when certain events occur on the widgets of your app.. You could write a javascript function that would automatically populate fields when one of the fields is changed.

Hmm, I cannot find any documentation at all on "onchange"? Is there any?

Cheers, Julian.

[shannah]

http://framework.weblite.ca/documentation/tutorial/getting_started/customizing (look down the page for examples of setting the style or other html attributes)

http://framework.weblite.ca/documentation/manual/fields_ini/widget_atts

http://www.devguru.com/Technologies/ecmascript/quickref/evhan_onchange.html

And since it is conceivable that others may want to do this same thing, I have just created a how-to that links all of these concepts together at http://framework.weblite.ca/documentation/how-to/custom_javascripts

Best regards

Steve

[jules]

Ahh, the light shines!

I should have worked this out myself I suppose, sorry about that.

I've now got - in fields.ini:
widget:attsnchange = "doJsFunction();"

with the JS code defined in .php
';
}
}
?>

And some code in javascripts.js in the folder for the table.

That does the job.


It might be a good idea to expand the manual page for widget:atts to include mention of javascript so that this appears more easily when searching:

"Any HTML attribute can be set using this convention." ->
"Any HTML attribute can be set using this convention including JavaScript event handlers."


One further thought though is that this would probably be better done AFTER submision of the changes using some custom code in .php such that the user only needs to select the manufacturer name and the code inserts the rest. Otherwise I need to create a hidden field in the form that the javascript can process and that would need to contain all of the manufacturers table - thankfully it is pretty small but that is still very inefficient. Alternatively, I could code some AJAX to go get the data required but either way the amount of work required would invalidate the reasons for wanting to use Dataface in the first place.

Thanks for your help so far, you've got an excellent tool here.

Regards, Julian.

[jules]

By the way, for anyone who has followed this thread.

I discovered a better way in the end. Because I am using MySQL 5, I can use SQL triggers to the the updates I need so I make most of the fields read only, the triggers update/add the extra information as required.

Regards, Julian.

[mikep]

Hey,

Those links that Mr.Hannah posted seem to be broken. Have they been relocated? I might need some of the information they contain.


Mike

[shannah]

I have added a redirect to fix the widget:atts link. The other ones appear to be working still.

-Steve