Code Snippet: Sage 300 Optional Fields

If you are like me, and write a lot of code that integrates with Sage 300, you probably faced the need to read from or update optional fields. For every optional field, there are a few lines of code that you need to write, whether you are reading or updating the value. I got tired of doing that, and created two procedures that can read or write the value in just one line. Here is the code for reading the value:


    Function GetOneOptionalFieldValue(view As AccpacCOMAPI.AccpacView, FieldName As String) As Object
        view.RecordClear()
        view.Browse("OPTFIELD = """ & FieldName & """", True)
        If view.Fetch Then
            GetOneOptionalFieldValue = view.Fields.FieldByID(view.Fields.Item("VALINDEX").Value).Value
        Else
            GetOneOptionalFieldValue = Nothing
        End If
    End Function

The function returns a property typed value, i.e., for Text fields it returns a String, for Date fields it returns a Date, etc.

And here is the update code:


    Sub UpdateOneOptionalField(view As AccpacCOMAPI.AccpacView, FieldName As String, Value As Object)
        view.RecordClear()
        view.Fields.Item("OPTFIELD").PutWithoutVerification(FieldName)
        If view.Read Then
            view.Fields.Item("VALUE").Value = Value
            view.Update()
        Else
            ' Field does not exist
            view.RecordCreate(AccpacCOMAPI.tagViewRecordCreateEnum.VIEW_RECORD_CREATE_NOINSERT)
            view.Fields.Item("OPTFIELD").Value = FieldName
            view.Fields.Item("VALUE").Value = Value
            view.Insert()
        End If
    End Sub

Each of these functions takes a view as the first parameters. The assumption is that the view is an optional fields view, and it is already composed with it's parent. Composing the optional fields view with it's parent enforces that the parent filter is already applies, so all we need to do is search for the specific field we want to read or update.

For example, updating a customer optional field would look like this:


    UpdateOneOptionalField(viewCustomerOF, "SVCTYPE", "REMOTE")

viewCustomerOF is the AR0400 (Customer Optional Fields) and is already composed with AR0024 (Customers). AR0024 needs to be positioned on the correct customer using Browse/Fetch or Put/Read before calling UpdateOneOptionalField method. We are setting the value of SVCTYPE optional field to "REMOTE". If the field already exists, its value is updated. If the field does not exist, it is inserted with the specified value.

Makes the code a lot more readable.