Help on how to extract data from Base to Calc

[dreadstar]

Hi forum members. Thank you for accepting me to the forum. Long time user of OpenOffice (on WinXP) and LibreOffice (on Linux Mint). My usual uses are only for Writer and Calc but now I have a current project that requires a database. First time for me to use Base and post on the forum for your help.

I would like to inquire how I can extract a record from Base and copy all field data to a row in Calc. More specifically, I would like to create a monthly TAX report of my expenses for that month on Calc. I figured out based on my google research how to create and populate the customer database on Base each indexed with a unique tax identification number (TIN).

On the Calc spreadsheet I have columns date, TIN, name, address1, address2, description, category, base_amount, vat_amount and total_amount. When I enter a row entry on Calc, I would like the TIN field to search the customer database to see if its an existing customer. If yes, automatically populate the corresponding name, address1 and address2 fields. If no, enter the new entry to update the Base database then populate the corresponding name, address1 and address2 fields of the spreadsheet. How do I do this?

Currently, I have an extra sheet in the spreadsheet that holds the customer data. I do a search for the TIN and if a match is found, I manually copy / paste the data on the monthly expense sheet. Its gets to be quite tedious after a while and thought of the database route to be more elegant.

Any help or suggestions are appreciated. Thanks in advance.

[UnklDonald418]

To accomplish what you have described would be very difficult.
The problem is that while Calc is a good spreadsheet program, it doesn't work very well as a database.
However, what you describe would be fairly straightforward using a relational database.

I uploaded a little demonstration database showing one way you might be able to reach your goals using a database.
Open the Form document DataEntryForm01
In the upper table select a Customer from the list sorted by TIN numbers.
The lower windows will display all the transactions associated with that Customer.
To post a new transaction for the selected Customer, in the lower left table on the row displaying the yellow asterisk (*) select the TRDATE column and either type in a date or using the down arrow select one from the date picker dialog.
Type in the relevant data in the DESC column and you can select a CATEEGORY using the List Box in that column. Type in a BASE_AMT and after entering the VAT amount and hitting return the record is saved and the Total is calculated.
The Total amount as well as the values for the Primary Key values are shown in the lower right table. I have two lower tables because everything in the lower right table is automatically generated.

Something I want to mention is that while it may seem logical to use the TIN as a Primary Key in the Customers table it can be a source of trouble. It is best to avoid Primary Keys that are input by the user because of the potential for errors and the problems they can cause. It is best to use an automatically generated Primary Key as is done in the demonstration.

I included a simple report, but to use that you will need to download and save the Sun/Oracle Report Builder extension (oracle-report-builder.oxt) Go to
https://wiki.openoffice.org/wiki/SUN_Report_Builder
for links to the download and documentation.
The extension can to be installed from any OO module, select Tools->Extension Manager-> Add and use the file picker to locate the downloaded extension file.
There is another form document SelectReportMonth that allows the user to select a Month and Year to be used by the supplied report.

This is just a demonstration, it would need further refinement before actually being useful. For instance, the VAT could probably also be calculated once the rules were defined for that calculation.