Please help for calculation field!

[Mirido]

Hi All,

I think FileMaker is a great software, especially for beginners like me. However, I sometimes cannot solve my problems by consulting FileMakers

[ESpringer]

Mirido,

It sounds like you're asking the individual records here to function as summaries. Is it that each customer has only one record? (Is it also that you have only one product, so qty is the only variable?)

Ideally, each *order* would have its own record, with fields like Date, Order#, qty, ShipDate, etc.

There might also be a database of Customers, which would be related to all the orders from that Customer. Then, it would be easy to have summaries (outstanding orders, etc.) that update automatically.

Once you build the relationships between these databases, you could work mostly in the Customer mode, as is familiar to you, but the other database would be keeping track of the details, and calculations would function most smoothly.

Is this making sense so far?

[Mirido]

ESpringer,

- Each individual *order* can have from 1 up to 1,520 records per customer, depending on the number of ordered SKUs (Stock Keeping Unit).

- The Order database is linked to several external databases such as Customers, Qualities, Salesmen, Countries, Weights, Volumes, Currency Conversion and so on...

What I'm trying to obtain is:

- One field per record named, for instance, "shipped orders" that contains a "cumulative total" of all items shipped so far for that particular order#/customer#/product# combination.

- On the same above-mentioned record one field named, for instance, "orders for shipping" that will:

When empty --> leave the amount in the field "shipped orders" as is.

When not empty --> trigger an increase in the field "shipped orders" by the amount it contains.

For example following steps for one particular order#/customer#/product# record combination:

Field

[ESpringer]

Many here are much better qualified as consultants on order and shipping databases! But in the meantime...

I notice that "orders" seems to have two meanings in what you've written:

Each Order from a customer involves a number of LineItems, generally, each of which specifies a qty and an SKU. It sounds like the database you're working out of here is what might be called a LineItems file, no?

Then, what you want to know about is (in my terms, if I understand) not how many *orders* have shipped, but what QUANTITY, out of the quantity ordered... Is that right?

So your fields could have been named QtyOrdered; QtyShipped; QtyShippingNow; QtyOutstanding (or something like that)? It's not important that you name your fields one particular way, but it is important that one word, like "order" consistently means the same thing. Probably, it should stand for something like "total request from customer", and each order# has its own unique tag, and the LineItems db shows how each of these orders involves a quantity of this, a quantity of that, etc.

So, if I understand, you want it so when data is entered about what's shipping NOW, it will reduce the "outstanding" qty, and when it's no longer in process, and the value is changed back to 0, the qty shipped goes up accordingly. Yes?

OK. To be honest (I welcome other responses from real gurus), it seems you actually want yet one MORE file: ShippedItems. If an order is not always satisfied in just one shipment (in which case you could have just had a "ship date" for an order), then you want to be able to track information like how many shipped on what date. As an advantage, qty information will update automatically, without requiring scripts (thought scripts might still be useful).

If you sometimes ship more than one SKU# to the same customer, then you want your Shipments database also to contain something like line items: each ShippingItem shows:

On Date____ we shipped ___qty in response to LineItem#_____, perhaps with a check box for "shipment complete". This record can be linked to the original LIneItem to display what the item's SKU# was, and how many were ordered...

The Orders database (as distinct from the LineItems database) then can have a portal not just to what was ordered, but also to what was shipped. All shipments in response to the same LineItem# can be related to each other (as well as to the , so that it will be easy to show the total shipped in response to that order, and how many are outstanding.

This may be more complex than what you're after, but unless I'm misunderstanding you, you should set up a clean structure to hold your shipping information.

-ESpringer