OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Datagrid based on two tables

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Tue Mar 28, 2006 4:02 am    Post subject: Datagrid based on two tables Reply with quote

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
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Tue Mar 28, 2006 4:06 am    Post subject: Reply with quote

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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Tue Mar 28, 2006 4:21 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Tue Mar 28, 2006 4:41 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Tue Mar 28, 2006 5:14 am    Post subject: Reply with quote

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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Tue Mar 28, 2006 6:44 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Tue Mar 28, 2006 8:22 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Thu Mar 30, 2006 1:55 am    Post subject: Reply with quote

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:
Code:
SELECT * FROM projects LEFT JOIN (clients)
   ON (clients.clientid=projects.clientid)


Wahur
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Thu Mar 30, 2006 4:55 am    Post subject: Reply with quote

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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Thu Mar 30, 2006 6:00 am    Post subject: Reply with quote

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

Wahur
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Thu Mar 30, 2006 6:22 am    Post subject: Reply with quote

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/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Sun Apr 02, 2006 6:27 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Linuxhippy
General User
General User


Joined: 04 Feb 2005
Posts: 26

PostPosted: Mon Apr 03, 2006 12:24 pm    Post subject: Readonly-Tables Reply with quote

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
Back to top
View user's profile Send private message
wahur
Power User
Power User


Joined: 22 Sep 2004
Posts: 62
Location: Tallinn, Estonia

PostPosted: Mon Apr 03, 2006 11:07 pm    Post subject: Reply with quote

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

Vahur
Back to top
View user's profile Send private message
Linuxhippy
General User
General User


Joined: 04 Feb 2005
Posts: 26

PostPosted: Tue Apr 04, 2006 8:45 am    Post subject: Thanks Reply with quote

Thanks a lot, works perfect Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group