Updating Inventory With Lot No Using Config Package

(0) Share

Report

Posted on by KL Ang

Hi Friends,

I like to ask whether anyone has or knows how to update inventory with Lot No using config package?

I am trying to import inventory data for opening balance but before that I wanted to zero-ise the inventory as the data quality is very poor and verify no item is left out. I tried to use config package, tested various tables, I can get the data imported into the inventory journal but without the lot no. There is a Lot No field in Physical Inventory Journal or table 83 (not sure if the table no is universal in BC) but lot no is a "dead" field. I cannot do a 1 shot import of lot numbers and update the inventory qty at the same time - I have to manually open the Item Tracking Lines screen, select the lot no and do this item by item before I can post and complete the inventory update for each item. This is extremely manual and if there are thousands of items, the manual update of lot no and inventory qty is impossible - anyone knows a more efficient way?

Thank you.

All responses (8)

Answers (1)

KL Ang 35 on at

Like (0)

Report

RE: Updating Inventory With Lot No Using Config Package

Hi Guy, MahGah,

I thought I tried earlier but not as a package. I re-try again as a package, exported the Excel file to identify the difference. Make the changes and imported back in but the first 2 imports or tries were not successful but the 3rd try worked - I explain why below. The lot numbers show up and I can post successfully.

What I tried to test was to add 10 units to the inventory. The fields that were not correctly filled earlier in Reservation Entry table - my fault.

Entry No.: should use a new running number (when I did not, no import was inserted in first 2 tries)

Source Subtype: return a 2, so I add 2 to each line (not sure why)

Source ID: should fill with Journal Template Name

Quantity Invoiced (Base): should be zero (I filled with 10 earlier)

Cheers!

KL
Verified answer

Guy-JM 95 on at

Like (1)

Report
RE: Updating Inventory With Lot No Using Config Package

I have used this process successfully myself so can verify this does work.

May I suggest the following method to troubleshoot your data/process.

Manually create an item journal with a few lines of data with the item tracking and then download the configuration package.

Delete the entries in the Item Journal in BC

Import your configuration package and check the journal has populated correctly with Item Tracking

If that works successfully, it suggests you have not managed to format your data correctly. If it doesn't work, you will be missing a required field in your config pack.

In the latter case, I'd export the whole reservation table and repeat the above, it should hopefully become quite clear which field you've missed.
Suggested answer

MahGah 15,437 on at

Like (1)

Report

RE: Updating Inventory With Lot No Using Config Package

Sorry KL that the method did not help,

My only other suggestion is to create Item Journal for 2 items. Then add the Lot to them. Then download table 83 and 337 to see if you see how BC set these up. Maybe you can replicate that and have success.
KL Ang 35 on at

Like (0)

Report

RE: Updating Inventory With Lot No Using Config Package

Hi MahGah,

I followed your instructions carefully, use a new set of data, download a package, update to the instructions, import into BC. The journal lines are created but when I check in Item Tracking Lines, no lot numbers were imported for any of the 6 items that were imported.

Thank you very much for your effort. I really appreciate your time to help.

Best Regards.

KL
Suggested answer

MahGah 15,437 on at

Like (1)

Report

RE: Updating Inventory With Lot No Using Config Package

Forgot to add that in 337 I took SN field but you need Lot field. Also, Item Tracking (the last column in 337) it the place that you define Lot or SN.
Suggested answer

MahGah 15,437 on at

Like (1)

Report

RE: Updating Inventory With Lot No Using Config Package

Hi

Can you try this method. I included some screenshot.

Download table 83 and 337 under one package through Configuration Package. (each table will be it's own tab in Excel).

Then populate 83 with info you need for QTY make sure to break down the QTY. Also, you need to add a Line No here since you have to refer back to it in table 337.

1) if you have SN no need to break down enter total QTY

2) if you have lot each QTY should reflect lot QTY that you have for that lot

Then in 337 populate the info you need to refer to table 83 and also populate a few extra fields. See my example below and see some info here. I used SN here but for lot you can enter the QTY for lot but make sure Source match the line in table 83 (https://forum.mibuso.com/discussion/15389/field-quot-source-type-quot-in-table-quot-reservation-ent)

Then upload them both and try
KL Ang 35 on at

Like (1)

Report

RE: Updating Inventory With Lot No Using Config Package

Hi MahGah,

Your reply looks promising as I could download the Lot No as I can't with table 83. However I still could not get the job done. I think maybe the issue is source type, your example shows 83 but mine was exported to Excel with 32. What I did was change it to 83 but it did not work. Could this be the issue and if yes, how to get the export to Excel come from 83 in the source type field?

Best Regards.

KL Ang
Suggested answer

MahGah 15,437 on at

Like (1)

Report

RE: Updating Inventory With Lot No Using Config Package

Hi

See this articles it will give you an idea.

http://shehanalmeida.blogspot.com/2016/01/uploading-item-journals-with-serial-or.html