Jump to content
Sign in to follow this  
rservis

Inventory updates

Recommended Posts

Hello,

Am stuck on a little problem, I have created a purchase orders line items file and want to update my inventory with the newly ordered items.

I thought of two ways to accomplish this but am not quite there and was looking for expert advice.

I would be ordering the same items several times and would like to press a button and update all the qty's in the stock table and if I have never ordered that product before to just add it as a new record.

I tried exporting from Purchase Order Line Items into a new temp file then importing in to Stocks, with the barcode field as the matching field so it would only update the qty and add non-matching as new records. Didn't work!

Then, I thought about a set field script but it seemed to only update the record you was last on and leaving the others out.

So, not sure what to try next??

Any help and advice greatly appreciated.

Thanks

Roy

Share this post


Link to post
Share on other sites

Hello Roy

Question - Why are you ordering items that are not in your main inventory/items in the first place. I thought one would add the item to the DB before purchasing it so all the details are ready to go.

If your relationship is working correctly you should only need to step through each record in the PO table to update the inventory table as I think this would be a 1 to 1 connection and not a 1 to many connection. If you wish you can email me a copy of what you had done and I will take a look at it with you.

Regards

Phillip

Share this post


Link to post
Share on other sites

Not sure of your structure but we also keep track of a fairly big inventory here. We have it set up as 2 tables. Inventory(1 record per item) and Parts in/out(multiple records). When we scan a part into or out of the inventory we just create a record an put the qty in the correct field(qty in or qty out) This keeps the single record in the inventory accurate to what we have received and pulled for production runs.

The only thing I see as a problem doing this is that once the Parts in/ out file gets to about 60,000 records(takes us about 8 months)it gets kinda slow. We then archive that file to a seperate file and delete all the records(Make sure to update your "Start Qty" field in the inventory before deleting)....lol.

As far as updating your inventory from the purchasing file. We do the same here and it is basically a script in the Parts in/out file that imports the line items as they are checked as received.

hth

Edited by Guest

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.