How to Add User Defined Fields (UDFs) in Sage 100
how to add user defined fields in Sage 100
Also searched as
- Sage 100 UDF Customizer Selection
- Sage 100 add custom field to sales order
- Sage 100 UDF not showing in Crystal report
- Sage 100 user defined field ODBC UDF_ prefix
Short answer
In Sage 100 you add a user defined field through Custom Office: open the Customizer Selection for the module, create the field with a name, type and length, then place it on the relevant data entry panel using the Customizer. UDFs are stored in the module's tables with a UDF_ prefix, which is how they appear in ODBC, Crystal and imports once the field is defined.
Applies to: Sage 100 Standard, Advanced and Premium with Custom Office (Customizer) available; menu text and the exact set of customisable screens vary by version and by the modules installed
Add a UDF and show it on a screen
- 1Make sure you are the only user in the company, or that the Sage 100 version allows customisation while others are logged on. Many sites schedule UDF changes for a quiet period.
- 2Open Custom Office, then Customizer Selection, and choose the module and the screen group where the data lives (for example Sales Order Entry header or Customer Maintenance).
- 3Use the UDF maintenance option to create the field. Set a clear name, the data type (string, numeric, date, or a list), the length or decimal places, and optional default and validation list.
- 4Open the Customizer on the screen and drag or add the field to a visible position, adjusting the label, tab order and read-only status.
- 5Save the customisation, then log out and back in so the panel reloads, and test by entering a record and reading it back.
- 6For reports, refresh the data source in Crystal or Excel so the new UDF_ column is visible.
- 7For imports, re-open the Visual Integrator job and map the new field, since the job does not pick up new columns automatically.
- 8Document the field in a change log with the owner and purpose, because UDFs outlive staff and are hard to retire cleanly.
How UDFs are stored
A UDF is defined at the table level and stored as an extra column on the module's data file. When you create a field named Region on the customer record, ODBC and the Business Object Interface expose it as UDF_REGION, with the prefix added automatically. Because the field lives on the base table, it is available to any report, extract or script that reads that table.
Header and line items are separate tables. A UDF on the sales order header belongs to SO_SalesOrderHeader, while one on the line belongs to the detail table. If you need a value on both the order and the invoice, you define it on each and rely on Sage's copy rules, or create a mapping that copies the value as the order converts. Do not assume a field entered on an order flows to the invoice unless you have checked this behaviour.
Making UDFs useful beyond the screen
UDFs often fail not at creation but at the second step: users add the field to a screen and then find it empty in reports. In Crystal, use the refreshed table definition; in Excel through ODBC, re-run the query. In Visual Integrator, the new field appears in the field list after you re-select the source table. In BOI, read and write the field by its UDF_ name through nGetValue and nSetValue.
Validation matters. A free-text UDF used for a controlled value, such as sales region or inspection class, becomes inconsistent in months. Use a validation table or list where the product allows it, and keep names short and documented. Numeric UDFs used in calculations should define decimals carefully, because changing the decimals later can require rebuilding data.
-- ODBC: reading a UDF defined as Region on the customer record SELECT CustomerNo, CustomerName, UDF_REGION FROM AR_Customer WHERE UDF_REGION = 'WEST'
Limits, upgrades and when not to use a UDF
Customisations created through Custom Office are carried through version upgrades in the normal case, but custom panels should be retested after every update, especially when Sage changes the underlying screen. Some screens cannot be customised, and UDFs do not change the business logic of standard posting.
If you need a calculation, a workflow or a validation that depends on other tables, a UDF alone is not enough. That is the point to look at Sage's scripting options for the version you run, or at a third-party tool. If you only need the data for reporting, consider whether a calculated column in the report layer is cheaper than permanent schema change.
Common pitfalls
- !Defining the field on the order header and expecting it on invoice or ship-to records without checking where it is carried.
- !Using free-text UDFs for controlled values, which produces dirty data in months.
- !Changing a UDF's type or length after data exists without a tested plan and a backup.
- !Forgetting to update Visual Integrator jobs and reports after adding the field.
- !Skipping a retest of customised panels after a Sage 100 update or product update.
How an ERP-grounded AI assistant handles this
An assistant grounded in the Sage 100 data dictionary knows which UDFs exist on each table and can answer questions such as which customers have a given UDF value, showing the query. It can also draft the field definition and mapping notes for a Visual Integrator job for a human to review. It does not create the UDF itself; that remains a controlled Custom Office change.
Frequently asked questions
Why does my new UDF not appear in Crystal Reports?
Crystal caches table definitions. Refresh the data source or re-map the database in the report so it picks up the new UDF_ column. Also confirm the UDF was created on the table the report reads, not on a related header or detail table.
Do UDFs carry over from sales order to invoice?
Not automatically in every case. Check how the specific fields are carried for your version, and test with a copy company. If a value must flow, define the field on both records and verify the copy behaviour.
Can I delete a UDF in Sage 100?
You can remove the definition in Customizer, but that discards the stored data, so back up first and check reports, imports and scripts that reference the UDF_ name. Hiding the field from the panel is the safer first step.
Do UDFs work with ODBC and BOI?
Yes. They appear with the UDF_ prefix in ODBC and are readable and writable through the Business Object Interface by that name, subject to the same security and validation as other fields.
Related
Sage 100 Business Object Interface (BOI): Working VBScript Example
The Sage 100 Business Object Interface exposes the same business objects the screens use through a COM object called ProvideX.Script. You initialise it with the MAS90\Home path, create an SY_Session object, log on, set the company, then create a business object such as AR_Customer_bus and read or write through it. Because it runs the real business logic, it is the supported way to write data in code, unlike direct file or ODBC writes.
Error fixFix Sage 100 SOTAMAS90 ODBC Connection Errors
Most Sage 100 ODBC failures are one of four things: the DSN was created in the wrong 32-bit or 64-bit ODBC administrator for the tool you use, the DSN path or company code is wrong, the Windows account cannot read the MAS90 folder, or the Sage 100 user lacks external access. Recreate the DSN in the matching administrator and test with the driver's own Test button before blaming Excel or Power BI.
How-toHow to Void a Check in Sage 100
Sage 100 voids checks through Accounts Payable > Main > Check and Payment Voiding. Select the bank code, choose the check number, and Sage reverses the Cash Disbursements Journal entry and reopens the invoice it paid. A check that already cleared in Bank Reconciliation or falls in a closed period needs an extra step before it can be voided cleanly.
How-toHow to Run a Physical Inventory Count in Sage X3
Sage X3 physical counts run through Stock > Counts: create a count session scoped to a site, category, or zone, generate the count sheet to freeze theoretical quantities, enter counted quantities, review the variance report, then validate to post stock and valuation adjustments. Blocking movements against the counted locations during the window keeps the variance meaningful.
How-toHow to Create a Generic Inquiry in Acumatica
Generic Inquiries (GI) are Acumatica's no-code query builder for joining tables, adding filters, and exposing the result as an inquiry screen, dashboard data source, or API endpoint. Open System > Customization > Generic Inquiry, add your base table, define joins and conditions, then run and save. No SQL or customization project is required.
Error fixFix Odoo AccessError: You Are Not Allowed to Access Records
Odoo raises AccessError when a user's security groups do not grant a CRUD right on a model through ir.model.access.csv, or when an ir.rule record rule filters the specific record out for that user or company. Fix it by adding the user to the group named in the error, editing the model's access rights, or reviewing the record rule's domain under Settings > Technical > Security.
AI for ERPAI for Sage 100, Grounded in Your MAS90/200 Data
Put AI on Sage 100 and the Sage 100 Manufacturing (MRP) data set without sending data to a public model. Private LLMs and agents grounded in your MAS90/200 tables.
Stuck on Sage 100?
Talk to engineers who work inside Sage 100 every week, and who build private AI that answers these questions from your own ERP data.