Creating a key on existing tables (BC)

PottiPotti Posts: 11Member
edited 2019-06-12 in NAV Three Tier
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?

Best Answer

Answers

  • TrippyZTrippyZ Posts: 86Member
    What problem are you trying to address? If you want a key changing on a table raise a ticket on partnersource.
    Professional Navision Developer
  • PottiPotti Posts: 11Member
    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.
  • BlackTigerBlackTiger Posts: 1,222Member
    Simply do not use 'cloudy' NAV. Solved.
    "You can’t just ask customers what they want and then try to give that to them.
    By the time you get it built, they’ll want something new.” Steve Jobs
  • Slawek_GuzekSlawek_Guzek Posts: 1,683Member
    @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.

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • PottiPotti Posts: 11Member
    edited 2019-06-21
    > @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.
  • TallyHoTallyHo Posts: 282Member
    edited 2019-06-21
    "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.
  • PottiPotti Posts: 11Member
    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

  • krikikriki Posts: 8,768Member, Moderator
    @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.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
Sign In or Register to comment.