layouts and calculations

[nickOS]

I am not sure I can explain this correctly but I will try.

I have two related databases which work fine. The parent contains all the data and in the child is where additional data per end user is entered. For example one shipping container ( one customer )contains 10 different items - such as 3 boxes with spoons and 7 boxes of forks. The data in the child is further manipulated to reflect the distribution to up to 5 different end users. Each shipping container is a record.

Example: 2 boxes of forks and 2 box of spoons are to be delivered to end user A, 3 boxes forks and 1 box spoons to end user B and so on.

The problem is I cannot create a delivery receipt , thus deducting from the inventory of the end users and give me the correct balance of each commodity.

Any advise on this ?

[bcooney]

Alright, 12 views and still no replies. I must admit, I can't follow your question. It seems that you don't have the typical Inventory system data model. Can you list the tables you have and the relationships that you've set up. My instincts say that something's not quite right.

[nickOS]

I did not explain this correctly, my apologies.

I am attaching my files, hope this will give you a better understanding of my problem/

Invent_distribution.zip

[m.v.peabody]

Taking a whack at your calculation, assuming your problem is on the "distributionfp7" file, where you want the original quantity (20) to subtract the distrquanity (3 From JOE'S) and the quantity2 (7 from PETERS)

The original quantity at the top of the page was associated with the current TO, "inventory in" and the others were related to the "inventory" TO. So the simple subtraction was the problem. (It was subtracting from zero, because it didn't find a quantity field of the same TO on this layout.)

Try sticking this in your "balance" calc instead:

Inventory in::quantity - (inventory::distrquanity + distribution2)

[nickOS]

My problem is that I have several sub records in my distribution and inventory. My question is how can I get a correct balance of inventory for each one.

Unless I have a balance field for each of the sub records I dont think that I can obtain accurate inventories.

I dont know if I am looking at the right direction but I feel that I may have to change my way of thinking.

[m.v.peabody]

(Along with most people in the USA, I've been off for the weekend. Sorry I'm late getting back to you.)

The layout you have shown is good for what goes to each customer, and for this I might make a new layout just for inventory. Count the widgets and stanchions at the beginning of the month, and make a running count subtracting each as shown with each record, without regard to who bought them. Add another field (new_Widgets) for when you add more to your inventory, and include that in your calculation.

[nickOS]

Thank you, excellent suggestion. I will try it.