Customizing User-Defined Fields, Tables, Objects, and Values

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 instructions when processing a sales order. You can add a user-defined field to the sales order document, and record the delivery instructions in this field.

Let's cover how to add user-defined fields.

• You can add new fields to most business objects, including business partner and item master data, and marketing documents. These fields are stored in the database and table of the business object.
• Only authorized users can add user-defined fields. The general authorization is Customization Tools > User-Defined Fields - Management.

• You can add fields to both the header area and to the 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.
• UDF's added to the header are editable after the document has been added.
• If the user-defined fields are not used in a marketing document, the new window can remain closed.

• Authorized users can use the Tools > Edit Form UI function to drag relevant user-defined fields from the side window to the main form area, thus dispensing with the side window.
• Authorized users can make this change just for their own use or for other users by creating a UI Configuration template. In the UI configuration template, select the form that has the UDFs added, then drag the fields into position. The template can then be assigned to users. For more information on UI configuration templates, see the UI Configuration Templates course in the System Setup and Administration unit.

• If you decide to keep user-defined fields in the side window, you can manage them 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. The menu only shows active when you select a form with user-defined fields.
• The user can make the user-defined fields visible or invisible, and active or inactive, in the user-defined fields side window. For example, you can make all the fields relevant for a sales order invisible in a purchase order.
• There is also the option to define new categories to group related fields and only display the fields for a category.

• 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.

• 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 change these settings or move the field to a new position on the row using standard form settings.

• Row level UDF's are behaving the same as system columns. For example if a sales order is open, and rows can be updated, so are the UDF's. Once the order is closed, the UDF's are also blocked.
• You can allow editing row level UDF's from the Administration→ System Initialization → Document Settings → Per Document.
• In addition, you can use the general authorization to determine which user can edit row level UDF's on the sales and purchasing modules.

• 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_". In the slide example you can see two new fields added to the order row table RDR1.
• 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

• When you create a user-defined field 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 to the database. 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.

In addition to the type and structure for a user-defined field, you have the option of specifying validation options.

• The option Linked to Entities allows you to set up a direct link between a user-defined field in a form and a system object, a user-defined table, or a user-defined object. In the example we have linked a user-defined field in the service contract form with the A/R invoice object.
• The link is actually established by the user who creates the form. In the user-defined field the user will be shown all the instances of the chosen object and can select one to create the direct link. For example, when creating a service contract for customer C40000 the user can select the invoice paid by this customer for the service item. After the link is established the user can open the A/R invoice directly from the user-defined field.
• Be aware that the user must select the correct instance, since the system shows all rows stored in the database for the chosen object.

• If valid values is chosen, you can define a list of possible values for the user-defined field. This definition is made when you add the user-defined field.
• In the example, you can see that there are three possible values for the field - silver, gold and platinum.
• 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.

• If the Advanced option is selected the system lets you set some simple validation rules for when the user enters data in the user-defined field.
• The validation rules apply to all UDF types with the exception of General type.
• The validation controls vary according to the type and structure of the user-defined fields. For example, for alphanumeric type the rules can check which characters the user can enter in the field, and how many characters. For units and totals the rules can check that the amount entered falls in a certain range.
• Other rules allow you to validate that the value entered is equal to, less than, or greater than a value entered when you add the user-defined field.

• A flexible way of using a user-defined field is to link a query to the field using user-defined values.
• The query can automatically populate the field value based on the result of the query.
• In the example shown, a query is added to a user-defined field in the header of a sales order. The query fetches the customer's account balance from the business partner master data table OCRD. The query runs automatically when the user selects the customer code when they first create the sales order.
• User-defined values functionality is covered in the companion course User-Defined Values.

• You can optionally set a default value for any user-defined field.
• In the example, we have selected a default time from the list of valid values.
• The default value set for a field will appear in all new instances of the object.
• You can optionally set the user-defined field as 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. After you have added the user-defined field you can remove the default value.
• If you update a user-defined field to make it mandatory, and the field already has a default value, the system will prompt you to choose if the default value should be inserted into all existing instances of the object, or whether to only insert the default value into new instances.

• You can add user-defined fields to most objects.
• User-defined fields are added to the table for the selected object in the database, and have prefix "U_".
• For marketing documents, you can add fields to the header (title) area or to the rows.
• User-defined fields added at the header level of a marketing document initially appear in a separate window to the side. You can use the View menu to open this window.
• You can move user-defined fields from the side window to the main area of the document using the Edit Form UI function. You can then make the edited document available to users in a UI Configuration template.
• A value entered into a user-defined fields is transferred to a target document using the Copy to/Copy from function.
• 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, link it to a system object, define rules for validating data entry in the field, set a default value, or make it mandatory. You can also link the user-defined field to a user-defined table or user-defined object.

Here are two examples of requirements that can be met by adding user-defined values to form fields:

• In the first example, the company wants to have the delivery date, which is a mandatory field, automatically calculated in sales orders, so that the salesperson does not have to manually enter the date. In the business process the delivery date is not used, since sales orders are dispatched immediately for delivery.
• In the second example, the salesperson needs the ability to instantly view a customer's up-to-date account balance when processing a new sales order over the phone. With user-defined values, the salesperson does not have to manually drill-down to the master data.

• You can add user-defined values to any editable header field in a document or form, and to row level fields. You can also add UDVs to user-defined fields.
• If added at the row level, user-defined values apply to each row.
• User-defined values can be added to a field as:
  • List of values. The user chooses from a list of predefined values for the field.
  • User query. When the query runs, the query results are stored in the field. In the example, a query has been added to the delivery date field. The query will calculate a valid date for the delivery. Using a query is the most flexible option as the query can be set up to run manually or automatically.

• A magnifying glass icon indicates that user-defined values have been added to the field.
• Users can toggle the display of this icon on and off, using the menu View > Pickers Display > User-Defined Values.
• In the example, we can see that user-defined values have been added to the Delivery Date field, and to a user-defined field called Source.

• To add user-defined values to a field, select the field in the document or form and press the key combination Alt+Shift+F2, or choose the menu path Tools > Customization Tools > User-Defined Values - Setup.
• The setup screen for user-defined values will open.
• Only authorized users can add UDVs. The authorization is located in the General Authorizations window by navigating to Customization Tools > User-Defined Values - Setup.

• In the setup window for user-defined values, use the option Without Search in User-Defined Values to remove user-defined values from the field.
• There are no restrictions on removing user-defined values from a field. After you remove the user-defined values, documents saved in the system retain the values entered in the fields.

You can add a list of values to a field using user-defined values.

• To add a list of values to a field, choose the option Search in existing user-defined values. Select the browse icon in the setup screen, and enter the values for the list.
• When defining the values, make sure that the value does not exceed the field length as defined in the database. In this example, a list of values has been added to a user-defined field called Source. If the length of the field Source is 15 characters, and you enter a value with16 characters, the last character will be truncated.

• To open the list of values when processing a form, the user can select the magnifying glass indicator or press Shift+F2 in the field. The user then selects a value from the list. Furthermore, the user can add new values to the list by choosing the New button.
• This example shows a list of values added to a user-defined field. Note that a list of values can also be defined when you initially create a user-defined field using the User-Defined Fields - Management window. The difference is that the user cannot add new values to the list. The list can only be maintained from the User-Defined Fields - Management window.

• Note that, when you add user-defined values to a specific marketing document type, such as a sales order, other similar document types such as deliveries and invoices are unaffected. In other words, user-defined values behave differently to user-defined fields and are not inherited by other document types. Therefore you need to add user-defined values to each specific document type as required.
• However, the value entered in a field with user-defined values is transferred to a target document using the Copy To/ Copy From function.

You can attach a query to a field using user-defined values.

• The option Search in existing user-defined values according to saved query lets you attach a query to the field.
• This option is very powerful, since the field is populated with the results of the query.
• The query can set a value in the field, or store the results of a calculation in the field, or fetch another field value from the database to go in the field.
• The query can also use a variable to prompt the user for a parameter, and use the parameter to calculate the results.

• When you use a query, make sure the query result matches the field type in the document, and does not exceed the size of the field. For example, do not return an alphanumeric string result into a field that is defined as numeric.
• In the example shown here, a query is used to set the delivery date in a sales order. The query adds 7 days to the current date (DocDate) and stores the result as the delivery date. The query refers to the DocDate field in the current active document therefore it uses the $ and square brackets.
• The query is written using SAP HANA syntax and requires the clause "FROM DUMMY" since there is no FROM clause in the query.
• For more information on creating queries using the query tools see the course Queries.

• When you select the option to use a query as user-defined values, you can decide if you want the query to automatically run without user involvement.
• The Auto Refresh When Field Changes checkbox determines if the query runs automatically.
• If you do not select this checkbox, the query is not automatic and only runs when the user clicks the magnifying glass or presses Shift+F2 in the field.

• If you select the Auto Refresh When Field Changes checkbox, you are prompted to select a dependent fields. You can select up to 5 fields to trigger the query to run. The query runs automatically when the dependent field changes in value or the user enters a value in the dependent field.
• In marketing documents, the customer or vendor code is often selected as the dependent field.

• When you choose Auto Refresh When Field Changes, two further options appear. These options determine if the query runs only once, or if the query runs whenever the dependent fields change.
• The default option is Display Saved User-Defined Values and is recommended since it maintains the initial result of the query. The query runs once when the dependent field changes, and will not run again even if the dependent field changes. An example is a query added to the Delivery Date in a document. The query calculates a delivery date based on the system date. You only want the query to run once. If the query ran multiple times, the delivery date might be incorrectly updated.
• If you choose Refresh Regularly, the query will run each time the dependent field changes or is selected in a document. You should use this option with caution since it can result in the field value changing when not expected. When the query runs, the document status will change to Update mode, and you must save the changes if you want to record the new query results. An example of using Refresh Regularly is a query that shows the up-to-date account balance of a business partner. You want the query to run whenever you browse or open the document so you can always see the latest balance. However, you will still be prompted to update the document if you want to save the query results.

Here is an example to illustrate how the auto refresh function works.

• A query is added to a user-defined field in the sales order. The query access the customer's account balance from the business partner master data.
• The Customer/Vendor Code is selected as the dependent field. When the user selects the business partner code in a new sales order, this action triggers the query to run.
• This is typically the first action when a user processes a new marketing document. Therefore it is a common practice to use the Customer/Vendor Code as a dependent field to trigger the query to run.

• For a query added to a header field in a document, you can only select another header field as the dependent field. This includes all fields in the document that are not in the row table.
• For a query added to a row-level field, you can select either a header or a row-level field as the dependent field. When you add user-defined values to a row field and choose Auto Refresh, you have two choices:
  • Select When Field Changes if you want to use a header field as the dependent field for a row-level query. Choose When Exiting Altered Column or When Column Value Changes if you want to use another row field as the dependent field. 
  • If you need the query to run when item is selected, always use one of the dependent field to be item No. Or Description
• In the example a query is added to a row field called Commission. The query calculates commission amount by multiplying quantity, price and commission percent. The query will run when the item number is first selected, and if quantity or price are updated by the user.

• For a query added to a row-level field, dependent on other row fields, select between the two options:
• • Choose When Exiting Altered Column to trigger the UDV to run based on editable fields
  • Choose When Column Value Changes to trigger the UDV to run based on all available fields in the row, including non-editable fields such as tax amount, gross profit amount and more. 

• User-defined values (UDVs) can assist users with data entry by using a list of values or a query to populate a field value.
• You can add UDVs to editable header and row level fields, and to any user-defined field. To add UDVs, select the field, then press Alt+Shift+F2.
• A magnifying glass indicator shows if user-defined values exist for a field. You can enable this from the View > Pickers Display menu.
• User-defined values can be set up as a list of values, or, more commonly, as a user query.
• A user can opens the list of values or run a query manually by selecting the magnifying glass indicator or pressing Shift+F2 in the field.
• If a query is used, the results of the query are saved in the target field. To set the query to run automatically with no user involvement, use the auto refresh option and select a dependent field. Two further options are available for running the query:
  • If you select Refresh Regularly, the query will run each time the dependent field changes or is selected, or when browsing records. This could lead to inconsistent results, depending on the query, or could cause a performance degradation when browsing through records.
  • If you select Display Saved User-Defined Values, the query will run once and retain the result in the field. This option is recommended since it maintains the initial value of the field, but the choice of this option depends on the business need and the query.
• Queries should be written to reference the active window, where needed.

• A company wants the ability to assign a delivery vehicle to delivery documents.
• The delivery fleet can be maintained as a user-defined table and made available to the delivery document.
• Users can manage the fleet entries in the table.

• 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.
• When you create a new company, you have the option to copy user-defined tables from the currently selected company to the new company.

• User-defined tables can be linked to a user-defined field (UDF) in a document or form. To do this you must choose the correct object type when you create the table.
• To link to a UDF choose No Object or No Object with Auto Increment.
• To link to a user-defined object (UDO) choose one of the other options:
  • Choose Master Data if the object will hold master data. You can also create child tables by selecting Master Data Rows as the object type.
  • Choose Document if the object will hold transactional data. You can also create child tables by selecting Document Rows as the object type.
• Note that you cannot change the Object Type after you have added the user table to the system.

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

To link table to user-defined field (UDF) in a document or form, you create the table with the object type No Object.

• You access a newly created user-defined table by selecting the table from the Tools menu.
• The table initially contains two columns, code and name.

• In our scenario, we want to use the table in a user-defined field in a delivery document. Therefore when we set up the table we could choose either No Object or No Object with Auto Increment as the object type.
  • If you choose No Object as the object type when you create the table, the Code column is editable and you can enter a unique value for each row.
  • If you choose No Object with Auto Increment as the object type, the Code column is not editable and the value will be auto incremented for each row of data you enter. We chose this option so the primary key is updated for us.
• The Code is the primary key and the Name must be unique for each row that you add to the table. In addition, you have the option to select any field or combination of fields from the user table as a new key. This option can speed up searches.

• To be useful, a user-defined table needs additional columns. Each column is added as a user-defined field.
• Select the name of the table in the user-defined fields management window. Then add a user-defined field for each column.
• In the example, we have added four additional columns - Name, Shift, Available, and Location to the Delivery Drivers table. In these fields we will record the name of the driver, the hours they work, if they are available for assignment, and their location. With this information, a user will be able to select the best driver for the delivery.
• For information about user-defined fields, see the related course User-Defined Fields.

After the additional columns have been added to the table, you can enter data or add new rows by choosing then menu Tools > User-Defined Windows. Choose and select the table. 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.

At this point the user-defined table is not attached to any form or document so is not accessible to the user; 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 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.
• In the example, a user-defined field called Drivers is added to the title area of the marketing documents object. When you add the user-defined field choose the Set Linked Table validation option. Then select the user-defined table from the dropdown list.
• 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.

• When you select the user-defined field, you can see the first two columns. Choose Define New to view and access all columns in the table.
• The user can edit and add rows to the table.

In this example, the customer has requested an evening delivery, therefore a driver is selected who is available in for an evening delivery. The Name from the user-defined table is now stored in the user-defined field in the document.

To link a user table as a user-defined object (UDO) you create the table with the object type Master Data or Document

• To link a user table as a user-defined object (UDO) make sure you created the table with the object type Master Data or Document.
• A user-defined object consists of a user-defined table and added user-defined fields. These fields will hold the data for the new business object.
• You can add user-defined fields using the SAP Business One application or via the DI API. In this example we have created a user table called Company Cars, as a master data object.
• We have added 4 user-defined fields.

• Once you have created the user table with the user-defined fields you need to register it as an object in SAP Business One using the Objects Registration Wizard. The registration is per company.
• Enter a unique ID and select the user-defined table name.
• As you progress through the wizard you will be prompted to select the services for the object, for example, Add, Update, Find, Delete. Add and Update are basic services and cannot be deselected.
• You can optionally select a child table, that will appear as rows in the master data or document object. Child tables are created by selecting the object type as Document Rows or Master Data Rows.

You can add the object to the main menu by selecting the Menu Item checkbox in the wizard. You then select the module from the selection form, and the position within the module menu.

You can select either Header Line Style or Matrix Style in the wizard. This affects how the table displays when the user access it. Examples are shown here.

• You can add your own user-defined tables to the database. These tables can hold additional, related sets of information.
• User-defined tables can be identified with the prefix "@".
• To be able to link a user-defined table to a user-defined field in a document or form, select the Object Type as No Object or No Object with Auto. Increment.
• The table is initially created with two columns, code and name. User-defined fields can be added as additional columns in the table.
• To make a UDT accessible to a user in a document or form, create a user-defined field in the document or form, then choose the Set Linked Table option to link the table to the UDF. Only UDTs with Object Type No Object or No Object with Auto. Increment can be linked to a UDF. End users can access the table through the UDF, and have the ability to add rows to the table, and enter data in the user-defined field columns.
• To use a table as a user-defined object (UDO), select the Object Type as Master Data or Document.
• Add user-defined fields to the table as columns.
• Run the objects registration wizard to create the object, select the services for the object, select any child tables for rows in the object, and add the object to the main menu. Users can access and maintain the object from the menu, and browse through records.