Question on Header-Detail programming.

[John Esak]

>John will probably come on here and point out something that I just
realized as I'm building a new program....

>If you use a master control file then you don't have to count the number of
existing records in order to add one to the end.  You simply go to the
control file and >pull out a new number and you're sure to sort in the order
in which they were entered.
>
>Boaz


Yes, always being sure of grabbing a higher number than anything that could
already be in the file is a huge plus for me.  Also, there are times when
files get corrupted and this was a certain way of dealing with a more
reasonable "fixing" scheme.  Actually, though, didn't my suggestion say not
to just use an ever incrementing number for the pieces of detail, but to
assign a completely unique number to every header record as well. By using
*that* unique header-number & detail-record-number you get a very reliable
index that is chronologically (by time even) sequenced.  This has been
useful to me in the past. If you use GIANT header numbers to begin with, say
a 10 digit number, than you can also play games with the leftmost number,
for example all records starting with 1xxxxxxxxx are invoice header records
and all starting with 2xxxxxxxxx are customer file records and so forth.  If
you ever use a system where there is just one (1) transaction file and the
guts of that file tell you what kind of transaction it is and detail it
completely. You can always have this up-front method of culling out just a
particular type of transaction.  Of course this only works in systems that
can use just one transaction file like a G/L with all it's various modules.


So, now that you know that I use this method mostly for G/L systems... It
becomes important to have unique numbers in the header because there are
actually times when an Accounts Payable invoice might have a vendor code of
USA1 and somewhere in the customer file is a customer code of USA1 as well.
You start putting customer codes together with invoice numbers and find that
they are not as unique as you thought they would be. :-(  

And really, the final absolute specific reason I use an ever increasing
unique number for any header record is it keeps its uniqueness even when the
customer changes his name from Smith Brothers with a code of SMITH1 to
Ludens with a code of LUDE1.  The detail will never become disassociated
with their correct header.  

By the way, if I said to *always* do this... My apologies.... This sort of
convention is more... Well just that a convention to use, not a hard and
fast rule. I tried to be very careful about saying what is a style you might
want to adopt and what is an absolute must to have good file integrity and
design.  You don't *always* have to have a unique numeric header id...
Hidden from users or not.  But, if someday you decide to integrate some
filePro app you've written with an SQL database, this is one of the first
things you will wish you had done.  Those SQL guys always want a field that
holds a unique number... And for some reason they don't like our record
number.  With good reason, our record number can *change* if ever you
compress out the unused records, or just delete a record and re-use it. for
something else. This messes up SQL relationships.

By the way... (And if you've made it this far... You probably want to hear
this next thing as well... If you haven't made it this far, that is a
science fiction kind of impossibility that I don't care to go into here.)

Really, by having unique numbers for anything that will be considered a
"header" record... You might end up with lots of them and lots of
flexibility.  Sort of like this:


Customer File      Cust-Cod    Unique Cust#
Fred's Quarry      FRED1       1000000001


Invoice File       Invoice#    Unique Cust#  Unique Inv#  
FRED1              2049        1000000001    2000000001

Invoice LineItems  Invoice#    Unique Cust#  Unique Inv#   Unique LinItem#
1	FRED1        2019        1000000001    2000000001    3000000001
3	FRED1        2019        1000000001    2000000001    3000000014
4	FRED1        2019        1000000001    2000000001    3000000019

Now, you see that there is no line-item 2. Maybe it got deleted during the
phone call order or for whatever reason.  Because you have diligently put
unique numbers on everything, you can quickly renumber the invoice line
items to be sequential using the hidden unique#. And this "looks good" to
the customer.  In other words there is no reason why the customer ever has
to see the unique line item number, but only the human-readable one.
Obviously, "skipping" a line item on your invoice is going to be cause for
them to call and say "Where is Line Item 2?" In some cases not, but
generally, people like to see things nice and neat.

Anyway, even if the line items were added days apart with widely varying
numbers, they will always be in rising sequential order so you can pull the
invoice together to look any way you want it to look.  And, this ability
extends to any header.  Say for example the invoice# has to be deleted
entirely and you want to assign another number for whatever reason.  You can
use the hidden invoice number to do this.  Same with the customer code...
And this happens often believe me.  Even if you use numeric customer codes
there are still times they have to be changed. Having a hidden unique code
number is a huge value.

Unique record numbers are usually good candidates for using "hidden"
indexes. No user is probably ever going to see these or need to use them.

John