You may find that the database which is part of Libre Office/ OpenOffice delights you as much as it has me. This page tries to help you use it.
Remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.
There's more about ooBase in the main index to this material.
This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software, 7/19
Suppose you have a database.
And you want to add further records to it... in a batch. Perhaps you've downloaded a bunch of records from online your credit card statement.
The obvious elements of your existing database, with some records already entered, might look like...
Date (MM/DD/YYYY) / Amount/ ToOrFrom whom 1/1/2019, -500.00, Daycare 1/1/2019, -10.00, Flowers 5/1/2019, 510.00, Repaid- check 2341
(The first two records describe debits, arising from my use of my card; the last is a payment from me to the credit card company, made with check number 2341 from my checking account.)
In addition to that, you need some way to create a primary key. The values in the primary key, remember, must be unique across the whole database.
For the purposes of this exercise, I'm going to use a really crude answer: A number. So my database becomes...
Unique ID number/ Date / Amount/ ToOrFrom whom 0001, 1/1/2019, -500.00, Daycare 0002, 1/1/2019, -10.00, Flowers 0027, 5/1/2019, 510.00, Repaid- check 2341
Normally the Unique ID numbers would be generated 1, 2, 3... However, that isn't necessary, and won't always happen. Also, sometimes records are deleted, leaving "gaps" in the sequence. I made the ID for the third record "0027" to stress that the numbers can be "anything"... as long as they are unique.
If I didn't want to add records in batches, I could just use "auto-increment", and "the system" would deal with generating the value for the primary key field. I think I can still use that, up to a point.
=======
Looking ahead: Let's say that what the credit card bill download provides is...
1/23/2019, -30.00, Henry Higgins 1/25/2019, -25.57, AmazonSeller GetItCheap
I built an Open Office database (ver 4.1.6) to hold the data given. I am not aware of anything in what follows which doesn't apply equally to the same task under Libre Office.
Create new. Register it. Open for Editing. Called it FDB048.odb ("Free DataBase"). Created a folder first, also called FDB048, to hold everything related to this database.
Used Create Table, Design View. The first field made Primary Key...
Field/ Type/ Properties...
IDNum/ Integer/ AutoValue= yes
XDate/ Date/ Format MM/DD/YYYY ("XDate" for "Transaction Date")
Amnt/ Decimal/ Length=8, Decimal Places=2 ("Amt" for "Amount")
Descrip/ Text (VarChar), Length=2
Saved it as "Trans", for "Transactions" (Long names are a pain, when you work on the "internals" of a database
Filled in the data I promised as the starting point.
Initially, the IDs were 0,1,2. They were not displayed with leading zeros, but I'm sure that could be arranged.
To exactly match my hypothetical starting point, I then went through the data, and edited the ID numbers. The system didn't object, except when I tried to make the Daycare entry "1" BEFORE I had made the "Flowers" entry "2".
What I had at this point is available as FDB048-bef append.odb
Now... one of the "secrets" is:
Put the records to be appended in an Open Office ("Calc") spreadsheet to start the process. For columns holding dates, format the column accordingly before filling it. (I rarely use dates stored, as far as the computer know, as dates in my personal work. They are a pain in so many ways. But have used dates, as dates, in this exercise.)
The details of that will vary from situation to situation, of course, but it should not be difficult to arrange... and arrange for the computer to do it. I'm not suggested you transcribe the data to the spreadsheet by hand!
This, by the way, would be a good time to tweak things. You might want to change, say "Henry Higgins" (the description of that expenditure as supplied by the credit card company) to, say, "elocution lessons".
The spreadsheet I used is available as FDB48-data to append.ods
LOOK AT the data in the spreadsheet CAREFULLY
Spreadsheets aren't fussy about what you put where. Databases are. Suppose, due to a corruption, you have a date like "112/3/2019"? The database may well choke on that when you try to send that record to it.
Suppose you weren't very generous when you set up the field for the description of the transaction; suppose you only set aside 10 characters? And further suppose that, once you've been using the database for a while, you want to describe a transaction as "Cow from Metcalfe-Gibson"? That needs more than 10 characters. How will the database react to your attempt? (This is a matter to be figured out, dealt with, one day. For now: Just be sure your data DOES meet the requirements of the database specification before you proceed to...!)
In the spreadsheet, highlight just the cells containing the new records. Yes, I know: There's nothing to supply the database's "IDNum" field.
Open the database, if it isn't already open. You want the database's main project manager window, but it doesn't matter if the Transactions table happens to be open already.
After making sure you're looking at your database's tables ("Tables" selected in left hand column), right-click on "Trans", the only table this database has at the moment, and select "Paste" from the resulting menu.
Get it looking like...
Note, especially, that you want to append data, and you do NOT want to "Use first line as column names".
Click "Next", and make the changes to give the following result...
(To get there, I repeatedly selected a field name in the right hand column, and used the "up" arrow to the right to move it up, so that the date is in the row for "XDate", the -30 is in the "Amnt" row, and "Henry Higgins" is in the "Descrip" row.)
Click "Create" (lower right)
My first attempt was only partially successful. (But it was more successful than it might have been!)
First I got...
But upon clicking "Yes", I did (eventually) get...
To SEE that I'd got my result, after I went to the already-open window with the Trans table in it, I needed to click the "Refresh" button....
I soon traced the "wrong data type" problem to something esoteric, which I will explain in a moment.
But notice! Even thought the results aren't perfect, they aren't crazy, AND the system warned me that I needed to check the results carefully.
After the little tweak, all went well, resulting in...
The "issues" of the formatting, etc, are trivial matters, easily tidied to your heart's desire. ("$-10.00" displayed as "10" even though, at least, the last record is displayed as "-25.57", for instance.) You should be looking at what is in the table with a form or report, anyway. (During the formatting "tidy up" you can make many improvements. For instance, you could arrange for "money in" to display in black and "money out" to display in red.)
So... what went wrong, how did I fix it?
I told you dates are a pain!
I was working on a PC set up for use in the UK... where 1/2/2019 means (logically!) the 1st of February, not (illogically, USA cousins) the 2nd of January.
I'd carefully set the fields thus in both the spreadsheet and the database... but that didn't satisfy the "append data" system. I was using "custom formats", not, I would guess, shared between the two applications. As soon as I redefined both fields to one of the "standard" "date" formats, the process went without a hiccup. (I didn't even have to re-enter any of the data... even though the spreadsheet had to "be clever" and realize that the old 23/01/2019 needed to be "converted" to 01/23/2019. AND the database also converted the already-present data without a murmur. (^_^) !!
Duly impressed, and the day's work scheduling "bust" anyway, I thought- "Well... it did so well with that, what else can I throw at it?
Trying to be difficult, working in the database, I left the table open, but went to the database's main project manager window, right clicked on the table's name, selected "Edit".
Now... before this, remember, I had a description entry of "AmazonSeller GetItCheapAmazonSeller GetItCheap" in one record.
What would happen if I changed the properties of the field that is in to say it can only store 15 characters?
"The system" copes without complaint! It just trims the data in fields that had too many characters at the time you re-specified the field. (To be very fussy, I would say that I would prefer it if the system weren't quite so "can do". It would be nice if it said "Data will be truncated. Proceed?")
-------
Following on from "what happens if you change the space allocated for a field?".... What happens if you try to append data with too-long data?
A quick test gave rise to....
BUT! Instead of adding what data it could, as happened before, when the source field and destination field formats didn't match, this time, the system just "passed over" the troublesome record. At least it generated a warning! But if you were appending many records, I don't know how you would know which records had been skipped over.
Note, however, that something like this will be a problem for any database system. Maybe others will deal with the problem more elegantly, but it will NEED dealing with. This isn't "a fault" in the Open Office / Libre Office database!
Two routes forward occur to me. If someone more expert than me can help, it would be very welcome.
If "the system" has a log somewhere, listing the records that could not be appended, that would be great.
Alternatively, is there an easy way to do a "trial" update? I.e. to "present" the records, as if they were to be appended, but without actually appending them... yet. The "try to append" could be done a few times, with edits to the data to be appended, until the attempt didn't show up any problem records.
Sorry.. this isn't "finished"... but I am rapidly running out of time for it. At 13:05 I have finished the first pass, captured screenshots, roughed out the story.
Converting that to a finished webpage took until 14:35... hence my "failure" to cover "everything".
I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....
I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.
PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE
If you liked this ooBase tutorial, see the main index for information other help from the same author.
Editor's email address. Suggestions welcomed! - - - Want a site hosted, or email? I like 1&1's services.
Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.
AND passes...
. . . . . P a g e . . . E n d s . . . . .