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

Drawing in a value from another table
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
mick.touraine
General User
General User


Joined: 15 Aug 2010
Posts: 23

PostPosted: Sun Aug 15, 2010 1:19 pm    Post subject: Drawing in a value from another table Reply with quote

I'm working on a database which is very similar to "orders" application where each order line references a particular product with its description and price, except in this case it is "work items" carried out at particular dates rather than order lines. So, I have a "work items" table with a foreign key constraint on a "work type" table. Each "work type" has a description and a price. Each work item has a date, various other associated fields, and must carry the description and the price. If you think of a classic "order line" then my "work item" would be the order line, and the "work type" would be a product description.
I have successfully designed the tables and linked them. I have a form with a list field where I can select the "work type", and have the appropriate work type id included in the "work item" table, so that I can also produce a report which displays a list of work items, with the appropriate description and price, as well as the other stuff.
This is fine, except for one thing: I can imagine that the price of a "work type" could change and in this case, I do not want to change the associated price in all the work items that have already been recorded. So, what I want to have in my form is a selection list of work types where clicking on a work type would:
    1) Cause the work item to be created with the corresponding work type id to link the work type to the work item (this works already).
    2) Read the price of the work type, and store it in a "price" field of the work item (this is my real problem).

This way, the price will be permanently associated with the work item and will not change in the work item table even if it is changed in the work type table.
Also, I hope to do all this without using macros.
I would be very grateful for any help anyone can give and hope that my description is clear.
Back to top
View user's profile Send private message
SimpleSi
General User
General User


Joined: 08 Aug 2010
Posts: 8

PostPosted: Sun Aug 15, 2010 3:28 pm    Post subject: Reply with quote

Hi Mick,
This is just the problem I have been wrestling with.
With quite a bit of help from this forum I'm now successfully going down the macro route - take a look at this post.

If you don't want to use macros then dacm has posted another solution there.

Now, I'm a novice at macros but, if you can access and create a macro (even if you don't know much about the coding), then it's worth taking a look at Romke's 'gridtable' database, in particular the 'gridtable' form. With minor modifications, such as changing table & field names, you could copy the relevant macros.

Good luck - I was banging my head over this for a few days!

Oh - ignore the table relationships that are near the top of the linked post, they are no longer applicable.
Back to top
View user's profile Send private message
mick.touraine
General User
General User


Joined: 15 Aug 2010
Posts: 23

PostPosted: Sat Aug 21, 2010 7:17 am    Post subject: Reply with quote

Thanks for the pointer. I confess I'm still really confused about how all this works and ties together. I've looked at Romke's gridtable for example, but I'm really stuck working out where the macros are fired from, and then exactly what they do.
One very frustrating thing is that I can't for the life of me find a straightforward SIMPLE documentation of the different objects and methods that I could use on a form and to get data out of the database.
Grateful for any help...
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Aug 21, 2010 11:58 am    Post subject: Reply with quote

Hello

Villeroy does have given several examples in which he use only SQL and no macros. That means you have to understand how the forms are working. The forms is is including the SQL and the properties how you can change them.

This is not easy but a lot easier then learning macros.

The properties you always to learn for making a good form. I think when you start to search on this forum or the other forum and then special on the OOo-base parts and Villeroy then you will find a lot information.

When you follow the method of Villeroy then you to change maybe the forms.
I hope this helps you.

Romke
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Sun Aug 22, 2010 1:20 am    Post subject: Reply with quote

Quote:
Read the price of the work type, and store it in a "price" field of the work item (this is my real problem).

Please ignore this post if you have already found the solution. It should be possible to achieve your aim without resort to macros. The bound field for your "price" form control should be the "price" field in the Work Item table. This "price" form control should be a list box, the contents of which are supplied by a SQL command such as:
SELECT "WorkTypeName", "WorkTypePrice" FROM "WorkTypeTable
Keep the default Bound Field as 1.

This list box displays a list of Work type names from which a selected Name will load the relevant Price data (as opposed to a FK) into the bound field in the Work Item table.

In effect you will now have two list boxes: the existing first one fills the FK (Work Type ID) linked to the PK of the WorkType into one field of the Work Item table, the second one may appear similar but is filled with a different SQL command so that it fills the "Price" field with the actual data from the WorkTypePrice field of the WorkTypeTable. This means that any subsequent changes to the price field in the WorkTypeTable will have no impact on the data in the "price" fields of records previously stored in the Work Item table.

Selecting the same Work Type Name from two separate list box form controls may appear tedious and inefficient. It is, however, one method of avoiding resort to a simple macro that fills the "price" field automatically (as well as the WorkType ID field) when triggered by an event from the first (and thus only) list box.

I fear my ill prepared explanation may merely confuse further, so do please come back if clarification is required. On the other hand if my suggestion is way off beam please accept my apologies for wasting your time and that of others.
Back to top
View user's profile Send private message
SimpleSi
General User
General User


Joined: 08 Aug 2010
Posts: 8

PostPosted: Sun Aug 22, 2010 4:28 am    Post subject: Reply with quote

Hi,
Below is an image showing Romke's 'gridtable' which, in part, does what Arineckaig is suggesting.


The form source is the 'Order Details' table. But, you will notice that the 'Order Details' table does not have a field called 'Name'. The 'Name' column has been added to the form by right clicking on a column heading, selecting 'insert column' and choosing 'list box'.

In the 'general' tab of this new list box the column has been named 'Name'. You can see that, under the data tab, 'ProductID' has been selected as the data source (same as the ProductID column in the table), and the 'boundfield' is 1 (which is the ProductID field). However, the 'type of list' field has been chosen as 'sql' then, by pressing the ... next to the 'List content' the 'sql command' dialogue window is opened. By clicking on 'Insert' in the menu bar, the 'Products' table has been added and the 'ProductName' & 'ProductID' fields by double clicking on them.

Now you will be able to choose the required product name from the list that appears in the 'name' field and the 'ProductID' will automatically be filled in.

Romke then uses macros to fill in values in other fields. If you have a look at the 'Events' tab of the 'Name' field, you will see that he has macro called 'afterupdateartikel' attached to the 'after updating' event. The only adaptation that I had to make to this macro was to change the name 'UnitPrice' to the name I was using. (he does use other macros for other fields in a similar way).

If you don't want to play with the macros then, I think, Arineckaig is suggesting that you could make another list box in a similar way to fill up another field.

Hope this helps.

Now, back to my problem trying to understand another macro ...
Back to top
View user's profile Send private message
mick.touraine
General User
General User


Joined: 15 Aug 2010
Posts: 23

PostPosted: Mon Aug 23, 2010 4:33 am    Post subject: Reply with quote

Perhaps it would be clearer if I explained what I am trying to do. First of all, here is an image of the relations.


Now, here is a view of the form I am creating (not complete, not all the fields are on it yet)


So as you can see what I want to do is actually very similar if not identical to Romke, although I am not using a grid (though thinking about it, perhaps a grid would be better).
What I want is, when I select a "Work item type" this will put the Item_ID in the WorkItems table, so that in queries I can pull in the name of the work item. However I want the price to be loaded from the Item_Pricelist table and stored in the WorkItems table, to take care of the possibility of prices changing.

So it seems to me that there are three possibilities:
    1. Use the solution proposed by Arineckaig. While this uses no macros the problem is that it obliges the user to select the same thing twice which is not really satisfactory from the user point of view.
    2. I could include a "start" and "stop" date in the Item_Pricelist table, and then adjust my queries so that they handle this. But this is a bit complicated for my user's needs.
    3. Finally, use a macro. Thanks to SimpleSi for explaining so carefully how Romke does this, but there are still some things I don't understand (I find macros really confusing), so I will expand below.


The macro is called with two parameters: Standard.Order.afterupdateartikel (document, Basic) - yet in the definition it only has one: sub afterupdateartikel(oevent as object). Why is this?

I find it really hard to work out what are the relationships between the different objects, and above all how could I generalise this macro. Where is there some documentation that tells me what are the objects that exist in a form, what are their methods and properties?

Anyway, I will try to do this and see what happens.

Another question:As you can see in the form I have an item date. I would like to set the item date to default to today's date but I cannot see in the "Control Properties" how you would do this. Any advice would be welcome!
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Aug 23, 2010 11:31 am    Post subject: Reply with quote

Hello

I think it make no great difference when you use no grid table in your form.

For dates see this link.

For PDFs you can study see this link.



Romke
Back to top
View user's profile Send private message
mick.touraine
General User
General User


Joined: 15 Aug 2010
Posts: 23

PostPosted: Tue Aug 24, 2010 12:15 am    Post subject: Reply with quote

The only difference using a grid table is a matter of usability - otherwise, logically speaking, I agree that the two are equivalent.
I think it is crazy that a date field cannot have something like "$TODAY" in the default value of the field control.... What chance of an enhancement?
And finally, I managed to work my way through the macro bit by bit, but it was really extremely difficult to determine the properties and methods of the different objects. I can't believe that there is not some simple documentation that, for example, illustrates the inheritance tree for a Form with the different sub-objects etc.
For example, it is hardly intuitive that you need to put this:
Code:
oUnitPrice.BoundField.Value=oResultset.getDouble(1) 'give returnvalue

instead of this (which does not work):
Code:
oUnitPrice.Value=oResultset.getDouble(1) 'give returnvalue

While there are some very good tutorials around the place, I think there is a lot more needs putting into the Wiki. Perhaps now that Oracle has taken over things will look up a bit....
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue Aug 24, 2010 1:56 am    Post subject: Reply with quote

Hello

For people who have study the grid example they have maybe seen I use sometimes the method commit. Using the method commit give the possibility to stay short to the form. The reason I want stay short to the form is: I believe then that the macro code is more easy.

This more easy is true unless you ask the explanation of all methods and properties. I think for a lot of users this method can help them when they want not learn the working of objects and there service and methods.

What must the user learn?
Most of the time the user want change a field value. Then the user have to discover what property use that control: some most common properties are
a) string
b) Text
c) value
d) date
e) time

I use also a lot of events. It is the user who must define the event and there action. So the user must understand how to use an event.

When the user want learn to understand how the API is working then start study the BASIC tutorial. This is long way and it cost me a long time for I can really understand it. I believe most user on this forum learn faster as I do but also for them it is maybe to long for making a simple form. For that reason I do always point to easy forms of programming.This can be my method but also the method of Villeroy or Benitez.

And YES basic programming is not intuitive. This is special true when you want use the API. I think this will not change.

Romke


Last edited by RPG on Tue Aug 24, 2010 2:29 pm; edited 1 time in total
Back to top
View user's profile Send private message
FreewayFred
Power User
Power User


Joined: 22 Feb 2007
Posts: 85
Location: Wisconsin USA

PostPosted: Tue Aug 24, 2010 5:35 am    Post subject: Reply with quote

It seems to me that your are going through a great deal of effort to avoid simply entering "work type" prices manually.
Back to top
View user's profile Send private message
mick.touraine
General User
General User


Joined: 15 Aug 2010
Posts: 23

PostPosted: Thu Aug 26, 2010 12:31 am    Post subject: Reply with quote

RPG wrote:

For people who have study the grid example they have maybe seen I use sometimes the method commit. Using the method commit give the possibility to stay short to the form. The reason I want stay short to the form is: I believe then that the macro code is more easy.
.......
I use also a lot of events. It is the user who must define the event and there action. So the user must understand how to use an event.

Just a question on this - I don't know what "commit" actually does. Where is it documented? I would also like to use Events more - where are they documented? Also, there is a thing that seems odd to me: I set up my event just like Romke and it works the same way. My "event" updates the workitem price - but only AFTER I have exited from the workitem type field, whereas IMHO it ought really to do the update when I change the value of the workitem field but before I exit it - at least I think it would be nice to do this! But I can't see any way that would be possible.
FreewayFred wrote:
It seems to me that your are going through a great deal of effort to avoid simply entering "work type" prices manually.

Well that's precisely the point of a lookup table! If the user was forced to enter the price manually then he would have to look up the proper price in a paper listing and take care not to make a mistake.
Normally in OO a list control allows you to look up one column in a table, choose a key value, and include the value of another column in your form. That's precisely what I want to do, except that I want to bring in TWO values. And apparently the only way to do this is with a macro.
----------------------------------------
Also I just found this useful looking Object Inspector extension. Does anyone have any experience with this?
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Thu Aug 26, 2010 2:47 am    Post subject: Reply with quote

Hello

When you want set a date in datecontrol I think the preferred method is using the database engine. When you define a database then you can set a default date in the database.Special if you work with a database who is not stored on the computer where you are working. But most question on this forum are for a single computer. So there is always one date for the database and the computer. When you use the database engine it is still the most easy method.

When you want use an other method it is not easy then you have to learn a lot of thing. It is easy when you get the code. And there is nothing wrong that I give my solution to you. I do search also a lot on internet for searching other problems with my computer.

This two methods does set a date.

Code:
dim ToDay
ToDay= date ' Give the sytem date of this computer
ToDay=CDateToIso(ToDay) ' Convert the system date to a long value in iso format
ToDay=format(ToDay,"0000-00-00") ' Convert now the iso format to a string in iso format
oDatum.boundfield.updatestring(ToDay) ' Now update the date field

Code:
dim ToDay
ToDay= date ' Give the sytem date of this computer
ToDay=CDateToIso(ToDay) ' Convert the system date to a long value in iso format
oDatum.date=ToDay ' Now update the date field
oDatum.commit 'Transfer a value from the control to the database field


But why study so long when you can easy define it with SQL. I knew SQL is also not easy but it most of the more usefull.

The question why it is so different I have no answer. Every time you see a point in a command line you go to an other service.

The next is as far as I understand it and it can contain errors there my understanding is wrong. The basics in the UNO API are interfaces. For a service most of the time several interfaces are bound to one service. This means that one interface can belong to more then one service.

So I believe for a service they have made a choice what interfaces you can need for doing your work as programmer. When you want use OpenOffice.org then you have to understand what you can do with OpenOffice.org and special with the part what you are using on this moment.

You ask several time where are the documents. All the little parts are documented in detail but only to understand for people who have study it a long time. Also for me it is real difficult to understand the little parts. It is like looking to tubes of paints in a shop and asking how can I make a nice painting.

http://api.openoffice.org/docs/common/ref/com/sun/star/form/XBoundComponent.html

For understanding the API I made a lot of text files for learning all the Service and interfaces. Now after some years I slowly understand how it is organized. You can see that the two methods are not in the link I did point to. That makes clear I did learned new things.

Romke
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Fri Aug 27, 2010 4:09 am    Post subject: Reply with quote

Hello

I think it is good to bind this thread to an example of Arineckaig.
You can find there an example and a link to a PDF what explains the navigation toolbar.

Romke


Last edited by RPG on Wed Sep 01, 2010 3:24 pm; edited 1 time in total
Back to top
View user's profile Send private message
mick.touraine
General User
General User


Joined: 15 Aug 2010
Posts: 23

PostPosted: Mon Aug 30, 2010 2:15 am    Post subject: Reply with quote

RPG wrote:
When you want set a date in datecontrol I think the preferred method is using the database engine. When you define a database then you can set a default date in the database.

Thanks for your suggestions - I will try to use this. However, I have two questions:
1) I tried to set today's date as default in the Table design, but cannot work out how to do this. According to the HSQLDB documentation, there is a function called CURRENT_DATE which should return the current date, and if I have understood properly should be acceptable in a table definition. However, it is not accepted in the table design, which simply ignores it completely. So I don't know how you could do this in OO.
2) If you set the default in the database definition, will it show up on the form? If not, then I think this is not good. The user needs to see the default date displayed, then alter if necessary. But there should be a display of the default date, no doubt about it.
I would like to bind to Arineckaig's solution - but I'm confused which one?
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
Goto page 1, 2  Next
Page 1 of 2

 
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