Customizing Field Values with a Mathematical Formula

Objective

After completing this lesson, you will be able to use a mathematical formula to customize field values.

How to Define a Mathematical Formula on a Header Field

Mathematical formulas allow you to customize field values, including date and time. A formula can include:

  • Free text you enter
  • Mathematical expressions
  • Fields from the current document, or other business documents or objects in the database

This lesson covers a few examples of how to define a mathematical formula. The following video shows you how to define a mathematical formula on a header field. Note that you can apply this concept to any field that allows editing and testing, such as date, alphanumeric, or numeric fields for calculations.

Note

Mathematical formulas support:

  • Mathematical operators and brackets: (, +, -, *, /, )
  • Additions of strings
  • System fields and UDF, in header and table
  • API for Date and Time - for example, you can define a UDF that measures the KPI of how many days before Christmas you delivered an order. The formula will be: DATE(2024, 12, 24) - DeliveryDate

    The API DATE (YEAR, MONTH, DAY) creates a date based on the three parameters. If the delivery date was on 20/12/2024, the result of the formula will be 4.

    The same concept is relevant for the TIME API: TIME(hour, minute, second)

Refer to the guidelines for Effective Formulas on the SAP Help Portal.

Note - A formula can also be defined as manual refresh. You need to manually refresh the value in the field.

Refresh button next to the Customer Ref. No field in the Customer Details section.

How to Define a Mathematical Formula on a Table Field

In this video, you’ll learn how to define a mathematical formula applied to a table field.

How to Define a Mathematical Formula Involving Data from the Database

In the following video, you’ll learn how to write a mathematical formula that involves fields from the database.

For example, let’s say that each time you select a customer in a sales order, you want the customer’s balance to display in the Remarks section as "Customer Balance is" then the balance.

If you are a technical user and are familiar with the Desktop client, you probably know that you’d need to write a query to retrieve data from the database. However, in SAP Business One, Web client, you don’t have to write a query. You can use the Lookup mechanism to instruct the system how to retrieve a record from the database.

The following image shows you an example query, and how the lookup mechanism in SAP Business One, Web client compares

An SAP Business One, Desktop client query compared to the SAP Business One, Web client UI.
  • The first part of the query SELECT T0.[Balance] goes to the formula, which is the db.BusinessPartners of the CurrentAccountBalance.
  • The second part of the query is the Lookup, which is made up of three different parts:
    • Lookup Object: The object you want to search for.
    • Lookup Field: The field you want to search upon.
    • Compare With and Value / Field Name: The value of the field that you want to search, which can be taken from the UI or can be a constant value.

Note

For the lookup mechanism, you can have more than one lookup field for the same object – the system will perform the "AND" operator on these fields. You can also have more than one object for lookup (for example, Items and Business Partners Master Data).

You can refer to the DB Table Reference and BO Reference to see the names of tables, objects and fields.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn