Tables and User Objects in SAP Business One: Create Your Own Modules - Featured Image

Publications

User Tables and Objects in SAP Business One: Create Your Own Modules

Hector Pincheira

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

items

When SAP Business One does not have the module your company needs

There is a moment in every SAP Business One implementation when the project team asks an uncomfortable question: «What if the system does not have exactly what we need?» Say you need to record warranties on equipment you sold, control tool loans to customers or keep a log of technical visits. SAP B1 has no native module for that. What it has is something better: the ability to build it yourself. The User-Defined Tables (UDT) and the User-Defined Objects (UDO) in SAP Business One are the strategic answer to that question.

In this tutorial you will learn to create custom data structures inside SAP B1, query them with SQL and connect them to real processes in your company. All without programming, without extra add-ons and without leaving the system.

Why UDT and UDO are a real competitive advantage

Most companies that implement SAP Business One run the system in its standard configuration. As a result, they end up adapting their processes to the limitations of the software instead of adapting the software to their processes. UDT and UDO flip that equation.

A services company, for example, can create a UDT to record every technical visit to a customer: date, technician assigned, equipment serviced, time spent and diagnosis. It can then create a UDO that turns that table into a navigable form inside SAP, with search, filters and export. The process ends up 100% integrated into the ERP, with no parallel Excel sheets and no external systems.

The real power, though, shows up when you combine these structures with SQL queries: you can report on your own custom data exactly as you do on standard SAP data.

Key concepts before you start

Before the tutorial, it is worth clearing up three concepts that get confused constantly. First, a User-Defined Field (UDF) adds a new field to a table that already exists in SAP. Second, a User-Defined Table (UDT) creates a completely new table in the database, identifiable by the prefix @. Finally, a User-Defined Object (UDO) is the visual interface that turns a UDT into a navigable form inside SAP Business One.

The logical sequence is therefore always the same: first you create the UDT, then you add the UDFs that table needs, and finally you create the UDO so users can work with the data.

Step-by-step tutorial: create a UDT for warranty control

We will use a concrete case: a manufacturing company that sells equipment and needs to control active warranties by serial number, customer and expiration date.

Step 1: create the User-Defined Table (UDT)

Navigate to Modules > Tools > Customization Tools > User-Defined Tables. Then click «Add» and fill in the fields. The table name (without the @ prefix) — for example: GARANTIAS. SAP will add the prefix automatically, creating the table @GARANTIAS in the database. The description can be «Equipment Warranty Control».

The table type determines how it behaves. For independent master data choose «No Object»; for detail tables linked to a header, choose the matching type. In our case we will use «No Object», because we will create the UDO manually.

Step 2: add fields (UDF) to the UDT

Once the table is created, you need to add the fields you want. Go to Tools > Customization Tools > User-Defined Fields and select your table @GARANTIAS as the target. Then add the following fields: NumSerie (Alphanumeric, 50 chars), CardCode (Alphanumeric, 15 chars), DocEntry_Factura (Numeric integer), FechaInicio (Date), FechaFin (Date) and Estado (Alphanumeric with a value list: Activa, Vencida, Cancelada).

Step 3: create the User-Defined Object (UDO)

The UDO turns the UDT into a navigable form with every standard SAP control: add, update, delete, search and record navigation buttons. Go to Tools > Customization Tools > User-Defined Objects and create a new object. Define the object code (e.g. «GARANTIA»), the descriptive name, the type (Master Data for individual records), and link the main table @GARANTIAS.

In the «Find» tab, configure the fields users will be able to search records by: serial number and customer code are the most relevant. As a result, users will open the form from the SAP B1 menu and work with warranties exactly as they would with any other native module.

SAP Business One UDO user-defined objects - custom form in the ERP
A UDO turns your custom table into a form with every native SAP B1 control

SQL queries on user-defined tables

Once the structure exists, you can query the data with SQL exactly as you would any standard SAP table. That is how you fold your custom data into reports and formatted searches.

Query 1: active warranties about to expire (next 30 days)

This report is critical for the after-sales service area. It lets you get ahead of expirations and reach the customer before the warranty runs out, opening the door to renewals or maintenance contracts.

SQL Server

-- Active warranties expiring within 30 days (SQL Server)
SELECT
    T0.[Code]           AS Code,
    T0.[U_NumSerie]     AS SerialNumber,
    T0.[U_CardCode]     AS CustomerCode,
    T1.[CardName]       AS CustomerName,
    T0.[U_FechaInicio]  AS WarrantyStart,
    T0.[U_FechaFin]     AS WarrantyEnd,
    DATEDIFF(DAY, GETDATE(), T0.[U_FechaFin]) AS DaysRemaining
FROM [@GARANTIAS] T0
INNER JOIN OCRD T1 ON T0.[U_CardCode] = T1.[CardCode]
WHERE T0.[U_Estado] = 'Activa'
  AND T0.[U_FechaFin] BETWEEN GETDATE() AND DATEADD(DAY, 30, GETDATE())
ORDER BY T0.[U_FechaFin] ASC

SAP HANA

-- Active warranties expiring within 30 days (SAP HANA)
SELECT
    T0."Code"           AS "Code",
    T0."U_NumSerie"     AS "SerialNumber",
    T0."U_CardCode"     AS "CustomerCode",
    T1."CardName"       AS "CustomerName",
    T0."U_FechaInicio"  AS "WarrantyStart",
    T0."U_FechaFin"     AS "WarrantyEnd",
    DAYS_BETWEEN(CURRENT_DATE, T0."U_FechaFin") AS "DaysRemaining"
FROM "@GARANTIAS" T0
INNER JOIN OCRD T1 ON T0."U_CardCode" = T1."CardCode"
WHERE T0."U_Estado" = 'Activa'
  AND T0."U_FechaFin" BETWEEN CURRENT_DATE AND ADD_DAYS(CURRENT_DATE, 30)
ORDER BY T0."U_FechaFin" ASC

The main difference between the two versions is in the date functions: HANA uses DAYS_BETWEEN(), Current date and ADD_DAYS(), while SQL Server uses DATEDIFF(), GETDATE() and DATEADD(). The result, however, is identical: an ordered list of warranties about to expire.

Query 2: warranty history by customer with accumulated value

Because the record now lives in SAP, you can cross warranty data with sales invoices to calculate how much value each customer holds under warranty. That figure is powerful for sales management: customers with high value in active warranties are priority candidates for service contracts.

SQL Server

-- Warranties grouped by customer with associated value (SQL Server)
SELECT
    T1.[CardName]            AS Customer,
    COUNT(T0.[Code])         AS TotalWarranties,
    SUM(CASE WHEN T0.[U_Estado] = 'Activa'  THEN 1 ELSE 0 END) AS Active,
    SUM(CASE WHEN T0.[U_Estado] = 'Vencida' THEN 1 ELSE 0 END) AS Expired,
    SUM(T2.[DocTotal])       AS InvoicedValue
FROM [@GARANTIAS] T0
INNER JOIN OCRD T1 ON T0.[U_CardCode] = T1.[CardCode]
LEFT JOIN OINV T2 ON T0.[U_DocEntry_Factura] = T2.[DocEntry]
GROUP BY T1.[CardCode], T1.[CardName]
ORDER BY TotalWarranties DESC

SAP HANA

-- Warranties grouped by customer with associated value (SAP HANA)
SELECT
    T1."CardName"            AS "Customer",
    COUNT(T0."Code")         AS "TotalWarranties",
    SUM(CASE WHEN T0."U_Estado" = 'Activa'  THEN 1 ELSE 0 END) AS "Active",
    SUM(CASE WHEN T0."U_Estado" = 'Vencida' THEN 1 ELSE 0 END) AS "Expired",
    SUM(T2."DocTotal")       AS "InvoicedValue"
FROM "@GARANTIAS" T0
INNER JOIN OCRD T1 ON T0."U_CardCode" = T1."CardCode"
LEFT JOIN OINV T2 ON T0."U_DocEntry_Factura" = T2."DocEntry"
GROUP BY T1."CardCode", T1."CardName"
ORDER BY "TotalWarranties" DESC

Query 3: equipment with no registered warranty (gap detection)

Warranty control aside, the biggest risk sits in the equipment that was sold but never got a warranty created in the system. This query finds that gap by crossing invoices for «Equipment» type items against the warranty table.

SQL Server

-- Equipment invoices with no registered warranty (SQL Server)
SELECT
    T0.[DocNum]     AS InvoiceNum,
    T0.[DocDate]    AS InvoiceDate,
    T1.[CardName]   AS Customer,
    T2.[ItemCode]   AS Item,
    T2.[Dscription] AS Description,
    T2.[SerialNum]  AS SerialNum
FROM OINV T0
INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
INNER JOIN INV1 T2 ON T0.[DocEntry] = T2.[DocEntry]
WHERE T2.[ItemCode] LIKE 'EQ-%'   -- prefix for equipment-type items
  AND T2.[SerialNum] NOT IN (
      SELECT ISNULL([U_NumSerie], '') FROM [@GARANTIAS]
  )
ORDER BY T0.[DocDate] DESC

SAP HANA

-- Equipment invoices with no registered warranty (SAP HANA)
SELECT
    T0."DocNum"     AS "InvoiceNum",
    T0."DocDate"    AS "InvoiceDate",
    T1."CardName"   AS "Customer",
    T2."ItemCode"   AS "Item",
    T2."Dscription" AS "Description",
    T2."SerialNum"  AS "SerialNum"
FROM OINV T0
INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode"
INNER JOIN INV1 T2 ON T0."DocEntry" = T2."DocEntry"
WHERE T2."ItemCode" LIKE 'EQ-%'
  AND T2."SerialNum" NOT IN (
      SELECT IFNULL("U_NumSerie", '') FROM "@GARANTIAS"
  )
ORDER BY T0."DocDate" DESC

Important note: in HANA you use IFNULL() instead of ISNULL() from SQL Server. Both do the same job: replace null values with an empty string so the comparison does not break.

SAP Business One UDT user-defined tables - custom data analysis
Crossing your UDT data with standard SAP tables delivers high-value reports with no additional systems

Use cases beyond warranties

The UDT + UDO logic carries over to dozens of business processes. In construction companies, for instance, you can create a UDT for subcontract control by project, linked to SAP purchase orders. In companies with a vehicle fleet you can log preventive and corrective maintenance by license plate, crossed with the cost centers of the finance module.

For manufacturing, a UDT for mold or die control is especially useful: service life in cycles, last maintenance, items produced and the associated production order. Retail companies, in turn, can use a UDT to manage stores, tenants and contract expiration dates, firing automatic alerts through linked formatted searches.

The limit is not the system: it is how creative the implementation team is in mapping business processes onto these structures.

Best practices and common mistakes

Across UDT and UDO implementations, the same mistakes keep coming back. The most frequent one is creating the UDT without planning the fields you need: adding fields later is possible, renaming or deleting them is not, at least not without risk. Spend the time designing the structure before you create it.

The costliest mistake, though, is using UDTs for massive transaction volumes without optimization. User-defined tables do not get automatic indexes the way system tables do. So if you are going to store hundreds of thousands of records, you have to create indexes manually from SQL Server or HANA to keep the performance of the whole system from degrading.

Other good practices: document every UDT and UDO in an internal repository (a plain Word document will do), set consistent naming conventions (module prefix + descriptive name), and do not create UDTs for data that already exists in standard SAP tables — that duplicates information and creates inconsistencies.

Official SAP resources

To go deeper into UDT and UDO implementation, SAP keeps updated technical documentation on its official portal. The SAP community is also a valuable resource for specific cases and advanced configurations.

Conclusion: extend SAP B1 without leaving SAP B1

User-Defined Tables and Objects in SAP Business One represent a level of implementation maturity few companies reach. They are within reach of any organization with a consultant or IT manager willing to invest the initial learning time. The return is clear: processes that used to live in Excel or in external systems end up integrated into the ERP, queryable with SQL and manageable with the same SAP user experience.

And every well-designed UDT is a long-term asset: the data you accumulate today is the base of tomorrow's strategic reports. This is not just a technical feature — it is an information architecture decision that shapes the quality of your business decisions.

Does your company already use User-Defined Tables in SAP B1? Or are those processes still running outside the system? If you are part of the Strategic Circle, you have access to ready-to-use UDT templates for the most common processes in industrial companies, plus the SQL queries optimized to work with them. Join now and take your implementation to the next level.

Most Read

QlikSense Hierarchy: parent-child chart of accounts expanded by levels
QlikSense Hierarchy: the Chart of Accounts That Doesn't Add Up
QlikSense Sum TOTAL: share of each family over the total
QlikSense Sum TOTAL: the Percentage That Changes When You Filter
SAP Business One Inventory Revaluation - featured image of the tutorial
SAP Business One Inventory Revaluation: Fix Wrongly Loaded Costs
Empty chamber with wooden benches: the counterweight that required being present to be used
The Veto You Had to Say in Person: Your Decisions With No Counterweight
SAP Business One customizations - featured image of the pre-upgrade audit tutorial
SAP Business One Customizations: Pre-Upgrade Audit
SAP Business One Customer Concentration - featured image of the tutorial
SAP Business One Customer Concentration: Measure Your Risk with SQL

RELATED ARTICLES