[Solved] Convert report to invoice

[Zazu99]

I have followed the sample database ([Example] Invoice (without macros)) and I would now like to create a unique invoice for each customer that can be printed and sent to the customer with the product purchased. We could increase the fields in the sample database query ITEMS SOLD to include other data required in the printed invoice i.e customer address, telephone, tax number etc. Over time this query could create many thousand entries relating to CUSTOMER and ITEMS SOLD. So I am looking for advice on how to create an invoice for a single customer or for a group of customers based on the days' or weeks trading activity

At the moment we use a manual invoice in CALC but this is duplication and not very efficient. I seem to recall the sample database Northwind that ships with MS Access was able to do it

[Villeroy]

... with the product purchased.

I think it should allow for many products. Or is there always one product only per invoice?

The report builder extension is the only OpenOffice related tool which should be able to produce invoices. I don't use it because it is so complicated and always buggy. I have never seen any invoicing solution based on that tool but such a thing may exist.
##############################################################################################################
If I wanted to do invoicing with OpenOffice I would design a Calc document as report.
An invoice has only one client record and many product records.
A Calc document can serve as report while having input forms as well.
It is very easy to import the client record for one invoice and link it to a the address cells of a well designed print range.
You can import the product records for the invoice below the address area. The calculations (quantity * price and total * VAT) can be done in the same query or in the spreadsheet since formulas adjacent to dynamic import ranges adjust to the size of the import range.
An input form lets you pick one invoice, stores the invoice-ID in a filter table and when you refresh the import ranges the queries pick up the client and the products of that invoice. The recorded invoice-IDs help you to keep track of already printed invoices.
Refreshing all import ranges is a one-line macro.
Processing an array of multiple selected invoice-IDs (set parameter-query, refresh ranges, print out) is a matter of a few lines of Basic code.

[Zazu99]

Thank you Villeroy. I understand the principle of your post but not sure on the actual steps to make it happen.
As mentioned we have a manual invoice in Calc which means we have address fields set up and lines in the body for the entry of products. We use formula to calculate line totals and tax and also at the bottom to add all the lies incl tax.

If we use the Example database what do I need to do to replace the address fields in the Calc sheet with the address fields in the database customer table and also the products

[Villeroy]

Register DACM's Invoicing_Unique_Items2.odb as "Invoice Demo" (Tools>Options>Base>Databases...)

From that database call Tools>SQL... and run this to add another table:

Code: Select all

CREATE TABLE “Filter” (ID INT PRIMARY KEY, INT1 INT);
INSERT INTO “Filter” VALUES(1,NULL);

Create 4 queries
1) qSingleInvoiceSelect

Code: Select all

SELECT * FROM "Filter" WHERE "ID" = 1

2) qSingleInvoiceItems

Code: Select all

SELECT "ITEMS"."ITEM", "ITEMS"."ITEM_SERIAL", "ITEMS"."PRICE" 
FROM "SOLD", "ITEMS", "Filter" 
WHERE "SOLD"."ITEM_ID" = "ITEMS"."ITEM_ID" AND "Filter"."INT1" = "SOLD"."INVOICE_ID" AND "Filter"."ID" = 1

3) qSingleInvoiceCustomer

Code: Select all

SELECT "CUSTOMERS"."NAME", "CUSTOMERS"."CONTACT", "CUSTOMERS"."CUSTOMER_ID", "INVOICE"."DATE", "INVOICE"."ID" 
FROM "INVOICE", "CUSTOMERS", "Filter" 
WHERE "INVOICE"."CUSTOMER_ID" = "CUSTOMERS"."CUSTOMER_ID" AND "Filter"."INT1" = "INVOICE"."ID" AND "Filter"."ID" = 1

4) qListboxItems

Code: Select all

SELECT "CUSTOMERS"."NAME" || ' ' || "INVOICE"."DATE" AS "V", "INVOICE"."ID" 
FROM "INVOICE", "CUSTOMERS" 
WHERE "INVOICE"."CUSTOMER_ID" = "CUSTOMERS"."CUSTOMER_ID" 
ORDER BY "INVOICE"."ID" DESC

Download the attached spreadheet to a trusted directory where documents are allowed to run macros (Tools>Options>Security>Macro Security...]
Open the spreadsheet.
Pick an invoice from the list box on the first sheet and activate the second.
Call print preview.

I did not invest much time in nice spreadsheet layout.
It demonstrates a form on a spreadsheet to write data into the database (filter criteria to get one particular invoice).
It demonstrates how to use a spreadsheet as reporting tool. It can import many record sets (two in this case), re-arrange imported data (the address, date, invoice area), do calculations (VAT and sum below the items area). Conditional formatting, charts and many other things would be possible as well but I don't know any way to import pictures from a database.
A very simple macro stores the form content and refreshes all import ranges.

[Villeroy]

For an immediate print out of the latest invoice there is another solution:
Turn the invoice date field into a time stamp field with DEFAULT CURRENT_TIMESTAMP.
Put DACM's input form onto the spreadsheet.
Create a query which selects the latest invoice rather than a selected one.
This does not require any filter table nor filter form. You enter a new invoice and let it print immediately.

A third (more complex) variant would query all invoice-IDs where some boolean field is unchecked (not yet printed), query one invoice after the other, refresh import, print, set boolean field = True, fetch next ID.

[Zazu99]

Villeroy

CREATE TABLE “Filter” (ID INT PRIMARY KEY, INT1 INT);
INSERT INTO “Filter” VALUES(1,NULL);

I got an error message "Unexpected token: “ in statement [CREATE TABLE ]"

[Zazu99]

Villeroy
I have fixed the token issue. On the spreadsheet the list box doesn't open. Also where may I find the underlying code to incorporate this in my spreadsheet please.

As always - thanks for the help

[Zazu99]

Still cannot get the drop down box to display a list

[zazu]

I have successfully created an Invoice using Oracle Report Builder. The Invoice is based on a query. In the process I was able to create Groups within the invoice and calculated fields using functions to assign shipping, payments to date and calculate tax. Formatting of the invoice output was relatively straightforward.