Auto fill field when creating record based on a forumla RRS feed

  • Question

  • CRM2013 - Accounts

    Each customer has an licence number. What I want to do is when a user selects create record, the new_licence field is auto filled with the next licence number. Our licence numbers start with the year

    ie. for this year (2013) they started 130000, 130001, 130002 etc

    how can I query the account entity and get the next licence number ( ie, the first new record added in 2014 would be 140000) and auto fill the new_licence field.

    I have looked at a business rule but cannot see a way of doing it.

    In SQL this would be the formula,

    Declare @strLicence varchar(6)
    Select @strLicence = ISNULL(MAX(Licence) + 1, RIGHT(CAST(YEAR(Getdate()) as varchar(4)), 2) +'0000')
     From dbo.client
     WHERE LEFT(Licence, 2) = RIGHT(CAST(YEAR(Getdate()) as varchar(4)), 2)
     Select @strLicence as 'Next Licence'

    Dont ask me .. i dont know

    Monday, December 30, 2013 7:41 PM