Update Counting Journal with Excel Add-in

(0) Share

Report

Posted on by MattA

I am attempting to update a counting journal via the Excel Add-in. I have created a counting journal and imported records into the journal for counting.

In Excel, I selected the InventJournalTrans table and filtered for my new journal number.

7624.excel1.png

I updated the "Counted" value on the first record, published the data and got this error:

"The value 'HOND-000905' in field 'Lot ID' is not found in the related table 'Inventory transactions originator'."

1348.excel2.png

Any idea what is causing this?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (2)

Sort by

Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

Lot ID is a field in InventTransOrigin.InventTransId. Do you have the value above in that table/field?

Was this reply helpful? Yes No
MattA on at

Like (0)

Report
Copy link

Link copied!

I have Lot ID in my Excel spreadsheet, but I noticed that those Lot ID's do not appear in the InventTransId field on the InventTransOrigin table. Is that because the journal is not yet posted?

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

I think this is normal.

I am only seeing an inventory transaction for my counted items in case of a difference. So my guess is that the system temporarily allocates Lot IDs for these journal lines, which are then released if there is no change in quantity. It is most likely not meant to be updated from Excel, since that will do the validation for the connected tables, in which case InventTransOrigin.InventTransId will make it fail.

Was this reply helpful? Yes No
Verified answer

ColbyGallagher 3,668 on at

Like (1)

Report
Copy link

Link copied!

Vilmos is correct. It sounds like you pushed all the rows in without quantities, and now you are trying to input quantities on a second pass? If thats the case, yes, your LotID's are temporary and can't be updated. Push the quantities in with the transaction on the first pass.

Was this reply helpful? Yes No
Muhammad Haroon 2,376 on at

Like (0)

Report
Copy link

Link copied!

Hi All

Can anyone of you could let me know how you have resolved this issue because I am also facing this problem.

Thanks in advance

Haroon

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Muhammad,

The fact that the Lot ID's are temporary is what prevents us from using the Excel Add-in for this. We will be using Atlas for this process going forward. They are integrated with Excel and have developed a spreadsheet that allows you to update the counting journal.

Was this reply helpful? Yes No
Muhammad Haroon 2,376 on at

Like (0)

Report
Copy link

Link copied!

Thanks Matt for your reply.

Was this reply helpful? Yes No
Shahzad Sarang 90 on at

Like (0)

Report
Copy link

Link copied!

Hi All,

By using X++ customization on counting journal and using VBA coding on Excel, we have made a workaround which got us through this functionality

Now we are using Excel Add-in spreadsheet to update counting journal records

Was this reply helpful? Yes No