Adding numeric value to a text "Value list", I am trying to put together a
simple to use print quote form, the user will be able to choose a text
drop down value, eg: "Booklet single colour" that has a money value, but
how do I get the "real" numeric value into the drop down list for the
total calculation later? Am I thinking about this wrongly?
In article
Gif
> Adding numeric value to a text "Value list", I am trying to put together a
> simple to use print quote form, the user will be able to choose a text
> drop down value, eg: "Booklet single colour" that has a money value, but
> how do I get the "real" numeric value into the drop down list for the
> total calculation later? Am I thinking about this wrongly?
Sounds to me like you need a table of items with prices, and then pull
the value list from that. Call that table "Inventory"
To start, let's look at a simple Customer Order system. It would look
something like this:
Four tables: Customer, Order, LineItem, Inventory, Payment
Customer fields:
kpCustomerID, an automatic serial number
Name
Address
BalanceDue, a numeric calc field, total order amount minus total payment
amount
etc
Order fields:
kpOrderID, an automatic serial number
kfCustomerID, a number field
Date
Amount, a numeric calc field, total of line item amounts
etc
LineItem fields:
kpLineItemID, an automatic number field
kfOrderID, a number field
kfInventoryID, a number field
Quantity a number field
Amount, a numeric calc field, quantity * price
Inventory fields:
kpItemID, an automatic serial number.
Name, a text field
Price, a number field
etc
Payment fields:
kpPaymentID, an automatic serial number
kfCustomerID, a number field
Date
Amount
etc
Relationships:
Customer::kpCustomerID = Order::kfCustomerID, allow creation of Order
from Customer
Order::kpOrderID = LineItem::kfLineItemID, allow creation of LineItem
from Order
LineItem::kfIvnentoryID = Inventory::kpInventoryID
Payment::kfCustomerID = Customer::kpCustomerID, allow creation of
Payment from Customer
Define the value list of inventory items to use values from field, first
field Inventory::kpItemID, second field Inventory::Name, sort by second
field (and in FM 8 or later, show second field only).
Then you can complete the calculation field definitions:
LineItem::Amount = Inventory::Price * LineItem::Quantity
Order::Amount = Sum(LineItem::Amount)
Customer::BalanceDue = Sum(Order::Amount) - Sum(Payment::Amount)
With this concept in mind, you can now design the item selection layout
that you asked about in the first place.
You could use a layout of Order, which would have a portal to LineItem.
In the portal, put fields:
LineItem::kfInventoryID
Inventory::Name
Inventory::Price
LineItem::Quantity
LineItem::Amount
Attach the value list to the portal field LineItem::kfInventoryID
Or you could instead use a layout of LineItem, with fields:
LineItem::kfInventoryID
Inventory::Name
Inventory::Price
LineItem::Quantity
LineItem::Amount
Hope this helps.
--
For email, change
Bill Collins
On Mon, 17 Sep 2007 13:06:00 +0100, Bill
> In article
> Gif
>
>> Adding numeric value to a text "Value list", I am trying to put =
>> together a
>> simple to use print quote form, the user will be able to choose a tex=
t
>> drop down value, eg: "Booklet single colour" that has a money value, =
but
>> how do I get the "real" numeric value into the drop down list for the=
>> total calculation later? Am I thinking about this wrongly?
>
> Sounds to me like you need a table of items with prices, and then pull=
> the value list from that. Call that table "Inventory"
>
> To start, let's look at a simple Customer Order system. It would look
> something like this:
>
> Four tables: Customer, Order, LineItem, Inventory, Payment
>
> Customer fields:
> kpCustomerID, an automatic serial number
> Name
> Address
> BalanceDue, a numeric calc field, total order amount minus total payme=
nt
> amount
> etc
>
> Order fields:
> kpOrderID, an automatic serial number
> kfCustomerID, a number field
> Date
> Amount, a numeric calc field, total of line item amounts
> etc
>
> LineItem fields:
> kpLineItemID, an automatic number field
> kfOrderID, a number field
> kfInventoryID, a number field
> Quantity a number field
> Amount, a numeric calc field, quantity * price
>
> Inventory fields:
> kpItemID, an automatic serial number.
> Name, a text field
> Price, a number field
> etc
>
> Payment fields:
> kpPaymentID, an automatic serial number
> kfCustomerID, a number field
> Date
> Amount
> etc
>
> Relationships:
> Customer::kpCustomerID =3D Order::kfCustomerID, allow creation of Orde=
r
> from Customer
>
> Order::kpOrderID =3D LineItem::kfLineItemID, allow creation of LineIte=
m
> from Order
>
> LineItem::kfIvnentoryID =3D Inventory::kpInventoryID
>
> Payment::kfCustomerID =3D Customer::kpCustomerID, allow creation of
> Payment from Customer
>
> Define the value list of inventory items to use values from field, fir=
st
> field Inventory::kpItemID, second field Inventory::Name, sort by secon=
d
> field (and in FM 8 or later, show second field only).
>
> Then you can complete the calculation field definitions:
>
> LineItem::Amount =3D Inventory::Price * LineItem::Quantity
> Order::Amount =3D Sum(LineItem::Amount)
>
> Customer::BalanceDue =3D Sum(Order::Amount) - Sum(Payment::Amount)
>
> With this concept in mind, you can now design the item selection layou=
t
> that you asked about in the first place.
>
> You could use a layout of Order, which would have a portal to LineItem=
..
> In the portal, put fields:
> LineItem::kfInventoryID
> Inventory::Name
> Inventory::Price
> LineItem::Quantity
> LineItem::Amount
>
> Attach the value list to the portal field LineItem::kfInventoryID
>
> Or you could instead use a layout of LineItem, with fields:
> LineItem::kfInventoryID
> Inventory::Name
> Inventory::Price
> LineItem::Quantity
> LineItem::Amount
>
> Hope this helps.
>
That's for that, I'm having trouble getting the value list to display, i=
t =
seems to come up with random data along with the required stuff, I'll go=
=
through it all again.
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/