none
Maintaining contiguous code id's by applying business rule

    Question

  • I deleted some records out of an entity, I'd like to keep the Codes as contiguous and incremental, meaning no breaks between the code numbers.

    I created a business rule and applied it but codes remain the same.

    I used the "Default to a generated Value" action, then selected the Code attrib. --Saved.

    Then back to the Entity, I applied business rules. But nothing seemed to have happened. As there was no change in codes.

    There are no validation errors either.

    What might I be missing?


    --ACG

    Wednesday, February 29, 2012 8:20 PM

Answers

  • Hi ACG

    I am assuming that this is an entity where you enabled code generation by modifying the entity properties. Because of this new feature, we do not support using business rules to auto-generate codes with Sql Server MDS 2012. To reset the code generation counter try doing this:

    1. Go into System Administration

    2. Open the Manage menu and select Entities

    3. Select the entity in question and click the pencil icon to edit it

    4. Turn off "Create code values automatically" and click the Save icon

    5. Turn on "Create code values automatically" and click the Save icon again.

    This should reset the auto code generation counter and the next code generated will be one larger than the largest code value. Bear in mind that if your entity already has a large number of members it might take some time to reset the counter. Let me know if this works for you.

    Arun


    Arunjeet Singh (Microsoft SQL Server Master Data Services)

    Monday, April 30, 2012 8:04 AM

All replies

  • Hi ACG

    I am assuming that this is an entity where you enabled code generation by modifying the entity properties. Because of this new feature, we do not support using business rules to auto-generate codes with Sql Server MDS 2012. To reset the code generation counter try doing this:

    1. Go into System Administration

    2. Open the Manage menu and select Entities

    3. Select the entity in question and click the pencil icon to edit it

    4. Turn off "Create code values automatically" and click the Save icon

    5. Turn on "Create code values automatically" and click the Save icon again.

    This should reset the auto code generation counter and the next code generated will be one larger than the largest code value. Bear in mind that if your entity already has a large number of members it might take some time to reset the counter. Let me know if this works for you.

    Arun


    Arunjeet Singh (Microsoft SQL Server Master Data Services)

    Monday, April 30, 2012 8:04 AM
  • You can do it by updating the values in MDM database (backend). Just update the "LargestCodeValue" column value to whatever value you want or ) to initialize from 1. Other way to do it from MDM portal, however it's time consuming if you have modify it for multiple entities.

    • Below is the code to see / verify the current identity value for entity -

    SELECT e.NAME,
      i.LargestCodeValue
    FROM   mdm.tblEntity e
      JOIN [mdm].[tblCodeGenInfo] i
    ON i.EntityId = e.id
    WHERE  model_id = 3
      AND NAME = 'ABC' 

    • Below is the code to update/ reseed the identity value for entity -

    UPDATE i
    SET   i.LargestCodeValue = 5
    FROM   mdm.tblEntity e
      JOIN [mdm].[tblCodeGenInfo] i
    ON i.EntityId = e.id
    WHERE  model_id = 3
      AND NAME = 'ABC' 

    This will reset the identity value for 'ABC' entity to 5.


    Monday, July 27, 2015 4:54 AM