locked
How to set a value according to a specific pattern RRS feed

  • Question

  • My customer has a specific numbering system that I want to autogenerate using a Business Rule in the following:

    MyCode = "T" + "0000" + Code

    Having:

    MyCode could preferably be Code in the entity.

    Code is code of the entity with autoincrement enabled.

    The total length of MyCode should always be 10 ("T" + x number of "0" + incremented Code)

    Is this possible using Business Rules or anything else?

    Thanks Stig

    Wednesday, January 15, 2014 4:34 PM

Answers

  • Custom Workflow is not hard to implement, and by far the best solution here.
    Tuesday, January 21, 2014 2:58 PM

All replies

  • Ok I guess the simple answer would be no. (with ought a custom workflow that is)

    If you need this bad then MAYBE this headache, depending on how far you go with it, can accomplish something for you:

    ( I would probably try ti

    create another Entity with your future Keys / Codes

    MyCodesEntity

    1 T00001

    2 T00002

    3 T00003

    ...

    100 T00100

    In your Original table Create an Attribute call it something like IndexIntoMyCodes (you can make width = 0 to not show it).

    Create a Domain Based Attribute in this Original table to MyCodesEntity.

    Create Business Rule to default this Index to "defaults to a generated value".  

     Create a BR to set the Code of the Domain Based Attribute above to the IndexItoMyCodes.

    Then use another BR to pull the Code from the MyCodesEntity.


    • Edited by EthernetIP Monday, January 20, 2014 9:10 PM
    Monday, January 20, 2014 9:09 PM
  • Are you looking for the below:

    create Table testtable(code int identity(1,1), MyCode as 'T'+Right('000000000'+cast(Code as varchar(9)),9), Name varchar(100))
    
    
    Insert into testtable Select 'SQL'
    Insert into testtable Select 'SQLServer'
    Insert into testtable Select 'Server'
    
    Select * From testtable
    
    Drop table testtable

    Monday, January 20, 2014 10:59 PM
  • I haven't tried this method yet, But it worth to try

    you can set a business rule on Regular Expression

    so if that business rule apply that regular expression result into the entity attribute, then what you would require is to just write a regular expression to do a left pad of zeros on the CODE with a "T" at the start, 

    here is an example of such regular expression:

    http://stackoverflow.com/questions/2659058/using-regex-to-add-leading-zeroes

    Part of this solution that I am not sure about is that Does MDS apply the result of regular expression, or it just validate it.


    Regards,

    Reza

    SQL Server MVP

    Blog:   http://rad.pasfu.com  Twitter:   LinkedIn:

    SQL Server Integration Services 2012 Tutorial Videos:     http://www.radacad.com/CoursePlan.aspx?course=1

    Monday, January 20, 2014 11:35 PM
  • I did a check, and the method that I suggested won't work, because MDS only uses Regular expressions for validation purpose not for action.

    So the custom workflow would be the option.

    on the other hand; depends on where they want to see that T000... code you may find other workarounds. For example if the data would be exported out of MDS, then you may use SSIS or T-SQL to generate that coding convention.


    Regards,

    Reza

    SQL Server MVP

    Blog:   http://rad.pasfu.com  Twitter:   LinkedIn:

    SQL Server Integration Services 2012 Tutorial Videos:     http://www.radacad.com/CoursePlan.aspx?course=1

    Monday, January 20, 2014 11:46 PM
  • Thank you, this is exactly what I need, but working inside MDS. I did consider this approach and use it in a view build on top of a subscription view. The thing is that the data stewards cannot see the correct code in the UI, but only through "external tools". Would love to see T-SQL logic in MDS business rules.

    Tuesday, January 21, 2014 8:13 AM
  • Thanks for a very creative answer and I guess it would work. However, I think that it is not easy to explain and maintain practically here, so I guess it sounds more like time for a custom workflow...

    Tuesday, January 21, 2014 8:24 AM
  • You are right, would have been the perfect solution if regex support for Actions.

    Thanks, sounds like I need a custom workflow for such a trivial problem:-(

    Tuesday, January 21, 2014 8:26 AM
  • Custom Workflow is not hard to implement, and by far the best solution here.
    Tuesday, January 21, 2014 2:58 PM
  • Stig,

    I would like to weigh in on what Reza suggested. I encountered this problem in trying to DYNAMICALLY load new records into MDS on a daily basis. New records required new 'codes' (in my case) and they were similar to YOURS (i.e. Fin0001, Sales0001). As I was already pulling the data out from MDS on a nightly basis (to update outstanding the balances on overdue accounts) and because users must use 'existing records / rows',  the method works well.

    In fact what I do and PARDON the dirty language(cursor) is that I use a 'CURSOR'  and a temp table to do the new 'code' logic.  

    I ascertain in two steps the MAX 'code' value is  for the 'Fin' and 'Sales' and work forward from there (using the cursor). The results are then sent out into a staging table for loading into MDS after being checked against a list of codes that the user no longer wishes within the MDS environment.

    Feeding from a temp table into the cursor, does not seem to affect server performance and hence has been accepted.

    Further, I am able to manipulate the OLD nightly MDS data, easier outside of MDS than within.

    I know that this may NOT be the answer that you are looking for, however it is a solution. Do consider what Reza has suggested.

    Finally, should you or anyone else like to have a look at some of the SSIS packages that I have developed FOR THIS PURPOSE, please feel free to contact me.

    sincerest regards Steve Simon (SQL Server MVP)

    Tuesday, January 21, 2014 3:50 PM