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.