Subtracting a value

[Mille M]

I just started to work with FM so sorry if this question is on a too low level.

I have a Delivery note table where I specify all products my customer bought. One of the fields is called "amount".

I also have a table called Inventory where I have

the total number of amount of each product.

I.e: A customer buys 10 pieces of candy named sweet123. I want the amount field in this example with number 10 to subtract 10 from the total amount field in the Inventory table.

So if I had 100 of sweet123 after this purchase it should say 90 in the total amount field of the Inventory table.

Is this totally unclear or can anybody give me a tip ?! Please =)

[Ocean West]

Welcome to the forum!

The real question is at what point do you consider reducing the quantity in inventory?

At the creation of the invoice, or after you pull the items from inventory?

In all respects, I would seriously consider a transactional method for recording deposits & withdrawals to item in inventory.

Instead of a single field in your product table that shows Total On Hand, you should create calculation that summarizes the total from a transaction table of items received & shipped. ( think of your checking account )

Then thru scripts create logic to that will add records to the transaction table that records the event - this way you can also put more accounting for your items in inventory - such as returned items that can be resold - or adjust inventory for damaged or spoiled items - and also indicate reservations of items yet to be shipped.

Once items are shipped then you can process your inventory to reflect a withdraw from inventory.

+ 100 Received from Vendor ABC

- 003 Damaged in shipping (adjustment)

- 005 Customer 1001 Sold

- 007 Customer 1002 PENDING

+ 050 Received from Vendor XYZ

+ 001 Return from Customer 1001

- 010 Customer 1003 Sold

On Hand 133

Reserved = 7

Net On Hand = 126

[Mille M]

To start with, thanks a lot for your fast response!

To answer your question, since I have a unique

ID to all different products I'm thinking of withdrawing the amount entered in the invoice

table from the total amount/quantity value in the post connected to that specific ID.

So, if you have a product in Inventory:

ID Name Total amount/quantity

123 seet123 100

Then when someone buys i.e 10 pieces of sweet123

and that is entered in the invoice I want the

total amount/quantity in the Inventory to be reduced,

in this exampel to 90.

[comment]

Assuming you have a relationship between the two tables matching on ProductID, you can use a calculation field in the Inventory table =

InitialQuantity - Sum ( Deliveries::Quantity )

[Mille M]

Hi again,

Yes I have a relationship between the two tables matching on ProductID but it doesn't work =(

I also tried to create a numeric field in the Inventory table and then just added a calculated value to it:

Sum ( Deliveries::Quantity )

but it didn't show. Seems to be no "communication" between the two tables or am I doing anything else wrong ?

Thanks in advande !

[comment]

Try placing a portal to Deliveries on a layout of Inventory, and see if it shows any records.

[Mille M]

Unfortunatelly not =(

[comment]

Well, then your product ID's do not match. Check for things like leading/trailing spaces.

[Mille M]

I need to check.

Another q that might be a part of a solution to this:

If I in the Inventory table have following fields:

Shipping ID

Product ID

Name of product

Then in the Invoice table when typing the shipping id number I want a drop down menu in

the product ID field that only shows the products ID's that belongs to that particular shipping ID.

How do I make the relations here ? And how do I set up the fields ?

Sorry for all these q but they are very important to me.

[comment]

I believe it's unrelated. Search the forums for +conditional +value +list for some examples, e.g.

http://fmforums.com/forum/showpost.php?post/195641/