Add free text lines to an invoice form

  • Thread starter Thread starter Peter De Tender
  • Start date Start date
P

Peter De Tender

Hello,

I've always used a simple access database to create invoice forms and
reports. The data which I can enter in the invoice details (productnumbers)
are loaded from a table tbl_products.
I only had to enter the productnr and it gives me all details.

So far so good...

What I now want to do is give the user the ability to enter a "free line
text" when I don't have a productnr available, let's say for variable
transport costs. It should be possible to modify a description and price.

Hereunder I give a small example of the lines :

123 Product A 10? (data comes from
tbl_products; I only enter the productnr)
456 Product B 5? (idem)
<Whatever description> ??? For this line, I want to
add a free text description and a
price.

Of course, at the end of the invoice, all price fields are to be summarized
to have the total amount to be invoiced.

I tried with creating a productnr 999, but then whenever I change the
description and price, all invoices where this productnr is used gets
updated. Which is of course not the way it should be.

Can anyone please help me on this one ?

Kind regards,

Peter
 
Peter,

The right way to do it is to create a separate product code and description
for each different one required, rather than using free text. By the way,
this is how major ERP systems work, so you could say it's proven best
practice.

HTH,
Nikos
 
Nikos,

Thanks for your feedback, but I really want to know if it is possible.

I've seen already a lot of "shareware" invoice programs where this works; or
you enter a product code from your products list, or you can enter a free
text line.

As the business I create this for is a transport company, almost all invoice
lines will be text. Only for standard things they created a code.

eg. "free text line could be somethiing like "colli delivery from Belgium to
Germany" + price to be entered
product line could be "waiting 1 hour" at a fixed fee.

It is not possible to create product codes for each and every road the
courriers take...

Kind regards,

Peter
 
Peter,

It's doable under a different design. I was assuming the standard design
whereby the invoice line item table holds product codes only, and the
descriptions are read from a products table; I guess that's what you have
been doing, so the descriptions changed on all invoices when you changed it
on one?

In order to achieve what you want, the invoice line item table should store
the "product" code in a field and the description in another. The trick is
to use some simple automation on the entry form to default the description
of a product code from the products table to the control bound to the
description field in the invoice line items table once the product code is
entered/selected, so it does not have to be typed on a standard product, but
can be overwritten and stored without affecting any other invoice. In that
case, I would use a generic product code in my products table for use with
free text, or maybe a few of those, like one for freight, one for
warehousing, one for labour etc, so statistics on activity types can be
easily retrieved if required, independent of the free text.

This design is more storage space consuming (which is one of the reasons why
it is not adopted with "standard" products), but will do the job.

HTH,
Nikos
 
Back
Top