Creating unique key on User table using SAP B1 SDK

[Former Member]

Hi. Hope everyone is fine here. I want to ask how to add unique key on User table's column using SAP B1 SDK. Like we add unique key from Tools ---> customization tools ----> User defined fields -------> select table and add unique on it for a column. I want to add this key programmatically. Do anyone have idea?

[pedro_magueija · Accepted solution]

Hi Saad,

You can mark the user defined field as unique by creating a user key.

Here is an example from the SDK Help Center:

Private Sub AddUserKey()
'//****************************************************************************
'// The UserKeysMD represents a meta-data object that allows you
'// to add\remove user defined keys.
'//****************************************************************************
    Dim oUserKeysMD As SAPbobsCOM.UserKeysMD
    '//flag
    Dim bFlagFirst As Boolean
    bFlagFirst = True
'//****************************************************************************
'// In any meta-data operation there should be no other object "alive"
'// but the meta-data object, otherwise the operation will fail.
'// This restriction is intended to prevent collisions.
'//****************************************************************************
    '// The meta-data object must be initialized with a
    '// regular UserKeys object
    Set oUserKeysMD = oCompany.GetBusinessObject(oUserKeys)
    '// Set the table name and the key name
    oUserKeysMD.TableName = "OCRD" '// BP table
    oUserKeysMD.KeyName = "BE_MyKey1"
'//*******************************************
'// Add a column to a key button:
'//-------------------------------------------
'// To add an additional column to
'// the key, an additional element must be
'// created in the Elements collection.
'// The Add method of the Elements collection
'// must be used only as of the second element.
    '// Do not use the Add method for the first element
    If bFlagFirst = True Then
        bFlagFirst = False
    Else
        '// Add an item to the Elements collection
        oUserKeysMD.Elements.Add
    End If
    '// Set the column's alias
    oUserKeysMD.Elements.ColumnAlias = "The required field name comes here"
'// Determine whether the key is unique or not
    oUserKeysMD.Unique = tYES
'// Add the key
    oUserKeysMD.Add
End Sub 

These keys allow you to mark a field (or more) as being unique.

Pedro Magueija

LinkedIn | Twitter | Blog

[pedro_magueija]

Hi Saad,

With the latest SDK you can create an AutoIcrementing UDT (User Table). That will automatically add a "sequence" on the code field.

Pedro Magueija

LinkedIn | Twitter | Blog

[Former Member]

Hi @ Pedro Magueija

Thanks for your answer. I have created a table with auto increment code. That's not my problem. I want to make another column of that table to be unique. How to do it? can you please let me know.