Skip to main content
Post a question

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

Assembly Transaction Lot Numbers

Posted on 10 Oct 2014 12:48:18 by Microsoft Employee

Hello, 

We have had this problem for years, but it has grown exponentially as our business has increased.  I'm hoping someone else might have dealt with the same issue:

We use Assembly Transaction Entry to enter inventory built into GP.  If an item is Lot Tracked, then the number of rows created in the BM30400 table is equal to the quantity of items built.  i.e. If we enter 100,000 items, then 100,000 almost completely identical rows are inserted into the table.  It's as though the insert code is the same for serial tracked and lot tracked.  As you can imagine, after several years that history table is gigantic... over 200 million rows (60 gigabytes with indexes) and doing ANYTHING with that data takes ages and tends to time out. 

Things I think we can/should/hope to do about it:

- Move to the Manufacturing series - in the works, but won't help with the existing data.

- Delete history using GP's Delete Assembly History Routine - testing, takes a very long time since each transaction delete requires lookups of said table.  Also, the boss doesn't necessarily want to lose the information about lots.

- Use the Company Data Archive tool - What testing I have done with this has been slow going since it can only be done in off-hours.  It has the same problems that the GP delete does, but at least we don't lose data.

- Write a custom script to consolidate rows after they are written? - Not sure what impact this would have on the DB.  Also, it wouldn't fix the long delay times in GP when we post these large transactions.  They sometimes take hours, depending on the quantity.

- I would try to modify the SQL insert script GP uses, but again I am not certain of the impact and GP apparently calls each row entry one at a time... so it would require some temp table gymnastics.

Ideally, GP would be the ones to change the way the code works, but I don't see that happening in the near future.  I will mention that I have attempted to work with our partner on this in the past, but their suggestion was the CDA software, which still isn't optimal.

Any other advice?

Thanks for your time,

Michelle

*This post is locked for comments

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on 14 Oct 2014 at 22:36:43
    RE: Assembly Transaction Lot Numbers

    I am afraid the best approach to your problem is the use of the CDA software, which will allow you to be rid of the data to speed processing and allow you to have it available when needed.   This is exactly what CDA was designed to do.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans