Adding Valid Values to an existing database field.

[Former Member]

HI,

I'm trying to add a valid value to a database field that already has other valid values. I'm using the oUserFieldsMD object but Business One won't add it. I get the following message:

" [CUFD.EditType] , 'Field cannot be updated (ODBC -1029)'"

Am I missing something? My function is below.

Private Function AddToValidValidValues(ByVal TableName As String, ByVal FieldName As String, ByVal ValidValues As String) As Boolean

Dim oUserFieldsMD As SAPbobsCOM.UserFieldsMD

Dim ValueString As String

Dim DelimiterPosition As Integer

Dim Err As Long

Dim lErrCode As Long = 0

Dim ErrMsg As String = ""

Dim Result As Boolean = False

Try

oUserFieldsMD = oUtilCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields)

With oUserFieldsMD

.TableName = TableName

.Name = FieldName

Do Until ValidValues = ""

ValueString = ParseValidValues(ValidValues, "|")

DelimiterPosition = InStr(ValueString, "-")

.ValidValues.Value = Trim(Left(ValueString, DelimiterPosition - 1))

.ValidValues.Description = Trim(Right(ValueString, Len(ValueString) - DelimiterPosition - 1))

.ValidValues.Add()

Loop

Err = oUserFieldsMD.Update

If Err Then

oUtilCompany.GetLastError(Err, ErrMsg)

If Err = -2004 Then

oUtilApplication.MessageBox(TableName & ": " & ErrMsg)

Else

oUtilApplication.MessageBox(FieldName & ": " & ErrMsg)

End If

Else

Result = True

End If

End With

Catch ex As Exception

Throw ex

Finally

System.Runtime.InteropServices.Marshal.ReleaseComObject(oUserFieldsMD)

KillObject(oUserFieldsMD)

GC.Collect()

End Try

Return Result

End Function

The ValidValues parameter could look like "A - ValueA | B - ValueB" if the intent were to add two. Assume that the parse function works.

Thanks,

Mike

[YatseaLi]

Hi Mike,

Do it this way:

-add a new line for valide values,

-then set the current line to the last one.

-set the value and description for it.

-update udf md

Sample code:

Dim oUdfMD As SAPbobsCOM.UserFieldsMD = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields)
        If oUdfMD.GetByKey("@SMC_MELVIN", 1) Then
            Dim oValidValues As SAPbobsCOM.ValidValuesMD = oUdfMD.ValidValues
            oValidValues.Add()
            oValidValues.SetCurrentLine(oValidValues.Count - 1)
            oValidValues.Value = "4"
            oValidValues.Description = "Type 4"

            lRetCode = oUdfMD.Update
            If 0 <> lRetCode Then
                oCompany.GetLastError(lErrCode, errMsg)
                MsgBox(errMsg)
            Else
                MsgBox("Validvlues updated.")
            End If
        End If

        System.Runtime.InteropServices.Marshal.ReleaseComObject(oUdfMD)
        oUdfMD = Nothing
        GC.Collect()

Regards, Yatsea