Calculate on hand stock but also keep track of different locations

[esvdm]

I have chewed on this one for a while but I'm stuck. How can I update the qty in my storage location when a transaction takes place.

Each item can be stored in different pre determined locations and I need to keep track of how many are in each location.

Thanks

[martinwcole]

Re: Calculate on hand stock but also keep track of different locations

I didn't look at your zip file, but this should be easy, with the following warnings:

say you have locations WH1, WH2, WH3, WH4 (WH is for warehouse, or similar)
each of these is a field in the inventory table, with another field, OH (on hand)
where OH is a calculated filed, the sum of the locations.

If you only have one user entering records, no problem. But if you have several users entering data you will need to do the following:

DO NOTuse posting for the inventory

In an Invoicing system, for example, where you are entering line items for items purchased - in the cansaverecord event in the lineitems table field rules, open the inventory table in exclusive mode allowing may 5 trys with a 1 second interval, and deduct or add the the quantity where purchased from, and then close the inventory table.

Although not technically necessary, when you have maybe 5 users who are entering invoices and are very quick, this will overcome isues.

[MoGrace]

Re: Calculate on hand stock but also keep track of different locations

Hi Emile,
Have you tried a posting field rule for each of the transaction table fields Quantity_Received and Quantity_Used? If you match the storage_id in both tables it seems to work fine.

Sorry Martin - we seem to have overlapped our responses - I will defer to you.

[martinwcole]

Re: Calculate on hand stock but also keep track of different locations

Hi Mo,
No problem, but I should have added, "if you are fluent with writing coded in xbasic."

[Ray in Capetown]

Re: Calculate on hand stock but also keep track of different locations

Emile,

The method you appear to be trying to employ is entirely possible, and for some situations, preferable;
(examples;- different stores, pricing varies, ordering done at branch level, number of branches or users want to add locations for other purposes).
That being, essentially, an inventory master record for each location. Your design maybe overly complicated - too normalized in my opinion. And multiple maintained consolidations. If you DO need to use this method that can be discussed. I have such a system in use by dozens of businesses for over a decade.

Without a doubt Martin's scenario is much simpler and most popular.
Where a single record is used per stock item with a finite number of fields per branch to carry the stock quantity on hand at each.
From your example that is all you are trying to achieve.

[Windstarsoftware]

Re: Calculate on hand stock but also keep track of different locations

We do this a lot as well sell several different types of inventory apps. What I like to do is use the Post to Inventory field rule. When the user enters the item or stock number for an item being added to inventory we do an auto op up or drop down list showing the item at all its locations. The user can select the line with the item and enter the Qty field and it will remove the Qty of the item selected. Thi method also gives each user the ability to see how many of these items are in inventory. It also allows a user to see that the slot or bin location may not have enough of the items and keeps from running into a negative stock in one location while also having an incorrect amount in another.

We also added a transfer option that allows the user to move a quantity of the item from one slot to the other.

[esvdm]

Re: Calculate on hand stock but also keep track of different locations

Hi there. Thank you for all your replies. I have thought about all off your ideas. I first tried posting like Robin suggested before I made this post but that didn't work. It seem to have work but it updated only the first storage KEY field found in the child table if there are duplicate records which there will be. I might have more than a 100 different storage boxes so Martin's idea won't work for my application but only one user will be entering information. Thank you Ray but I'm not planning a complicated database like that. The database is meant for one location with multiple storage locations and one user.

I have though come up with another solution and it seemed to do the trick for now. Since both tables have the same ITEM_id and Storage_id I have created a calculated field in both tables adding these to fields together in each table and adding a character to it just to make the record unique I.e item_id+"x"+storage_id.
Now I can post the amount from the transaction field to the correct storage location and to inventory on_hand field.(I have created a lookup field with a filter in the transaction table to ensure that the user can only select the storage locations that are pre-determined in the child table for the specific inventory item. The lookup then also show the qty in each location. (Thanks for the idea Fred). It will be quite helpful to see which locations are running low.

[Ray in Capetown]

Re: Calculate on hand stock but also keep track of different locations

Sounds good.
Presuming "x" is an incrementing string or number?

[esvdm]

Re: Calculate on hand stock but also keep track of different locations

no no, X is just x. Just a character that helps make the new key field unique to every storage location for that specific item.