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

just about calculation in fields

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


Joined: 25 Apr 2009
Posts: 28

PostPosted: Tue Sep 07, 2010 10:21 pm    Post subject: just about calculation in fields Reply with quote

Hello everyone,

Good or bad to export fields data in a spredsheet in order to get some calculations?
If yes, how to export them?

Thanks everyone for putting me on the right way.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Wed Sep 08, 2010 12:16 am    Post subject: Reply with quote

In OOo Base and Calc work well together and it is often beneficial to use Calc to perform complex calculations, including data pilot (pivot tables), on data stored in the database.

To export data from Base to Calc, first check that the database file has been registered in OpenOffice. Then open a new Calc spreadsheet and click f4. The "beamer" then opens above the spreadsheet showing all your registered databases. Open in the beamer the required database and table. It is then possible to drag and drop data as required from the beamer into the spreadsheet.
Back to top
View user's profile Send private message
mick.touraine
General User
General User


Joined: 15 Aug 2010
Posts: 23

PostPosted: Wed Sep 08, 2010 7:15 am    Post subject: Reply with quote

I don't suppose it's possible to move data back the other way from Calc to Base?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Sep 08, 2010 7:23 am    Post subject: Reply with quote

mick.touraine wrote:
I don't suppose it's possible to move data back the other way from Calc to Base?

No, of course not. Like you can get a working spreadsheet from PDF or like you won't get the tooth paste back into the tube.
Databases have a structure. Spreadsheets are entropic, a bunch of cells with arbitrary content.

Forms are the input tools for databases and you can attach forms to Calc sheets.
http://user.services.openoffice.org/en/forum/viewtopic.php?t=21099&p=96427#p96427
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
mick.touraine
General User
General User


Joined: 15 Aug 2010
Posts: 23

PostPosted: Thu Sep 09, 2010 12:55 am    Post subject: Reply with quote

Villeroy wrote:
mick.touraine wrote:
I don't suppose it's possible to move data back the other way from Calc to Base?

No, of course not. Like you can get a working spreadsheet from PDF or like you won't get the tooth paste back into the tube.

I take your point. However the question is not completely dumb, since after all it is possible to copy/paste from Calc to create a new Base table or add data to an existing one. Moreover, there are functions in Calc to allow parts of a sheet to work "like" a database. In principle then, if you can copy/paste into Base following a set of rules, why not set up a connection between a group of Calc cells and a Base table using similar rules?
But if you can't, you can't.
Back to top
View user's profile Send private message
danacol
General User
General User


Joined: 25 Apr 2009
Posts: 28

PostPosted: Mon Sep 13, 2010 4:15 am    Post subject: Reply with quote

..and...is it possible to get a spreadsheets database?
meaning a spreadsheets archive managed like or by a database?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Sep 14, 2010 12:53 pm    Post subject: Reply with quote

mick.touraine wrote:
Villeroy wrote:
mick.touraine wrote:
I don't suppose it's possible to move data back the other way from Calc to Base?

No, of course not. Like you can get a working spreadsheet from PDF or like you won't get the tooth paste back into the tube.

I take your point. However the question is not completely dumb, since after all it is possible to copy/paste from Calc to create a new Base table or add data to an existing one. Moreover, there are functions in Calc to allow parts of a sheet to work "like" a database. In principle then, if you can copy/paste into Base following a set of rules, why not set up a connection between a group of Calc cells and a Base table using similar rules?
But if you can't, you can't.


No, it is impossible to simply paste from spreadsheet. When the data do not match the requirements of the table you will have a lot of errors or a lot of clean up work due to inadequate field types.
When copy from Calc, I use to prepare a blank database table with all types, constraints and relations and then paste the Calc stuff.
IMHO this is the only way to get a clean database from the start.

Collection of useful spreadsheet formulas: http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=34056#p156196

I have never seen a huge spreadsheet database with consistent data.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
danacol
General User
General User


Joined: 25 Apr 2009
Posts: 28

PostPosted: Fri Sep 17, 2010 3:27 am    Post subject: Reply with quote

so...this is a real brainteaser for me.

Assuming you have a list of goods and you have to update frequently their quantities, how would you do?

I made a two tables db: the list of goods, and the list of processes for each good; I need to udpate goods quantity automatically at any process...

Thanks
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Sep 17, 2010 12:32 pm    Post subject: Reply with quote

danacol wrote:
so...this is a real brainteaser for me.

Assuming you have a list of goods and you have to update frequently their quantities, how would you do?

I made a two tables db: the list of goods, and the list of processes for each good; I need to udpate goods quantity automatically at any process...

Thanks


Any database in this world, this forum, your preferred online shop, thousands of desktop apps distributed on CD, are made for this. I bet there is not one single database application where any kind of speadsheet is involved (except as export option).

For the outlined reasons, a spreadsheet cell can never be an input control for a database record. Databases store records (rows) as the smallest unit, spreadsheets store cell values. The cell positions are arbitrary. When you link some record set into a spreadsheet and then move/sort around the cells at will (anything goes on the huge empty cell grid) there is no way to find the respective row set in the database.
If you really want this to happen, you've got to store some meta-information for every linked spreadsheet cell, something like:
"Database Name",DECIMAL(9,2) FROM "Table X" WHERE "Primary Key Field"=8
That would be the required info to generate some SQL UPDATE query, triggered whenever the cell has been modified. Then you need some code to test if the modified value conforms with the database field type or not.
This would be far beyond the normal purpose of a spreadsheet.
Spreadsheets are mere calculation tools able to import all kinds of data from databases, clipboard and from many file formats.
Once you imported the data you can do whatever spreadsheets can do best.
The cell grid as user interface may be very common, compared with form controls it is third class. It is very easy to get some data in and out, but it can be very hard to get correct data in and out of a spreadsheet (as we can see any day on the Calc forum).

Spreadsheets have nothing in common with databases. OK, they deal with tabular data, somehow. But they do not even have a concept of tables, just rectangles of used cells on a empty grid that may be interpreted as tables, but then the table is only in the head of the user.
Often the used cells on a sheet have a layout that has nothing in common with the layout of a database table (items in rows, attributes in columns).
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Sep 17, 2010 12:34 pm    Post subject: Reply with quote

danacol wrote:
so...this is a real brainteaser for me.

Assuming you have a list of goods and you have to update frequently their quantities, how would you do?

I made a two tables db: the list of goods, and the list of processes for each good; I need to udpate goods quantity automatically at any process...

Thanks


Any database in this world, warehouses, production lines, this forum, your preferred online shop, thousands of desktop apps distributed on CD, are made for this. I bet there is not one single database application where any kind of speadsheet is involved (except as export option).

For the outlined reasons, a spreadsheet cell can never be an input control for a database record. Databases store records (rows) as the smallest unit, spreadsheets store cell values. The cell positions are arbitrary. When you link some record set into a spreadsheet and then move/sort around the cells at will (anything goes on the huge empty cell grid) there is no way to find the respective row set in the database.
If you really want this to happen, you've got to store some meta-information for every linked spreadsheet cell, something like:
"Database Name",DECIMAL(9,2) FROM "Table X" WHERE "Primary Key Field"=8
That would be the required info to generate some SQL UPDATE query, triggered whenever the cell has been modified. Then you need some code to test if the modified value conforms with the database field type or not.
This would be far beyond the normal purpose of a spreadsheet.
Spreadsheets are mere calculation tools able to import all kinds of data from databases, clipboard and from many file formats.
Once you imported the data you can do whatever spreadsheets can do best.
The cell grid as user interface may be very common, compared with form controls it is third class. It is very easy to get some data in and out, but it can be very hard to get correct data in and out of a spreadsheet (as we can see any day on the Calc forum).

Spreadsheets have nothing in common with databases. OK, they deal with tabular data, somehow. But they do not even have a concept of tables, just rectangles of used cells on a empty grid that may be interpreted as tables.
Often the used cells on a sheet have a layout that has nothing in common with the layout of a database table (items in rows, attributes in columns).

Regarding you question on quantities, there are many solutions.
Have a look at my spontanious draft in Base:
Re: Using forms to add or subtract quantites from a table
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Sep 19, 2010 2:26 pm    Post subject: Reply with quote

Quote:
Regarding you question on quantities, there are many solutions.
Have a look at my spontanious draft in Base:
Re: Using forms to add or subtract quantites from a table

Well, my database seems to be broken in version 3.2, even after rebuilding the database from script. Used to work fine in 3.0 and 3.1.
Embedded HSQLDB becomes more and more useless.

EDIT: Deleting the "Stamp" fields from tables "Input" and "Output" makes the DB usable. The time stamp is not needed to demonstrate the inventory.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
danacol
General User
General User


Joined: 25 Apr 2009
Posts: 28

PostPosted: Fri Oct 01, 2010 12:10 am    Post subject: Reply with quote

Hello,

I apologize for the lateness in my reply, I was away...
I thank you for the db, I' ll take a look.

Stick around! Smile

Bye
Back to top
View user's profile Send private message
denwood
Newbie
Newbie


Joined: 18 Oct 2010
Posts: 1

PostPosted: Mon Oct 18, 2010 10:39 am    Post subject: registered? Reply with quote

Arineckaig wrote:
first check that the database file has been registered in OpenOffice.
How do I do this?
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Tue Oct 19, 2010 12:42 am    Post subject: Reply with quote

Quote:
How do I do this?

Welcome to the forum.

A complete explanation can be found at:
http://dai-videotutes.blogspot.com/2008/10/registering-database-in-openoffice_3025.html
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
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