Auto increment on insert RRS feed

  • Question

  • Hi all

    I am fairly new to Lightswitch/VB and I know this is a well discussed topic. Have spent many hours reading through this forum and trying to get this to work with no success.

    Basically all I am trying to do is have a unique integer field called "ContractNumber" in an entity called "Contracts" which auto increments for each new contract that's created.

    There will mainly be one user creating contracts but in the event of multiple users entering contract information I need to prevent the same contract number being assigned to 2 different contracts.

    I have tried writing code into contracts_inserting but not having much joy.

    Any help you can offer will be much appreciated.

    Wednesday, March 20, 2013 12:31 PM


  • Thanks guys for you help and suggestions.  Finally decided to include 4 characters from the business name field followed by a random number.
    • Marked as answer by Angie Xu Friday, March 29, 2013 7:31 AM
    Tuesday, March 26, 2013 3:12 PM

All replies

  • Let me ask a couple of important questions first (although you may have seen them in the threads you've read).

    1. Does the ContractNumber value "matter"? (Can it be any number, maybe padded on the left with zeroes to a particular number of characters ie "0000100"?)
    2. Do the numbers HAVE to be sequential, with no gaps?

    If the answer to both is "no", then your job is MUCH easier, & you can simply use the value of the entity's ID property, in either a computed property, or in a stored property.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
    By doing this you'll help others to find answers faster.

    Wednesday, March 20, 2013 1:20 PM
  • Hi Yann

    Thanks for your reply.  The answer to both of your questions is no and it would be good if the ContractNumber could be padded out so that it is always a minimum of say 5 characters. 

    I have no idea how to go about setting this up and ideally I would like it to be a stored property.  Would you be able to give me some pointers?

    Wednesday, March 20, 2013 2:23 PM
  • Maybe i understand not the entire problem, but it looks like that you a try to invent an existing feature?! ;-)

    If you create a table in LightSwitch this table entity has always an primay key as integer with auto increment feature.


    if you used an external SQL Server database you can also design an auto increment pk.

    So, the database ensures that no duplicate contract number can be saved.



    Wednesday, March 20, 2013 2:37 PM
  • Hi Andreas, thanks for your reply.

    I'm not using an external data source and I need to be able to display the value on a screen padded out to 5 characters.  Is this still possible?

    Wednesday, March 20, 2013 4:51 PM
  • hi!

    maybe you can do it on server using "entity_inserting" method (go to your table in designer, you'll see in upper right corner dropdown with title "write code", click on that and it will show you methods available for your entity).. you can also put "ContractNumber" in unique index (in table designer click on contractNumber and find in properties "Include in unique index" checkbox) to ensure that same contract number does not have multiple contracts.. i suppose that contractNumber is integer so you can put simple validation like min value = 1 and max value = 99999. after that you can write code in inserting method something like:

    Private Sub Contracts_Inserting(entity As Order)
                Dim ord = Me.Application.CreateDataWorkspace.ApplicationData.Contracts.OrderByDescending( _
                    Function(o) o.ContractNumber).FirstOrDefault()
                If ord IsNot Nothing Then
                    entity.ContractNumber = ord.ContractNumber + 1
                    entity.ContractNumber = 1
                End If
            End Sub

    downside of this approach is that ContractNumber can be shown only after save, so this would be applicable only if you don't need this information when creating contract.. i guess this is not perfect, but maybe you'll get some idea.. hth..


    Nobody expects the Spanish Inquisition! (M.P.F.C.)

    Wednesday, March 20, 2013 5:26 PM
  • Yes, but you have to add a computed property. You can't set a format value on the pk but on the calculated property. Just insert 00000

    If you created the computed property write one line of code: result = id;  


    Wednesday, March 20, 2013 9:26 PM
  • Thanks guys for you help and suggestions.  Finally decided to include 4 characters from the business name field followed by a random number.
    • Marked as answer by Angie Xu Friday, March 29, 2013 7:31 AM
    Tuesday, March 26, 2013 3:12 PM
  • Hello,

    How is this done with SQL server? I am trying to use an AddEditScreen to create a new DeviceType.

    DeviceType is a SQL table, and an entity in LS. I can input data into all of the required columns on my AddDevice screen (shown as dialogue) but unless a user knows the value of the next PK row, they do not know what to put in the IntDeviceTypeID column. This causes validation error.

    Thank you!

    Friday, January 29, 2016 4:54 PM