Very Computer

by **Stephen Chownin** » Wed, 30 Sep 1998 04:00:00

Sorry, but every time I have a major computer problem, I think of Robo Cop.
I'm building my first Relational DB and need some words of wisdom from the
Wise Ones. I've built a DB with three related files: Inventory, Customers, and
Orders. The demo/template on FM Pro 4.0 seemed to require alot of modification
to meet my requirements so I decided to go from scratch.

Naturally, I ran into problems. Problem one is that the summary fields in the
Orders file seems to be cumulative over all the records in the field. They are
summaries of repeating fields if that matters. How do I restrict the summary
to the current record?

Problem two is that I'm trying to figure out the best way to automatically
deduct the sell quantity in the Orders file from the quantity on hand of the
Inventory file. The demo version seems to have an intermediate file between
the orders and inventory file. I'm starting to think that there is a good
reason for this. Any suggestions? Advice?

Steve C.

--
"Americans are benevolently ignorant about Canada, while Canadians
are malevolently well informed about the United States."
- J. Bartlett Brebner

by **Stephen Chownin** » Wed, 30 Sep 1998 04:00:00

> Sorry, but every time I have a major computer problem, I think of Robo Cop.
> I'm building my first Relational DB and need some words of wisdom from the
> Wise Ones. I've built a DB with three related files: Inventory, Customers, and
> Orders. The demo/template on FM Pro 4.0 seemed to require alot of modification
> to meet my requirements so I decided to go from scratch.

> Naturally, I ran into problems. Problem one is that the summary fields in the
> Orders file seems to be cumulative over all the records in the field. They are
> summaries of repeating fields if that matters. How do I restrict the summary
> to the current record?

DUHH!!! Okay I figured out that I need to be using a calculation field, not a
summary field, so the Wise Ones can stop snickering now. Still pondering the
second problem...

Steve C.

Quote:
> Problem two is that I'm trying to figure out the best way to automatically
> deduct the sell quantity in the Orders file from the quantity on hand of the
> Inventory file. The demo version seems to have an intermediate file between
> the orders and inventory file. I'm starting to think that there is a good
> reason for this. Any suggestions? Advice?

> Steve C.

> --
> "Americans are benevolently ignorant about Canada, while Canadians
> are malevolently well informed about the United States."
> - J. Bartlett Brebner

--
"Being right too soon is socially unacceptable." - Robert A. Heinlein

by **Lynn All** » Thu, 01 Oct 1998 04:00:00

> problem, I think of Robo Cop. > I'm building my first Relational DB and
> need some words of wisdom from the > Wise Ones. I've built a DB with three
> related files: Inventory, Customers, and > Orders. The demo/template on FM
> Pro 4.0 seemed to require alot of modification > to meet my requirements
> so I decided to go from scratch. > > Naturally, I ran into problems.
> Problem one is that the summary fields in the > Orders file seems to be
> cumulative over all the records in the field. They are > summaries of
> repeating fields if that matters. How do I restrict the summary > to the
> current record?

> DUHH!!! Okay I figured out that I need to be using a calculation field,
> not a summary field, so the Wise Ones can stop snickering now. Still
> pondering the second problem...

> Steve C.

> > Problem two is that I'm trying to figure out the best way to
> > automatically deduct the sell quantity in the Orders file from the
> > quantity on hand of the Inventory file. The demo version seems to have
> > an intermediate file between the orders and inventory file. I'm starting
> > to think that there is a good reason for this. Any suggestions? Advice?

Yes....that intermediate file is "LineItems" I bet...it holds the info
you are now holding captive in your Evil Repeating Fields. ;)

ReLEASE that info! Set it free and it will make you rich! RICH, I say!!!

Ahem....never mind, I got overenthusiastic. You need to redesign so
that each line item is in a database related to Orders by the Order ID.
This will allow a) correct relational design and b) great flexibility in
reports, calculations, summaries, etc. This way your Inventory
depletion will take place on ONE line item per go...no wrassling with
getting which repetition of which repeating field. You view (and can
create) the LineItems through a portal on the Order layout.

Repeating fields seem so cheap and easy when you start out....just pop
them on there and damn they add up so nice.....but try finding. Try
sorting. Try, fer goshsakes creating a report! But they're a cheat and
sham and will make you wish you'd never heard the word database before
you're done. ;)

Do it the correct way, on the other hand, and your teeth will be whiter,
your car will never rust, and life will be easy and prosperous. Really.
;)

Lynn Allen
Allen & Allen Semiotics FSA Member - Filemaker Pro Consulting
http://www.semiotics.com Web & Graphic Design

by **Stephen Chownin** » Fri, 02 Oct 1998 04:00:00

<<SNIP>>

> > > Problem two is that I'm trying to figure out the best way to
> > > automatically deduct the sell quantity in the Orders file from the
> > > quantity on hand of the Inventory file. The demo version seems to have
> > > an intermediate file between the orders and inventory file. I'm starting
> > > to think that there is a good reason for this. Any suggestions? Advice?

> Yes....that intermediate file is "LineItems" I bet...it holds the info
> you are now holding captive in your Evil Repeating Fields. ;)

> ReLEASE that info! Set it free and it will make you rich! RICH, I say!!!

> Ahem....never mind, I got overenthusiastic. You need to redesign so
> that each line item is in a database related to Orders by the Order ID.
> This will allow a) correct relational design and b) great flexibility in
> reports, calculations, summaries, etc. This way your Inventory
> depletion will take place on ONE line item per go...no wrassling with
> getting which repetition of which repeating field. You view (and can
> create) the LineItems through a portal on the Order layout.

> Repeating fields seem so cheap and easy when you start out....just pop
> them on there and damn they add up so nice.....but try finding. Try
> sorting. Try, fer goshsakes creating a report! But they're a cheat and
> sham and will make you wish you'd never heard the word database before
> you're done. ;)

> Do it the correct way, on the other hand, and your teeth will be whiter,
> your car will never rust, and life will be easy and prosperous. Really.
> ;)

> Lynn Allen
> Allen & Allen Semiotics FSA Member - Filemaker Pro Consulting
> http://www.veryComputer.com/ Web & Graphic Design

Okay, okay!! I've seen the light! I'll change my evil ways! Besides, who
couldn't use whiter teeth? And as I live in Seattle, a non-rusting car is
always a plus. Please stand by to help when I*things up.

Steve C.

--
"Being right too soon is socially unacceptable." - Robert A. Heinlein

by **Stephen Chownin** » Sat, 03 Oct 1998 04:00:00

<<SNIP>>

> > > Problem two is that I'm trying to figure out the best way to
> > > automatically deduct the sell quantity in the Orders file from the
> > > quantity on hand of the Inventory file. The demo version seems to have
> > > an intermediate file between the orders and inventory file. I'm starting
> > > to think that there is a good reason for this. Any suggestions? Advice?

> Yes....that intermediate file is "LineItems" I bet...it holds the info
> you are now holding captive in your Evil Repeating Fields. ;)

> ReLEASE that info! Set it free and it will make you rich! RICH, I say!!!

> Ahem....never mind, I got overenthusiastic. You need to redesign so
> that each line item is in a database related to Orders by the Order ID.
> This will allow a) correct relational design and b) great flexibility in
> reports, calculations, summaries, etc. This way your Inventory
> depletion will take place on ONE line item per go...no wrassling with
> getting which repetition of which repeating field. You view (and can
> create) the LineItems through a portal on the Order layout.

> Repeating fields seem so cheap and easy when you start out....just pop
> them on there and damn they add up so nice.....but try finding. Try
> sorting. Try, fer goshsakes creating a report! But they're a cheat and
> sham and will make you wish you'd never heard the word database before
> you're done. ;)

> Do it the correct way, on the other hand, and your teeth will be whiter,
> your car will never rust, and life will be easy and prosperous. Really.
> ;)

> Lynn Allen
> Allen & Allen Semiotics FSA Member - Filemaker Pro Consulting
> http://www.semiotics.com Web & Graphic Design

Steve sidles hesitantly up to the edge of the abyss known as DB Redesign.

Steve (in a small voice): "I believe, I believe, I believe..." Steve peers
over the edge into the chasm which appears to have no bottom. In a shakey
voice he says, "I-I-I believe I can fly, bbbut before I jump, may I ask a
question?"
In a voice as loud as a thunderclap, the Wise Ones answer: "OF COURSE YOU MAY
ASK FOOL, THE QUESTION IS, WILL WE ANSWER!!! HAHAHAHAHAHAHAH!!"
"O-o-okay...ummm...yeah...th-th-the intermediate file in the template i-is
'LineItems'. A-and the 'Inventory' file that it is linked to s-s-seems to
determine quantity on hand by deducting total LineItems sales for each part
number from Intital Quantity."
"YES WE KNOW!! QUIT YOUR PRATTLING AND GET TO THE QUESTION BERFORE WE LOSE OUR PATIENCE!!!"
"Wwwwwhat h-happens when y-y-y-you have in-incoming inventory? What field
d-d-does the new p-product get added to?" He blurts out.

Steve stands at the edge waiting for an answer, the deafening silence broken
only by the whistling wind.....

--
"Being right too soon is socially unacceptable." - Robert A. Heinlein

by **Lyle Chamne** » Sat, 10 Oct 1998 04:00:00

You may also want to try this:

1. Read Chapter 10 in the FMPro4 User's Guide...you DO have the User's Guide,
don't you?
2. Pay particular attention to the section beginning on 10-36, "Invoicing
Example"
3. Re-read Chapter 10! and especially the "Invoicing Example"!

What you are experiencing is a "many-to-many" relation between the Orders and the
Products tables - "One Order can contain Many Products and One Product can be on
Many Orders". You will be happy to know that the majority of all RDBMS DO NOT
directly support many-to-many relations and must break these down to two (2)
one-to-many relations using a 'Link' or in this case, a 'Line Items' file. See
User's Guide page 10-39 for the abbreviated schema.

Oh, and by the way, you will also be pleased to note that FM does something that
even the big boys at Inprise (nee Borland) won't let you do with one of their main
database products(Paradox) that ships with Delphi and C++Builder - and that is let
you do a "JOIN" (get to know and love this term in the RDBMS world) on an
'auto-increment' (auto serial number) field! It took me minutes to prototype my
app in FMPro, which uses 8 tables that include 2 'link' tables, and literally days
using Delphi with Paradox as the database! Now, if FMPro(Dev. edition too) only
had ONE other menu item ...."Compile" !!!

Hope this helps out.

Lyle Chamney
Director, Technical Services
Canadian Digital Photo/Graphics Inc.
www.advics.com

> <<SNIP>>

> > > > Problem two is that I'm trying to figure out the best way to
> > > > automatically deduct the sell quantity in the Orders file from the

Very Computer

Glitch!! You call that a glitch!!??

Glitch!! You call that a glitch!!??

Glitch!! You call that a glitch!!??

Glitch!! You call that a glitch!!??

Glitch!! You call that a glitch!!??

Glitch!! You call that a glitch!!??

Glitch!! You call that a glitch!!??