Should I Insert Records into the [Item Attribute Value Mapping] table that don't have a corresponding record in the [Item] table

Hello,

We transitioned to Dynamics NAV 2018 from another ERP and we have a lot of old items that we didn’t want to import to Nav because they were no longer being sold but we still need them for historical reporting. We could import and block them but we want as little clutter as possible on the items list page.

Effectively, I wanted to add item attributes for these items in Nav without setting up an item card and I noticed that Nav will let me import records into the [Item Attribute Value Mapping] table even though the item does not exist as a separate item card. Even though I’m able to do it I wanted to know if it is a good idea or if I am playing with fire.

The only downside to my method that I have seen thus far is that I cannot use the Filter by Attributes button on the items page when trying to filter by an attribute that is assigned to at least 1 non-existent item.

I get the message “The Item does not exist. Identification fields and values: No.=xxx”

Maybe it is better to create a separate table within Nav to hold these items and attributes? Seems like it would require a lot of customization to union the secondary item attributes table with the original for all reports, queries, pages, etc.

Appreciate any advice.

Hello!

i think, In this case you have two options:

  1. Add Items to Item table and hide these items with fixed filters on the item list.

  2. Add code to 7500 Codeunit to catching errors like “Item does not exist”.

You say that you need the old items for historical purposes. How do they want to do this and where?

I have over the years seen this requirement more times than I can remember, but only a few of them really needed them in the end.

Most of the times it were really enough to have a copy of the old ERP, with a read/only access available for a year or two after the transition. If it is not possible to have the old ERP accessible then I have had customers where their historical sales were “migrated” to a Excel “solution”.

It really depends on what other historical data you have migrated. I.e. if you have migrated old posted invoices (not recommended), then you would need the actual item.

I also had customers where we created new tables for historical data, i.e. hist. sales entries, but the amount of time we ended up using for creating it, did not match the value they actually got from having this data.

So I would suggest that you go over your list of old items once again. Then import (and block) those you maybe would use in the future and leave everything else where it is or save it in Excel.

We migrated sales invoices to a SQL table outside of our Nav database and my colleague suggested simply adding columns for the attributes in this table rather than try continue with my method of trying to jam everything in Nav. Never occurred to me for some reason.
Most of our reporting is done via Excel power query where I union the Nav data with this separate SQL table and so far the users seem content that the info is not actually in Nav.
So in summary, I’m just going to add the attributes as columns to the historical sales invoice table. Thanks for your advice Erik.

Thanks for the suggestions Alexey, I wasn’t aware of fixed filters on pages in Nav. I’ll have to look into it more.