Hi

I've got a minor problem with my business-app, that I'm developing with FM 8
Advanced. This may be trivial to many of you.

I have got three related tables: customers, orders and line items (orders
being the binding table between these three).

Price gives me a headache. It's in the customers-table.

As I insert a new order in orders-table, i should be able to insert
line-items through a portal in a layout, so price should be fetched from a
customers-table.

If I add it in a portal as a related-field from customers-table, Filemaker
shows it on the next (unused) row too as soon as I accept the data.

So I added it in a line-items table as a related-field from customers-table.
This is where it belongs anyway and now it doesn't duplicate unnecessarily.

But the problem is, that the user needs to accept the new order first by
clicking outside of fields. Only after this a new order is established and a
customer-related data can be fetched into a line-item row (because the
orders-table is the binding one. There is no direct relationship between
line-items and customers).

How should I try to approach the solution?

In article <C1AB5E9F.105E8%anttiussa (AT) hotmail (DOT) com>,
Antti Ussa <anttiussa (AT) hotmail (DOT) com> wrote:

Ummmm . . .

Without knowing the details of your business I offer the following:

I think the usual approach is to have four tables: Customer, Order,
LineItem and Inventory

Inventory contains the description, cost and base price of each item.

LineItem contains the quantity and extended price of the item on an
order (Extended price = Inventory::Price * Quantity)

Order sums the extended prices of the line items, applies any discount,
sales tax, shipping cost, etc, and give the total price of the order. It
can also have fields for customer payment and balance due.

Customer has the info about the customer, and maybe sums of order
prices, payments and balance due.

Customer is related to Order by CustomerID, one customer to many orders,
allow creation of Order via the relationship.

Order is related to LineItem by OrderID, one order to many LineItems,
allow creation of LineItem via the relationship

LineItem is related to Inventory by InventoryID, many LineItems to one
Inventory item.

Portals, navigation buttons and value lists as appropriate:

Portal of Orders in Customer
Portal of LineItmes in Order

Value List of Inventory, taken from fields InventoryID and perhaps a
concatenated field of item name, size, price, so that you can pick from
the inventory list when you select an item for a LIne Item in an order.

--
For email, change <fake> to <earthlink>
Bill Collins

Hi

Thanks for the suggestion. This business-app is extraordinary in a sense,
that there is only one product to sell. That's why I skipped the idea of
inventory-table and instead inserted the price to customers-table.

Line-items -table just cannot fetch that until I create the order (because
it's the middleman in the table-structure). As I fill a new order it does
not exist until I click outside of fields. If I don't and start filling up
line-items in a portal in order-layout, FM cannot get the price from
customer-table...

I know there must be a more sophisticated way of solving this than making a
rectangle in the orders-layout advising user to "click here" before
continuing filling the line-items in the order.


On 17.12.2006 21:52, in article
bbcollins-FB4048.14521217122006 (AT)... uninet.net.mx,
"Bill" <bbcollins (AT) fake (DOT) net> wrote:

To try to address your second question about 'As I fill a new order it does
not exist until I click outside of fields', I think you may have a solution
in using a free plugin that triggers a script when leaving a field. Here are
3 of them :
- EventScript by Softs4Humans
- zippScript of www.nightwing.com.au
- Callscript at costello_ryan.tripod.com
Remi-Noel

"Antti Ussa" <anttiussa (AT) hotmail (DOT) com> a écrit dans le message de news:
C1AC1448.10965%anttiussa (AT) hotmail (DOT) com...