Are Item Attribute Value table entries kept indefinitely?

(0) Share

Report

Posted on by Ibu

I'm building an extension that will record historical item attribute value mappings.

For example, if an item's "Color" attribute changes from "Blue" to "Red", the idea is to have a table like so:

Item	Date	Attribute	Value
Walking stick	1/1/2023	Color	Blue
Walking stick	3/6/2023	Color	Red

In designing my table, I'm wondering if I could use Item Attribute ID & Item Attribute Value ID instead of actual names:

Item	Date	Item Attribute ID	Item Attribute Value ID
Walking stick	1/1/2023	1	13
Walking stick	3/6/2023	1	15

I would later join the associated tables for the names.

To decide, I need to know if Business Central will keep Item Attribute Value IDs around forever, or if some cleanup process could delete unused attribute values (without knowing my extension/table still needs them). The behavior isn't clear to me as I see gaps in Item Attribute Value's ID column range.

Taking my example from above again, it means that after the item got its color changed to Red and no other item uses the Blue color, the Item Attribute Value table would still retain its (13; Blue) record, forever.

All responses (5)

Answers (1)

Ibu 25 on at

Like (1)

Report

RE: Are Item Attribute Value table entries kept indefinitely?

Thanks yzhums. I ended up subscribing to the table trigger events directly, as the page events only triggered if attributes were modified from the UI (and we have another extension that bulk modifies attributes without going through the UI, which we needed to know about as well).

Your tip about saving names instead of IDs as IDs can have their names renamed makes sense. Thanks!
Verified answer

YUN ZHU 81,365 Super User 2025 Season 1 on at

Like (0)

Report

RE: Are Item Attribute Value table entries kept indefinitely?

Hi, I personally suggest that you save the name values directly, because users will also modify the value corresponding to the ID.

Maybe you have found a better way to handle this process, but I hope the following events can also help you.
page 7504 "Item Attribute Value List"

In addition, if you just want to save the history, you can actually use the standard Change Log to do it, but the format may not be what you want.

Hope this helps.

Thanks.

ZHU
Ibu 25 on at

Like (0)

Report

RE: Are Item Attribute Value table entries kept indefinitely?

Hi yzhums. Thanks for suggesting using Item Attribute Value Mapping. I'm aware of this table and should have been clearer in my original post that my intention is to build a historical version of it.

Therefore if Item Attribute Value Mapping holds Item <> Attribute ID <> Attribute Value ID mappings, my new table will have Date <> Item <> Attribute ID <> Attribute Value ID mappings. This way, reports can look back at what attribute mapping existed on some historical date.

And to rephrase my original concern; if Business Central prunes the Attribute / Attribute Value tables for those IDs that are no longer mapped to any item after an update, it would be a problem for my history table that still references the deleted Attribute / Attribute Value IDs.

The situation explained more graphically:

Starting context

Item Attribute (table 7500):

ID Name

1 Color

Item Attribute Value (table 7501):

ID Name

1 Blue

2 Red

Item Attribute Value Mapping (table 7505):

Item No. Attribute ID Attribute Value ID

1 1 1

2 1 2

My new table, "Item Attribute Value Mapping History":

Date Item No. Attribute ID Attribute Value ID

January 2023 1 1 1

January 2023 2 1 2

Change event

A Business Central user modifies the attribute value for item #2 in March 2023, such that the mapping tables become (changes in yellow):

Item Attribute Value Mapping (table 7505):

Item No. Attribute ID Attribute Value ID

1 1 1

2 1 1

My new table, "Item Attribute Value Mapping History":

Date Item No. Attribute ID Attribute Value ID

January 2023 1 1 1

January 2023 2 1 2

March 2023 2 1 1

My question

At this point, does Business Central delete the record with ID = 2 in Item Attribute Value (table 7501), since it is no longer used in any mapping (table 7505)? It would make sense from a BC perspective, though it doesn't know my history table still uses that ID. If it does delete it, then I need my history table to store actual name values ("Red") instead of IDs.

I will experiment to verify this myself, but figure it could be the case that I don't see the deletion right away, if it only happens during some monthly maintenance task.
Suggested answer

YUN ZHU 81,365 Super User 2025 Season 1 on at

Like (0)

Report

RE: Are Item Attribute Value table entries kept indefinitely?

Hi, I think you should take a look at table 7505 "Item Attribute Value Mapping", This is already pretty close to the extended functionality you mentioned. As for the ID, I think it is the same as the Key, even if the ID does not change, its value can also change. So as long as the value can be found, I don't think it's really a problem (Maybe I missing some thing).

Hope this helps.

Thanks

ZHU
Ibu 25 on at

Like (0)

Report

RE: Are Item Attribute Value table entries kept indefinitely?

Taking a look at Item Attribute Value's DAL code, specifically its OnDelete trigger, it appears possible to hook into its logic by way of the OnAfterHasBeenUsed event.

I imagine a good approach would be to create a code unit subscribing to that event, and set AttributeHasBeenUsed to true if a query to my history table returns results. With that variable set to true, the OnDeleteTrigger will present a confirmation dialog warning the user that the value is in use somewhere.

Something unclear to me still: the concept of a confirmation dialog in the OnDelete trigger tells me this is for deletions through the web client UI only. Is the trigger involved for programmatic deletes as well, and do those exist (ex.: in some cleanup maintenance task)?