In Acumatica, it is common to encounter scenarios where the format of data stored in the database differs from how it is displayed to end-users. One such case involves financial periods stored in the yyyymm format but requiring display in the mm-yyyy format for better readability and usability in the UI.

This article provides an in-depth analysis of the reasons why discrepancies between stored and displayed data formats arise, the potential challenges these differences can introduce, and detailed solutions to manage and resolve them effectively. We will also compare two approaches to address this issue.

Why Save and Display in Different Formats?

1. Storage Optimization: The yyyymm format is compact and efficient for database storage and operations. It simplifies sorting and querying financial periods.
2. User Readability: End-users typically prefer a more familiar and readable format like mm-yyyy when interacting with the UI.
3. Data Integrity: Storing the raw data in a single, consistent format ensures accuracy and avoids potential errors introduced by formatting during data operations.
4. Localization: Some formats might be specific to business or regional requirements, necessitating a flexible display without altering the stored format. This flexibility allows developers to present data in a user-friendly way without modifying the underlying stored format, ensuring data consistency and compatibility.

The problem is that if the field stores data as yyyymm, directly binding it to a UI control may result in an unfamiliar and less user-friendly display. For example:

• Stored Value: 202501
• Desired Display: 01-2025

Without intervention, the raw yyyymm format appears in grids, reports, and forms, leading to user confusion or inefficiency.

Solution 1: Using an Unbound Field for Display

This approach involves creating an additional, calculated field in the DAC to format the stored value for display purposes.

Code Example:

How It Works:

• FinPeriodID is the stored field.
• FinPeriodFormatted is an unbound field that formats FinPeriodID using a get method.

Solution 2: Using a PXDBCalced Attribute

This approach leverages the PXDBCalced attribute on the FinPeriodFormatted field to define a calculated field at the database level. The formatted value is computed dynamically when queried from the database.

Code Example:

How It Works:

• FinPeriodID is the stored field.
• PXDBCalced generates the formatted value dynamically using BQL expressions.

In summary, unbound fields offer more flexibility in data conversion, especially when dealing with complex transformations, but the conversion happens in the application layer. On the other hand, PXDBCalced fields handle data conversion in the database, making them more efficient for large datasets or scenarios where the formatted value is required for queries or reports.

When to Use Each Solution

• Unbound Field for Display: When flexibility and simplicity are priorities, especially if the formatted value is only needed for display purposes.
• PXDBCalced Field: When performance is critical, and the formatted value needs to be part of queries, reports, or other database-level operations.

Separating the storage and display formats of data enhances both performance and user experience. By choosing the appropriate solution—an unbound field or a calculated database field—developers can meet specific requirements while maintaining data integrity and usability.