I want to create an invoice DB, but....

Discuss the database features
Post Reply
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

I want to create an invoice DB, but....

Post by kaustein »

I think that I rather get my prostate checked by Capt. Hook!

I am having one heck of a time in trying to create an invoice DB for my small service company. I have used ms works in the past with absolutely no problem. But with OO, it is very hard just to start.

With ms works you created the fields, defined what you wanted them to do, and then moved them about the form to create the DB, but I just can't seem to figure out even how to get started that makes sense.

Any help. OBTW, I even bought a book about OO, but no help.

Thanks,
K :?
OOo 2.4.X on Ms Windows XP
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: I want to create an invoice DB, but....

Post by r4zoli »

Good tutorial from sheepdog on Base.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

Re: I want to create an invoice DB, but....

Post by kaustein »

Will check it out and thanks!
OOo 2.4.X on Ms Windows XP
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

There is Invoice-Easy that works with Calc. Are you sure you wouldn't be better using something ready-written and web-based, anything from CitrusDB to Compiere, say?
LibreOffice 7.x.x on Arch and Fedora.
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

Re: I want to create an invoice DB, but....

Post by kaustein »

Robert Tucker wrote:There is Invoice-Easy that works with Calc. Are you sure you wouldn't be better using something ready-written and web-based, anything from CitrusDB to Compiere, say?
I do about 20 invoices a month now and I have used dbs before. It is eaiser to manage the invoices with a db rather than a spreadsheet. If I can find a ready-made oo db that really would be perfect! I will check out the links
OOo 2.4.X on Ms Windows XP
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

Re: I want to create an invoice DB, but....

Post by kaustein »

Does the citrus db allow me to create the invoice and then print it from an online db? Or am I downloading the invoice db and just running via OO. There is no screenshot of an invoice.
OOo 2.4.X on Ms Windows XP
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

I have been looking more closely at phpCoin than CitrusDB. You need to run both on a web-server (LAMP, WAMP etc) or web-hosting service. The database will be on the same machine as the server (or on one it can connect to). I don't think I've any real doubt that there will generally be some means of editing the exact nature of the invoice. To quote from the home page of CitrusDB:
Stores general billing information and necessary information about the customer's billing cycle. Can bill on any billing cycle needed, one-time, monthly, bi-monthly, quarterly, 6 months, yearly, etc. Billing is able to provide batch credit card exports, printed invoices, or emailed invoices.
Admittedly I don't know quite what form it will produce the printed invoice (I've been looking more at emailing them).

OpenOffice would not be involved.
LibreOffice 7.x.x on Arch and Fedora.
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

Playing with the phpCoin demo I found it just created a printable web page.

Image
Last edited by Robert Tucker on Tue Dec 16, 2008 4:38 pm, edited 3 times in total.
LibreOffice 7.x.x on Arch and Fedora.
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

Re: I want to create an invoice DB, but....

Post by kaustein »

Looks like I am in over my head.

Downloaded the citrus db program but can't unzip it. Downloaded gnu zip program, but can't get it to work.
OOo 2.4.X on Ms Windows XP
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

PowerArchiver 6.1, 7-zip and Winzip include the gzip compression code and can decompress .gz and tar.gz files. Win-GZ can compress and decompress files in gzip format. Please note that gzip, 7-zip, PowerArchiver 6.1 and Win-GZ are freeware but you must register Winzip and PowerArchiver > 6.1 if you use them regularly.
http://www.gzip.org/
LibreOffice 7.x.x on Arch and Fedora.
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

Re: I want to create an invoice DB, but....

Post by kaustein »

Again I thank all of you for your help, but nothing is extracting the citrus file. I downloaded the zip gnu and nothing is working
OOo 2.4.X on Ms Windows XP
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

Perhaps try with 7-zip?

http://www.ofzenandcomputing.com/zanswers/1072

phpCoin comes as a .zip file.
LibreOffice 7.x.x on Arch and Fedora.
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

Re: I want to create an invoice DB, but....

Post by kaustein »

Great, I finally got it extracted, but now I can't get the program to run. Can't find the .exe to run it.
OOo 2.4.X on Ms Windows XP
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

It won't install quite like that. You need to have something generally referred to as WAMP installed on your computer (It stands for Windows, Apache, MySQL, PHP/Perl). An easy way to install it is with XAMPP. Probably, initially at least, it might be simpler to create an account on a free web host, OOOWebHost or Bytehost for example, and upload CitrusDB to it. The CitrusDB instructions are at:

http://www.citrusdb.org/help.html

Since my last post I came across another I have not tried so far, Simple Invoices

If you are sure you don't need the flexibility of having the system on a web-server, probably there are other software packages which will do the job on a single machine or local network. I thought most accounting systems could output an invoice – maybe I'm wrong, I'm not an accountant.

If you find anything that works specifically with OpenOffice, please let the forum know!
LibreOffice 7.x.x on Arch and Fedora.
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

LibreOffice 7.x.x on Arch and Fedora.
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

# Export to Excel/Word/OpenDocument format

* Simple Invoices allows to the export of an invoice from the Quick View to Excel, Word and the Open Document formats
* The default export formats are set to .xls(Excel) for spreadsheets and .doc(Word) for word processors
* These defaults can be changed in the config/config.php to be the Open Document formats, .ods for spreadsheets and .odt dor word processors
From Help pages of "Simple Invoices".
LibreOffice 7.x.x on Arch and Fedora.
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: I want to create an invoice DB, but....

Post by Arineckaig »

I am having one heck of a time in trying to create an invoice DB for my small service company. I have used ms works in the past with absolutely no problem. But with OO, it is very hard just to start.

With ms works you created the fields, defined what you wanted them to do, and then moved them about the form to create the DB, but I just can't seem to figure out even how to get started that makes sense.
If you are trying to convert from a MS Works invoice DB, I would recommend a step by step approach. The structure of OOo Base differs significantly from Works Db, and I found the conversion learning curve quite steep. It is ultimately rewarding in that Base will give you more flexibility and scope, but you may find as an intermediate step that a combination of OOo Calc and OOo Writer more readily replicates the MS Works DB which is essentially an interaction between the Works spreadsheet and word processor.

Until you have gained experience with the potential of OOo Base, you may find it easier to copy the Works data from its "list view" (Shft+f9) and paste it into a new OOo Calc spreadsheet. You will need to insert a new first row in which to type the field names that will not have copied across.

To reproduce the Works "form" (f9), create a new OOo Writer text document and use its "mail merge" abilities to create the layout of your Invoice Form. You may find these two sources helpul guides:

http://wiki.services.openoffice.org/wik ... ata_source

http://openoffice.blogs.com/openoffice/ ... mpler.html

There is a limitation to this method in that any changes or additions to the database can only be entered in the spreadsheet. In due course you may need to copy the data from the Calc spreadsheet into a Base table and recreate a Base form that will permit entry and edit of the source data. In the meantime, however, a Writer document reading data from a Calc spreadsheet can be made to reproduce a Works DB and is good experience for transfer later to Base.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: I want to create an invoice DB, but....

Post by Arineckaig »

My apologies but I overlooked that you are using OOo 2.4. The first user guide link in my last post should have been:

http://wiki.services.openoffice.org/wik ... Mail_Merge
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: I want to create an invoice DB, but....

Post by kabing »

I just ran across this invoice database created for OOo, available at sourceforge.net. I don't know anything about it, except that it exists.
kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

Re: I want to create an invoice DB, but....

Post by kaustein »

will check it out! Thanks
OOo 2.4.X on Ms Windows XP
kaustein
Posts: 18
Joined: Tue Dec 16, 2008 5:32 am

Re: I want to create an invoice DB, but....

Post by kaustein »

It was web basis and it didn't work.

There must be something I just don't understand. MSworks has a basic DB program that is a piece of cake to use. I am surprised that there isn't a simply db for oo that isn't web based. There are spreadsheets for everything, but no dbs at all. :cry:
OOo 2.4.X on Ms Windows XP
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

No professional database developer will design anything in Base, but you may insist on connectivity through ODBC...
Villeroy

http://user.services.openoffice.org/en/ ... 47&p=62561

There is an invoice for OpenOffice at:

http://documentation.openoffice.org/Sam ... index.html

Presumably this will work with the OpenOffice Database or any other that can work with OpenOffice. The last time I tried working with OpenOffice database connections (a few years ago now) connectivity wasn't always so reliable.
LibreOffice 7.x.x on Arch and Fedora.
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: I want to create an invoice DB, but....

Post by Robert Tucker »

GnuAccounting - an application that embeds OpenOffice to create invoices, credit memos, delivery notes, bills etc.
http://gnuaccounting.com/
LibreOffice 7.x.x on Arch and Fedora.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to create an invoice DB, but....

Post by Villeroy »

kaustein wrote:It was web basis and it didn't work.

There must be something I just don't understand. MSworks has a basic DB program that is a piece of cake to use. I am surprised that there isn't a simply db for oo that isn't web based. There are spreadsheets for everything, but no dbs at all. :cry:
There is no "DB program" in OpenOffice.org. There is just a collection of software tools to connect Writer and Calc with a wide variety of tabular data sources. Even the native, self-contained database which can be generated from scratch is not what it seems to be.

Because Base is so generic by intention, you can not do much with it unless you understand some fundamental concepts of database development which apply to most databases on the market. Please understand that tools like Base, even the better ones, are not made for "the end user". You can use a database frontend to create something which finally (and hopefully) can be used easily by an assumed end user, so it's a development tool..
This old-fashioned, document-oriented desktop software has nothing in common with web-based database frontends, as used in web-shops or exposing this forum's database. Base utilizes office documents to present database contents and form controls.

From my personal point of view, Base can be used in 3 stages
Stage 1: Access tabular data from almost arbitrary sources and serve them to Writer and Calc for pretty printing, spreadsheet analysis, serial letters, pivot tables, charting and many other things. This requires read-access to the underlying source of data together with slightly advanced end user skills, making Base an extremely useful part of this office suite because you can access your data beyond the limitations of supported file formats. At this stage ...
- Database creation has been done by means of some other tool.
- Input and maintenance of content is managed by some other tool.
- This office suite is just an additional option for database output among others. Optionally, you may use queries to limit the output and you may use more or less advanced tools for report creation. I think of the Sun Report Builder add-on which can perform complex tasks easily, extending the presentation of data beyond raw data access (formatted output to a page-oriented layout with charts, pictures and stuff).

Stage 2:
Access tabular data like in stage 1 while using the office suite for write access as well. This requires a writable data source, something that provides record cursors, a "real database" served by a database server, a set of dBase files in a directory at least. At this stage ...
- Database creation has been done by means of some other tool.
- Input and maintenance of content (not the database's structure!) can be handled by means of form controls embedded in a office document which itself may be embedded in a containing database document.
At this stage OOo provides quite a usable tool set if you have a reliable connection to a consistent database backend (a structure of well designed relations).
While only Writer forms can be embedded in a Base document, you can attach form controls to Writer documents, Calc sheets, even to pages in Draw and Impress.
In my honest opinion you need to understand how the given set of form controls is supposed to work with each other. You need to understand the hierarchy of forms, subforms, controls and how to bind them to the record sets of the underlying database. Unfortunately, there is no concise documentation on this. Numerous How-Tos can not reveal the overall concept which could enable you to recombine existing features in a creative manner.

Stage 3:
Additionally, you want this office suite to create and maintain a brand new "database document" from scratch which pretends to be "just like any other office document", particulary like a file-based, proprietary MS database. At this stage the developers climbed too high and they have chosen the wrong stairway, following loud public demands in the years 2003 to 2005.
No, it's not what it pretends to be, particularily it's not about office documents made of a free and open file format to be "loaded" by some appropriate application.
I'm writing about the infamous, native, self-contained, single-file "database document" being a major cause of confusion and frustration, starting with menu:File>New>Database...[x]New database ..., ending with a well hidden server connection to a third party database server called "HSQL" which serves your database from a temporary(!) directory. Recently I tried to outline the concept and it's implications in [Solved] Emailing database as an attachment problems
Wrapping everything into a single zip-file, against the concept of the underlying server technology, gives one advantage vs. too many disadvantages as I tried to outline in reply to DrewJensen http://user.services.openoffice.org/en/ ... 756#p53756
You can get more out of Base once you understand how to break out of the odb-container.
It would be so much easier for everyone if a portable database could be shipped like a package, more like an extension with forms, reports queries and a configured connection, optionally with table data in a commonly accessible file format such as dBase or SQLite, enabling relations, indices, cursors.

The current odb container behaves like an extension that gets installed every time you "open the database document". It has to be repackaged when the database is going to be closed.
Abandon all hope when you create this type of database for the sake of security, safety and performance, let alone concurrent access from various clients. This is a caricature of a database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to create an invoice DB, but....

Post by Villeroy »

Regarding your request for a database of invoices, I suggest a pencil and a sheet of paper as the most important tools to start with.

So far I can see 3 tables:
"Clients" having one record for every single client with a unique client ID and possibly special unspecified clients (e.g. cash-clients in a store) [Fields: ID, Name, Address, phone, delivery address, mail, birthday,...]
"Articles" having one record for every single article or service you have to offer [Fields: ID, name, price, vendor, colour, special tax-rates like VAT,... ]
"Invoice" having one record for every single act of sale with unique ID, a foreign key pointing to a client, a time-stamp field, a point of sale (foreign key to a separate POS-table?), a salesman (foreign key to a separate staff-table?), ...
Note the type of every field, if it is supposed to be mandatory or optional and a line to an equivalent field in another table if the field serves as a foreign key.

Since every invoice is about selling one or more articles (it's a many-to-many relation), we need another table to map multiple Article-IDs to multiple Invoice-IDs. Then I would add another helper-table holding the ID of the current invoice to be printed from a report.

Having the right tables with appropriate field types, relations and constraints we can start building the respective forms, so you can quickly lookup one client and multiple articles belonging to one invoice.

Have a look at DrewJensen's movies_2.odb with a form where each movie belongs to one genre (one-to-many relation) and another variant where each movie belongs to one or more genre (many-to-many relation).
The important part is the structure of table fields and relations. The forms are built on top of that structure.

Finally you may liberate the structure of your embedded test-database in order to connect the same forms and reports to a real database for adults.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
pjwalkerpj
Posts: 35
Joined: Fri Oct 24, 2008 8:33 am

Re: I want to create an invoice DB, but....

Post by pjwalkerpj »

Villeroy,
Thankyou for a system design for invoicing. However what about the rest of the answer to the question of "I want to create an Invoice from Base". An example of all of the tables you mention with dummy data and the exact connections between all of the tables required.
I think you assume that we all have your in-depth knowledge of the operation of Base.
Could I suggest that you review the excellent answers provided by Drew Jensen on how to actually create a data base that does what I am asking rather than pontificate about what you think Base doesn't do which is not very helpful.
I look forward to the rest of your answer in due course
Peter J Walker
Practising Professional Engineer
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to create an invoice DB, but....

Post by Villeroy »

I have absolutely no in-depth knowlege on databases. 10 years ago I grasped some of the most fundamental basics while designing 3 Access databases, working as user with some other systems and my understanding of web-based databases (like this forum) is limited to the results of some superficial experiments with LAMP (Linux,Apache,MySQL,PHP). I dumped some of my superficial experiments with Base into this forum. Today I failed to create something obviously simple for another thread. A team of payed developers could work all day and night to answer the usual questions in the Base forums by means of database skeletons and form designs.

Base does not help you to design a database. You need a concept, a little bit of theoretical background. Then Base is one of the more primitive database tools for the desktop. No, not even for the desktop. It's a database tool for this particular office suite since all of it's features are related to office documents. There is almost nothing special to know about Base except for some toolbars and what all those wizards do behind the scenes.

Design your relational database with invoices, customers and articles, prices, taxes and other national idiosyncrasies on a sheet of paper. It's not so trivial, particularly with prices and taxes changing over time. Then you are best off if you run some SQL commands to create the skeleton. This is by far easier and faster than all the work-around the bugs and insufficiencies. But, wait. If you can do all this there is absolutely no need to use Base for database creation. Just use a decent database for the job and enjoy Base when it seamlessly imports your data into your office documents. This is what it's best at. Abandon all hope. There is no easy way to do complex things. No free lunch. MS Access is the cheapest lunch you can chew with old teeth (but with a very low credit among professionals).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply