Datagrid based on two tables

wahur · Posted: Tue Mar 28, 2006 4:02 am Post subject: Datagrid based on two tables

I have table 'projects' with 'projectid', 'clientid' and some other fields
I have table 'clients' with 'clientid' and 'clientname'

Now I need a datagrid form with columns like 'projectid', 'clientname' and others.
Tried making a query but this gives me read-only datagrid. I want to be able to insert new projects from there.

I think I saw similar thread just few days ago, but cannot find it now.

Wahur

DrewJensen · Posted: Tue Mar 28, 2006 4:06 am Post subject:

There has been a few threads on this.

Base does not have updateable queries working yet, is the short answer.

Your only real solutions is to use a main / sub form layout.
_________________
Blog - http://baseanswers.spaces.live.com/

wahur · Posted: Tue Mar 28, 2006 4:21 am Post subject:

Very bad news.
It is almost impossible.
If you look at http://www.hot.ee/vlokk/Projectmockup.odt you will see what I want to achieve (this is non-working mockup). http://www.hot.ee/vlokk/schema1.png gives you an idea of MySQL database that is supposed to do the actual job so you see I have more forms like that coming. If you really think this cannot be done, any form layout ideas are really welcome.

wahur · Posted: Tue Mar 28, 2006 4:41 am Post subject:

Tried it with a view. Somehow I thought views should be suitable for entering info, but mine does not work at all - it does not show any info that is already entered and it does not allow to enter anything. And it cannot be edited itself, either, while I can edit tables. Weird.

DrewJensen · Posted: Tue Mar 28, 2006 5:14 am Post subject:

Which release of MySQL are you using then?

If it is pre-5.0 then I don't think it supports updatable views, but could be wrong maybe there was an addon package of some type.

Anyhow, I haven't really exercised this feature with my installation of 5.0.18 but it is supposed to be there.

As for altering the form layout in Base to account for not having the abibilty to do this with queries...that isn't really as hard as you think. I am looking at your mockup and your schema - I don;t see anythig that would require this..but I am missing something I am sure..if you can tell me where the updatable query was to be used, perhaps I can take a 'swag' at a differeent approach.

But I know that a lot of folks will be happy when the developers finally get this working.
_________________
Blog - http://baseanswers.spaces.live.com/

wahur · Posted: Tue Mar 28, 2006 6:44 am Post subject:

Well, one layout idea came to me as well, but I have yet to try it. There is also q how much stuff fits into one page sensibly.

What this db/app should become is workflow/client/invoicing management for a small translation bureau. I use latest MySQL (I think the same that you have) with InnoDB as engine. 4-5 people max will be working with it, only on LAN.

This specific form mockup I linked is supposed to become a heart and soul of the app - workflow management interface. On the left side PM should see list of translation projects (number of active projects about 200) and be capable of filtering them based on project/job deadlines. And enter new projects.
If project is selected, jobs belonging to that project becomes visible in upper right jobs table. Because all of the job data does not fit into table without extensive horizontal scrolling I put some of it into separate fields under the jobs datagrid. As well as some financial basics just for viewing not editing, plus comment field. Buttons should be self-explicable.
From the layout point of view most important thing is making as much info available to PM without much scrolling around.

I must admit I actually considered making the interface with Qt but this appears to be a bit above my head cos I am not a coder and also have no funds available for hiring someone. So I will try to do it with Base forms and community help, with results being made available to everyone.

Wahur

wahur · Posted: Tue Mar 28, 2006 8:22 am Post subject:

Right now the one idea that occurs to me is to make one big read-only query-based table as MainForm and then one small subform for editing and entering data, but this sounds like really lousy design - in order to edit project data one has to pick the project in main table, then move over to subform for editing. It is almost certainly mouse-job and far from being intuitive.
Another option is to force the entry of client data first. Then datagrid column could be made a listbox which only chooses among the existing clients. This will be a nuisance if there will be many clients.
So if you, Drew have some idea how to do it better, come forward.
Because there is no actual need for PM table now, and PMs are precious few I could give it up and enter PMs with simple listbox, but clients are the tricky part.

Wahur

wahur · Posted: Thu Mar 30, 2006 1:55 am Post subject:

Looks like its becoming a one-man-thread.
Anyway, after some digging in this forum I found that
1) When I build a query using query editor and use left join I can update the resulting view. But query would be missing projects that have no client specified, and would show clients that have no projects. While those are rare instances, I would rather avoid this.
2) When I build query using query editor and use right join (best for me) then it ends up with syntax error and sends me reading MySQL manual

So I tried entering query manually. First surprise was that LEFT JOIN and RIGHT JOIN went into reverse, i.e. when manually entered it was LEFT JOIN that gave me required results. But trouble is, this result was still read-only!

I really wish someone explained what the hell is going on.

Example of simple test query I used:

DrewJensen · Posted: Thu Mar 30, 2006 4:55 am Post subject:

Wahur,

When it comes to this subject I am cmpletely lost also. Thought I had a handle on it, but just don't. As of now, for myslef, I have just stopped trying to use the feature.
_________________
Blog - http://baseanswers.spaces.live.com/

wahur · Posted: Thu Mar 30, 2006 6:00 am Post subject:

I think about filing another issue or two but this is such a mess I don't even know where to start...

Wahur

DrewJensen · Posted: Thu Mar 30, 2006 6:22 am Post subject:

there is already one issue ( actually I filed a second, but is has been marked as a DUP of the first ).

You may have a second one however where the behavior switches...

Bottom line, they don't know if we don't file - I suppose.
_________________
Blog - http://baseanswers.spaces.live.com/

wahur · Posted: Sun Apr 02, 2006 6:27 am Post subject:

To everyone dreamin' about updatable queries/views to MySQL.
MySQL manual says loud and clear: view cannot be updatable if it includes join. End of story. There are many other conditions that do not allow view to be updatable. Manual makes it quite clear that updatable views are relatively new feature in MySQL and it gets better in the future.
This info is valid as of MySQL version 5.0.18.
So guys, OOo devels cannot give us what does not depend on them.

Wahur, moving over to check PostgreSQL.

Linuxhippy · General User Joined: 04 Feb 2005 Posts: 26

Hi there,

Did anybody find a way for doing this (showing the result of a join of many tables inside one grid control) for readonly data?
I have a table customers, goods and sells. Sells is the table which contains the PK of goods and sells and a price.

What I would like to do would be to display sells.price, customers.name and goods.name where sells.custkey == customer.custkey.

lg Clemens

wahur · Posted: Mon Apr 03, 2006 11:07 pm Post subject:

Just make a view and do your grid control based on this view.

Vahur

Linuxhippy · General User Joined: 04 Feb 2005 Posts: 26

Thanks a lot, works perfect Smile