Inventory settlements table

(0) Share

Report

Posted on by Community Member

Our inventory settlements table is growing exponentially, is there a problem with archiving last year's records and then truncating them from the settlements table?

Thanks!

Melissa Ingram

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (5)

Verified answer

JamesF 165 on at

Like (0)

Report

You should not do that

Unfortunately with average costing before AX 2009 in you can have maybe around 30% of your DB made up of this table!

This table is important to the whole inventory costing in AX

Which version of AX is this for?

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 301,069 Super User 2025 Season 2 on at

Like (0)

Report

Hi Melissa,

I agree with JamesF. The table is used for all inventory adjustments due to inventory value models. Itnis maintained by the inventory closing and adjustment. The value displayed on inventory transactions depends on this table. Also some reports needs this table for correct inventory values.

So please do not truncate this table.

Was this reply helpful? Yes No
Verified answer

Denis Patrakov on at

Like (0)

Report

Check out if you have many records with Cancelled flag set - those can be deleted. Yet you might have many such records only in case you often run invent cost recalculations and then cancel them before the actual invent closing.

Was this reply helpful? Yes No
Verified answer

Brandon Wiese 17,788 on at

Like (0)

Report

There is a built-in process that cleans up InventSettlement records by combining multiple adjustments to the same inventory transaction into a single record, and this can significantly reduce the size of the table, even though it takes quite a long time to run. This process is safe to run, though I highly recommend you do it in a test environment first to inspect the results and the time investment vs the reduction payoff.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you all for for the feedback on this issue, I will run the "Inventory Settlements clean up" job in the IM/Periodic menu in test and see if this helps. I have 7 companies so will be able to run this in the smaller ones first before running it in one for our headquarters.

Melissa

Was this reply helpful? Yes No
Verified answer

JamesF 165 on at

Like (0)

Report

Just to add, the clean up function does mean loss of data detail if you do the Grouping one.

To be honest I would try to avoid the grouping one and first just try to see how many of the records are Cancelled ones and if that gives a high % of the total then that is going to give you the best result.

Make sure you get the users to test the on hand reports by back dating the reports as far back as possible and comparing the results before and after the clean up.

Finally after saying all that, how big is your DB and this table? Even for the DB I had that had 30% for this table I left it as SQL server can handle it fairly well these days (as mentioned in another post there maybe some need for running profiling to see if any index should be added or improved)

FYI below is the online F1 help for the function (bold is added by me):

Inventory management > Periodic > Clean up > Inventory settlements clean up

Use this form to group closed stock transactions or delete cancelled settlements. Cleaning up closed or deleted stock settlements is especially useful to free up system resources.

Be careful about grouping or deleting stock settlements too close against current date or accounting year as a part of the settlement transaction information is lost.

Note

You cannot make changes to closed stock transactions when they have been grouped, because the settlement transaction information is lost.

You cannot reconcile cancelled stock settlements with finance transactions, if you have deleted the cancelled stock settlements.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you all for your quick response and help on this subject. I really appreciate it.

Melissa

Was this reply helpful? Yes No