Jump to content
Sign in to follow this  

Inventory updates

Recommended Posts


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.



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.



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.


Edited by Guest

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

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