[Kexi] Using Kexi databases/queries in KWord (or other KOffice apps)

[Sebastian Sauer]

Hi Matija,

On Saturday 29 July 2006 20:25, Matija Šuklje wrote:
> Progress I made so far:
> - updated the table for different types of work with their prices
> - added a table with names of "workers"
> - made a query that shows for a specific worker what jobs he's done from a
> specific date to a specific date
> - imported the query into KSpread (using "export to .csv" -> open up
> the .csv -> copy-paste content to a cell in KSpread -> tell Kspread to make
> a table from the data in that cell)

Sounds like nice progress :-)

> Problems I've encountered:
> - the import via "import database" failed saying that I don't have the
> right Qt database support

hmmm... that sounds as you don't installed the matching qt-sql library...
At debian that's done with a simple "apt-get install libqt3-mt-mysql". Don't 
know how other distributions may named the module.

> - the "export to .csv/.txt" and import "from text" messed up the special
> chars, because KSpread seems to ignore the encoding (was: utf8)

IIRC there was a commit just ~2 days ago that fixed this :) So the upcoming 
KOffice 1.6 will have that fix included.

> Things I believe are possible, but have no clue how to do:
> - get a calculation on how many times (in the beforementioned query) a
> certain type of work (= "water", "mow", "hour of other work") occurs.

Well, the query itself still depends on how you designed the table. If you 
have as example a table named "mytable" and like to sum all the numeric 
values of the column "mynumvalue" together where the column "mymonthcolumn" 
is set to 11, just do something like
SELECT SUM(mynumvalue) FROM mytable WHERE mymonthcolumn = 11;

There exists a wide range of howto's that outline the SQL-syntax which isn't 
that complex. As example
http://sqlzoo.net/
http://www.geocities.com/SiliconValley/Vista/2207/sql1.html
http://www.1keydata.com/sql/sql.html

You may like to provide some more details about the design of the table (so, 
tablenames, columns, datatypes)... Kexi includes the "Project Documentation 
Generator" python-script (Tools=>Scripts) which generates a HTML file that 
contain those kind of informations.
Another alternate may to just attach the sqlite-file or export the 
mysql-schema. That would make it easier to answer the specific questions :-)

> - bind/depend tables together - e.g. that in the main/"work" table the
> column "worker" can only contain a name from the table "workers" and that
> the column "type of work" can only contain a type of work listed in the
> "types of work with pricelist" table. a pull-down menu and/or
> auto-completion would be awesome :D

Guess I really need to look at the tables before I am able to understand this 
100%... So far as I understand it;
* to connect the work-table with the worker-table, just add to the work-table 
a foreign-key worker-id that points to the primary key of your worker-table
* do the same with the type_of_work connection...

> - make a script (in Kexi maybe) to create the tables from the queries
> automagically :]

Create the tables from the queries? You mean to save the result of such a 
query direct into a table?

-- 
Sebastian Sauer aka dipesh[sebsauer]
http://www.dipe.org/public_key.asc
Fingerprint: 8F1E 219B 16E6 4EC7 29CC F408 E193 65E2 9134 2221
Coder in http://www.koffice.org && http://www.kmldonkey.org