Sage 300 ERP Optional Fields
Optional Fields were added to Sage 300 ERP as the major feature for version 5.3A. They are a great way to add custom data fields to many master and transaction screens in Sage 300. They also have the benefit that we can flow them with the transactions through the system, for instance from an O/E Order to the corresponding A/R Invoice to the G/L Batch. This opens up a lot of power for tracking extra data in the system and to do sophisticated reporting based on it.
In this article we will look at some of the programming consideration when dealing with Optional Fields at the API level. We’ll use the Sage 300 .Net API to explore how to deal with a few things in that come up with Optional Fields.
Many programmers feel they can ignore Optional Fields and their program works perfectly for sample data, but as soon as they install it at a customer site, it fails. A common cause of this is that the customer has required optional fields that cannot be ignored and the API program needs to be updated. (Another common failure at customer sites is caused by not dealing with locked fiscal periods).
From my previous article on the View Protocols, any optional field view is an ordered detail where its header is whatever it is an optional field for. An ordered header/detail means that you set the value of the key. But you do have to be careful to setup your optional fields correctly in the application’s setup UI since there is validation on these.
In the sample program I’ll give a complete set of steps. In some situations some of the steps can be skipped, for instance you don’t really need to call RecordClear and RecordGenerate for the header optional fields, but I’ll leave them in since sometimes you do, and it’s easier to just use a formula that always works rather than re-thinking everything each time.
Since Optional Fields are often sub-details of details that are stored in revision lists, you sometimes need to be careful that things exist before using them. Revision Lists are our mechanism to store things in memory before they are written to the database in a single database transaction. So until you save the header, nothing is in the database and everything is in memory. The Revision List store the list of details that have been manipulated so far. The Optional Fields are themselves stored in Revision Lists until the big database transaction happens.
In the sample program we insert the detail before adding this optional fields. Until the detail is inserted there is nothing to attach the optional fields to, so we need to do that first. The Insert operation only adds the detail record to a revision list in memory, but once that is done we can add sub-details (ie Optional Fields) to it (which are also stored in memory).
For a sample program, I modified the ASP.Net MVC sample ARInvEntry to save a couple of optional fields for the header and a couple of optional fields for the detail. Below is a subset of the code to insert the detail optional fields:
// 9. Insert detail. arInvoiceDetail.Insert(); // Insert the detail line (only in memory at this point). // 10. Add a couple of detail optional fields. arInvoiceDetailOptFields.RecordClear(); arInvoiceDetailOptFields.RecordGenerate(false); arInvoiceDetailOptFields.Fields.FieldByName("OPTFIELD").SetValue("EXTWARRANTY", false); arInvoiceDetailOptFields.Fields.FieldByName("VALIFBOOL").SetValue(true, false); arInvoiceDetailOptFields.Insert(); arInvoiceDetailOptFields.RecordClear(); arInvoiceDetailOptFields.RecordGenerate(false); arInvoiceDetailOptFields.Fields.FieldByName("OPTFIELD").SetValue("WARRANTYPRD", false); arInvoiceDetailOptFields.Fields.FieldByName("VALIFTEXT").SetValue("180 Days", false); arInvoiceDetailOptFields.Insert(); // 10.5 Register the changes for the detail. arInvoiceDetail.Update(); // 11. Insert header. (This will do a Post of the details.) arInvoiceHeader.Insert();
Different Field Types
If you look in the database you will see that the Optional Field tables don’t hold that many fields, but any Optional Field View has quite a few fields, but many are calculated. Optional Fields can be all sorts of different types, but in the database all the values are stored in a single VALUE field regardless. So there has to be a conversion to/from this text field and the real type. This is the job of all the VALIFtype fields. Basically you use the VALIF field based on the type of the Optional Field and then the View will handle the conversion to and from the type as stored in the VALUE database field. That is why we used the fields VALIFTEXT and VALIFBOOL above.
In the sample program I just inserted the optional fields myself. However there is another way. Views that have optional fields usually have a field called PROCESSCMD (or something similar) where you can set a value with a title like “Insert Optional Fields” which will insert the optional fields that are needed. You can set this field and call Process on the View and it will insert these optional fields for you. Then you can read the optional field, set its value and update it. Some people find this an easier way to do things and you get all the optional fields with their default values as a bonus. (Note that this only applies to Optional Fields that are set to auto insert in the applications Optional Fields setup screen).
Similarly for Views that will transfer in Optional Fields (like from an Order to a Shipment), you will see PROCESSCMD’s like “Default and Transfer Optional Fields”. If you are doing API programming and want to preserve the flow of Optional Fields, you will occasionally have to set one of these and call Process.
When dealing with Optional Fields, it’s worth checking out the PROCESSCMD functions of the main View to see if they will help you do your job and save you a fair bit of coding.
Many optional fields have a list of valid values. If you don’t set it as one of these you will get an error message to this effect. When dealing with optional fields make sure you have an error handler to show the errors after an exception as explained here. If you want to get at these values they are in CSOPTFD CS0012 (a detail of CSOPTFH CS0011). You can read through these views like any other as explained here.
Optional Fields are a powerful feature in Sage 300 ERP. Many customers use these in a fundamental way to support their businesses. This means that developers working in the Sage 300 world have to be cognizant of these and make sure their programs are compatible with their use.