RE: Commerce ID

[Jack W. B. <ja...@sf...>]

Dieter, et al;

I have been lurking on this list for a while, but this subject has such a
simple answer that I am de-cloaking to provide it. (Apologies in advance to
non-programmers who might be overwhelmed by coder-speak.)

Root has mentioned a version of the answer; simply provide some custom
fields in the table which can mean different things for different users. But
there is an extension of this concept that would add 0 to N custom fields to
everything without changing the existing tables!

Create a set of 'Custom Fields' tables. These would look like this:

CustFieldsHeader
----------------
ID
DisplayName
Description
ParentTableName (value should be actual table name to avoid confusion)
ValueType (Indicates how the value should be stored and retrived - optional)
FormHTML (Actual HTML to use in a form including the value - optional)
ReportHTML (Actual HTML to use in a report including the value - optional)

CustFieldsValue
---------------
HeaderID (foreign key to CustFieldsHeader.ID)
ParentOID (foreign key to 'OtherTable'.OID)
TextValue (note that all values could be kept as text to simplify the code)
NumericValue
DateValue

For each table you want to add a custom value for, you create a 'header'
record describing the custom value. Values added to the Value table would
use the OID of the 'owner' record in the parent table as the foreign key
(note that this might be PostreSQL specific).

Now you simply modify the code for any form that allows custom values to get
the the appropriate CustFieldsHeader records and add the custom fields to
the form. Same for any report. The values can be retrived in one of the
following ways:
  (1) Use a separate query for the CustFieldsValues based on an OID returned
from another query. (probably the simplest way to implement this)
  (2) Modify a parent table query to also retrieve
CustFieldsHeader.DisplayName and CustFieldsValue.TextValue (or whatever) and
treat them as key/value pairs. Note that this would require changing any
code that loops through a recordset to look for a key change to know it has
'actually' moved to the next record. (complex, but not too many changes to
code)
  (3) Create a query generator that reads the CustFieldsHeader records for a
particular parent table and then generates SQL query statements that include
custom fields. (complex, but something you can encapsulate into a re-useable
function, may require lots of changes to code)

Admittedly this seems a little bit complex at first, and above I told you
the answer was 'simple'. But if you look at this carefully you will see it
has the advantage of being something you can add to any table without too
much effort once the base work has been done. It also has the advantage of
being hugely flexible -- with the ability to allow users to add their own
custom fields to tables via a simple form. In the long run it really is
quite simple. I have used variations on this scheme to create extremely
flexible systems in the past and I know it works quite well.

Of course you are probably wondering why I don't just do it? Mostly because
I need to do paying programming work right now -- anyone want to pay me for
this? If you do then you should also send a few shekels Dieter's way as
well, for the excellent work he has done.

Jack William Bell

> -----Original Message-----
> From: sql...@li...
> [mailto:sql...@li...]On Behalf Of Dieter
> Simader
> Sent: Friday, February 08, 2002 8:12 AM
> To: sql...@li...
> Subject: Re: Commerce ID
> customer specific information such as a tax number, commerce number can be
> recorded in the notes field. The notes you enter in the customer screen
> are carried over when you create an invoice.
>
> Dieter Simader    http://www.sql-ledger.org   (780) 472-8161
> DWS Systems Inc.     Accounting Software       Fax: 478-5281
> =========== On a clear disk you can seek forever ===========
>
> On Fri, 8 Feb 2002, Paul Saris wrote:
>
> > In the EU the same thing is used, depending on country I have seen it
> > been called VAT-number or community code.
> >
> > By law community code has to be printed on all invoices (and it is
> > recommended to do it also on all trade related documents) from the
> > invoicing party.
> >
> > In intra-EU trade printing of the purchasing party's community code
> > makes it possible to invoice without VAT charged. Definitely this is a
> > field which should be added to the customer and supplier tables
> (as well
> > as print-outs like quotations, order confirmations, invoices).
> >
> > Paul