Creating a key on existing tables (BC)

Potti

Hi,

I want to know if creating a new key (primary / secondary) on existing tables (EG: Item) using AL is possible. If so, how can I code it down?

borealis

To the best of my knowledge this isn't permitted. I haven't read up on the latest cumulative updates so may be mistaken, but I know that as of CU4 this was not allowed.

The only thing you can do key wise is add additional keys using added extension fields. Also worth noting that you cannot adjust the field groups. We have a client that has an extended item identifier (100 chars) and the shenanigans we had to go through to make that easily searchable/sortable without redoing everything involved were extensive.

Our theory is that this is because the extension based fields/keys are stored in a separate table in SQL and the base tables are considered set in stone. Thus you cannot add a key that mixes, for instance, the product group code (original table, in master database table) and "custom subproduct group" (extension field, stored in the secondary extension table) as they're in completely different tables.

borealis

To the best of my knowledge this isn't permitted. I haven't read up on the latest cumulative updates so may be mistaken, but I know that as of CU4 this was not allowed.

The only thing you can do key wise is add additional keys using added extension fields. Also worth noting that you cannot adjust the field groups. We have a client that has an extended item identifier (100 chars) and the shenanigans we had to go through to make that easily searchable/sortable without redoing everything involved were extensive.

Our theory is that this is because the extension based fields/keys are stored in a separate table in SQL and the base tables are considered set in stone. Thus you cannot add a key that mixes, for instance, the product group code (original table, in master database table) and "custom subproduct group" (extension field, stored in the secondary extension table) as they're in completely different tables.

TrippyZ

What problem are you trying to address? If you want a key changing on a table raise a ticket on partnersource.

Potti

borealis wrote: »

To the best of my knowledge this isn't permitted. I haven't read up on the latest cumulative updates so may be mistaken, but I know that as of CU4 this was not allowed.

The only thing you can do key wise is add additional keys using added extension fields. Also worth noting that you cannot adjust the field groups. We have a client that has an extended item identifier (100 chars) and the shenanigans we had to go through to make that easily searchable/sortable without redoing everything involved were extensive.

Our theory is that this is because the extension based fields/keys are stored in a separate table in SQL and the base tables are considered set in stone. Thus you cannot add a key that mixes, for instance, the product group code (original table, in master database table) and "custom subproduct group" (extension field, stored in the secondary extension table) as they're in completely different tables.

So, its not possible to add keys to an already existing keygroups. Thanks for the info. When I tried to add a primary key, an error popped up indicating a table-extension cannot add a primary key though I was successfully able to add secondary keys.

Slawek_Guzek

@borealis you cannot create a "mixed" key having fields from base table and the extension for a very simple reason - the extension data is physically stored in a separate table.

When you access table with an extension NAV Server creates in the background a query joining the base table with the extension table, and the results is visible in code as a one record. The SQL Server however is not capable of creating keys that span many tables, therefore key mixing base table fields and extension fields will be probably never possible.

Potti

> @Slawek_Guzek said:
> @borealis you cannot create a "mixed" key having fields from base table and the extension for a very simple reason - the extension data is physically stored in a separate table.
>
> When you access table with an extension NAV Server creates in the background a query joining the base table with the extension table, and the results is visible in code as a one record. The SQL Server however is not capable of creating keys that span many tables, therefore key mixing base table fields and extension fields will be probably never possible.

So what can be the workaround for it? If I want a key group consisting of one key from base table and one key created by the extension.

TallyHo

"So what can be the workaround for it? If I want a key group consisting of one key from base table and one key created by the extension."

First and foremost, the use of the term Key group is not correct I hope? Key groups were a feature in older NAV versions.. and if we're talking here about creating them in AL on Tables split by extensions.. I'm off.

Well ugly as it is.. Create copy, named differently, of all fields of the targeted key in the base table and add them to the extension. Create the key in your extension but now with the copied fields and your custom fields.

Create subscribers
C49OnAfterOnDatabaseInsert
C49OnAfterOnDatabaseModify
C49OnAfterOnDatabaseRename
To update the fields with the same value as the original fields.
This probably has a lot of cons, but I cannot see any other way.

Potti

TallyHo wrote: »

First and foremost, the use of the term Key group is not correct I hope? Key groups were a feature in older NAV versions.. and if we're talking here about creating them in AL on Tables split by extensions.. I'm off.

I apologize, yes I meant field groups.

TallyHo wrote: »

Well ugly as it is.. Create copy, named differently, of all fields of the targeted key in the base table and add them to the extension. Create the key in your extension but now with the copied fields and your custom fields.

Create subscribers
C49OnAfterOnDatabaseInsert
C49OnAfterOnDatabaseModify
C49OnAfterOnDatabaseRename
To update the fields with the same value as the original fields.
This probably has a lot of cons, but I cannot see any other way.

Yes, there is probably no workaround without cons in this case. Thanks for your input @TallyHo

kriki

Slawek_Guzek wrote: »

@borealis you cannot create a "mixed" key having fields from base table and the extension for a very simple reason - the extension data is physically stored in a separate table.

When you access table with an extension NAV Server creates in the background a query joining the base table with the extension table, and the results is visible in code as a one record. The SQL Server however is not capable of creating keys that span many tables, therefore key mixing base table fields and extension fields will be probably never possible.

In a way, it is possible: creating a view on the key fields and then putting an index on it. SIFT is implemented this way. But I don't know how that would go for performance. And also the upgrade routines should be changed. they should first drop the key and maybe also the view, update the table (or extension table) and then recreate the view+index.