Welcome to the topic on User-Defined Fields and User-Defined

Welcome to the topic on User-Defined Fields and User-Defined Tables.

On completion of this topic, you will be able to add your own fields and tables to

objects in SAP Business One. You will also learn to add user-defined values to

these user defined fields.

Additional fields are often added to master data and documents to implement a

customer’s business process. Here are two business requirements that can be

implemented with user-defined fields.

• There is a need to track a status level for each customer – gold, silver, and

bronze. You can add a user-defined field to the customer master data to hold

and track the status value.

• Additionally, the salesperson needs to record the customer’s preferred delivery

time when processing a sales order. You can add a user-defined field to the

sales order document, and record the delivery time in this field.

In the first part of this topic, we will cover user-defined fields.

You can add new fields to most business objects, including business partner and

item master data, and marketing documents.

Only authorized users can add user-defined fields. The general authorization is

located in the authorizations window by navigating to Customization Tools > User-

Defined Fields – Management.

User-defined fields are frequently added to marketing documents. You can add

fields to both the header (title) and rows of marketing documents.

When you add a user-defined field to the marketing documents object, the new

field is added to all logistics document types, including all sales and purchasing

document types, and some inventory documents such as Goods Receipt and

Goods Issue.

When you add user-defined fields (UDFs) at the header level, the new fields

appear in a separate window positioned by default to the right of the existing

document window.

This window is not visible immediately. To open this window, choose View > User-

Defined Fields from the top menu bar, or use the key sequence Ctrl+Shift+U. You

can reposition this window to the left or bottom of the existing window.

If the fields are not required in a specific document type, you can keep the new

window invisible.

When you add a field at the row level, the system adds this field as an additional

column in the row. The field is visible and active by default.

You can make the user-defined field invisible or inactive by using the standard

form settings for the document row.

User-defined fields are added to the respective database table for the object. They

can be quickly identified in the table, since they have the prefix “U_”.

User-defined fields are retained during an upgrade to a new release. When you

create a new company, you have the option to copy user-defined fields from the

current company to the new company.

An important point about user-defined fields is that when you add a new field to an

object, the new field is added to all instances of the object, including records

already saved in the database.

UDFs function as normal fields and can therefore be used in queries and reports.

You can also import data into user-defined fields using the Data Transfer

Workbench. In the DTW template, simply add these fields at the end of the

spreadsheet. Enter the name of the field in the header row and enter the value as

you would for a standard field.

It is important to remember that, when you add a user-defined field, the database

structure is updated. Therefore you should only add new fields when no other

users are logged in.

If you try to add a new field, the system will warn you if there are logged in users.

You have the option to:

• Try Again – you can notify the users close their work and wait until they have

logged out of the system

• Ignore - the system will force close all open documents for all connected users

This demo will show how to add user-defined fields to the header and row of a

marketing document.

You can select a Type and optionally a Structure for each user-defined field. The

Structure is dependent on the Type and influences the format of the field.

Be aware that you cannot change the type and structure after you have added the

field. If you mistakenly choose the wrong type or structure, you can remove the

field, and start again.

Fields with Alphanumeric type can have one of the structures shown here. A

Regular structure can hold up to a maximum of 254 characters. A Text structure

can accommodate 2 GB of text in header fields and 255 KB of text in row fields.

Fields with Numeric type can only hold integers, therefore there is no structure.

Fields selected with the Date/Time type can have Date or Hour as the structure.

These fields behave in exactly the same way as other date and time fields in the

system; for example, the calendar icon is available in a field with the date

structure.

To enter fractions as well as integer numbers in the new field, use the Units and

Totals type and select one of the structures shown here.

Decimal places will display in the new field according to the initialization settings

on the Display tab of the General Settings.

If you select the General type, the new field can hold links or graphics.

• Fields with the Link structure can link to a file or to a web address. You must

first define the default path to the Attachments folder. This is done in the

General Settings. This default path is opened when the user double-clicks the

new field. The user can either select a file from the default folder, or enter a

web address in place of the file name.

• Fields with the Image structure can accommodate pictures. You must first

define the default path to the Pictures folder, in the General Settings. If this

default path is not defined, an error will occur when the image field is used.

When the user double-clicks the image field, the default folder opens, enabling

them to select an image file. The user can then change to a different folder to

locate the image.

The user can, at any time, change an image or web address by pressing the Ctrl

key and double-clicking the image or web address.

You have the option of defining a list of values for the user-defined field.

In the example, you can see that three values have been supplied for the field –

gold, silver, and bronze.

This list is available in the document as a dropdown list, allowing the user to select

one of the values.

The user cannot update the values in the list. The list values can only be updated

by the authorized user from the User-Defined Fields – Management window.

Note that you can also add a query as user-defined values to a user-defined

field. The query can populate the field based on the result of the query. This

functionality is covered in the next topic.

You can optionally set a default value for any user-defined field.

In the example, we have selected Bronze (value 3) as the default from the list of

valid values.

The default value set for a field will appear in all new instances of the object.

You can make the user-defined field mandatory. The user will not be able to add a

new record without entering data into the mandatory field.

When you set a user-defined field as mandatory, you need to initially supply a

default value, to maintain the integrity of the database. However, if you are

working with release 8.82 and later, you can afterwards update the user-defined

field and remove the default value.

If you set a user-defined field mandatory, and the field already has a default value,

you can choose whether to insert the default value into all existing instances of the

object, or whether to only insert the default value into new instances.

This demo will show how to add user-defined fields with a list of values.

When using the Copy To/ Copy From function to generate a new document from a

base document, the value of a user-defined field is transferred to the target

document. This also applies to documents generated through the Document

Generation Wizard.

If multiple base documents are copied to a target document, and the user-defined

field has different values in these base documents, the field value is not copied.

Sometimes user-defined fields may not be relevant for every document type; for

example, a field in a sales order may not be relevant in a purchase order.

Users can manage the display of user-defined fields when they process

documents using the settings window. The settings window is different to the

standard form settings window, but works in a similar way. To open the settings

window for user-defined fields, press Ctrl+Shift+B, or choose Tools >

Customization Tools > Settings.

Using the settings window, users can:

• Define new categories to group related fields and display them in a document

type by selecting the category. The user can choose to display fields for a

single category or can choose to display all categories.

• Make fields invisible or inactive for a document type. For example, you can

make all the fields relevant for a sales order invisible in a purchase order

document type.

• Change the display order of the fields by setting the numerical order. In this

way, the most frequently used fields can be sorted to the top, for ease of use.

This demo will show you how to manage user-defined fields using the settings

window.

In the last part of this topic, we will cover user-defined tables.

In addition to user-defined fields, you can add new tables to the database. User-

defined tables (UDTs) give you the ability to store additional, related sets of data.

The user-defined table becomes part of the company database. The system

identifies user-defined tables by the ‘@’ prefix so you can easily distinguish them

from system tables. In our example, the table is called @TRUCKS.

Information about each new table is stored in the OUTB system table.

When you create a new company, you have the option to copy user-defined tables

from the currently selected company to the new company.

To set up a user table, you enter a name and description. Here we have created a

new table to hold information about a set of delivery vehicles that the company

owns. We want to record details such as the vehicle registration number, model,

and capacity. The object type is not relevant for user tables; it is only relevant

when creating user-defined objects using the Software Development Kit. You

should leave the selection as No Object, since you cannot change the object type

after you have added the user table.

You access a new user-defined table by selecting the table from the Tools > User-

Defined Windows menu bar.

• The table is initially created with two columns, code and name.

• You can enter data in these two columns. These fields are used as the primary

key, so must be unique for each row that you add to the table.

At this point, the user-defined table is not attached to any form or document;

however, you can use the table in queries, and you can import data into the table

using the Data Transfer Workbench.

To make a user table directly visible to users in a form or document, you can link

the table to a user-defined field in the document or form.

Note that user tables can only be linked to UDFs with the Alphanumeric type and

Regular structure.

Choose Tools > Customization Tools > User-Defined Fields – Management and

choose the Set Linked Table checkbox for the field. Then select the user-defined

table.

You can link the same user-defined table to multiple user-defined fields in different

objects, at both the header and the row level. For example, you could link the

trucks table to a user-defined field in the pick list document in addition to the

delivery document.

The table displays as a list of values in the form or document, where only the first

two columns are visible.

The user has the ability to dynamically add new rows to the table using the “Define

New” option. The user can view and populate all columns in the table.

You can add columns to a user-defined table from the User-Defined Fields –

Management window.

The extra columns are first added as a user-defined field. Select the table under

the User Tables object and choose Add.

In the example, we have added three additonal columns – Capacity, Make, and

Model, to the Delivery Vehicles table.

You can now enter data for the new columns. Choose Tools > User-Defined

Windows and select the table. Alternately you can open the table from the linked

UDF in a document or form.

If the new columns are not visible in the window, you may need to adjust the width

of the columns to bring the new fields into view.

The data in the table can be used to manually assign a delivery truck in a

document, or a query can be written to assign a truck with the capacity to hold the

delivery items.

The default key for a user-defined table is the combination of the first two columns

– column and name. In addition, you can select any field or combination of fields

from the user table as a new key. This option allows the system to conduct a

faster search using this field in queries.

You can optionally make the key unique. This enforces the unique constraint and

prevents a new row being added with a field that has a duplicate value.

This demo will show how to set up a user-defined table.

Here are some points to take away regarding user-defined fields and tables:

• You can add user-defined fields to most objects, at either the header level or

the row level.

• User-defined fields are added to the table for the selected object, and have

prefix “U_”.

• User-defined fields added at the header level will show in a separate window to

the side of the document. You can use the View menu to open this window.

• You can manage multiple user-defined fields at the header level using the

settings window. This allows you to assign categories, set fields as invisible,

and reorder the sequence.

• User-defined fields can have various types and structures, such as

alphanumeric and regular. The choice of structure affects the maximum field

length and the type of data you can enter in the field.

• You can optionally add a list of valid values to a UDF, or set a default value, or

make the UDF mandatory

• You can create user-defined tables to the database. These tables can hold

additional, related information. User-defined tables can be identified with the

prefix “@”.

• To make a user table available in a document or form, link the table to a UDF in

the document or form.

You have completed the topic for user-defined fields and user-defined tables.

Thank you for your time!