Re: Commerce ID

[Sergio A. K. <ser...@ho...>]

IMO your solution is too complicated for what is trying
to resolve and complicate the life of the programmer,
while the dieter's solution complicate the life of the
end user (and difficult to validate).

I second the motion of root, just add some "custom"
fields and everyone happy, a simple solution that
pass the KISS test.

if you *need* one of the custom fields to have non null
values you just add a constraint (or checks).
(as is the case for CUIT -tax number- for us)

another solution is to take advantage of pgsql dynamic
array field ("custom varchar(99)[]") and with one field
you can have all the custom fields you want.

but I prefer the simpler solution that is to add
some "custom_n" fields.

/sergio

----- Original Message -----
From: "Jack William Bell" <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
>
>
>
>