Persisting Data in Acumatica Events – Part 3: Validation and Performance

In Parts 1 and 2, we explored the foundations of data persistence and advanced techniques for working with events in Acumatica. In this final part, we'll cover validation strategies, cross-table persistence, transaction handling, and performance optimization techniques for high-volume data operations.

Validation Strategies

Validation is a critical aspect of data persistence in Acumatica. Choosing the right validation strategy determines when and how errors are surfaced to users.

1. Field-Level Validation (FieldVerifying)

The FieldVerifying event is the earliest point for validation and is triggered during the SetValueExt process, before the field is actually changed. If validation fails, you can throw a PXSetPropertyException, which will display an error message and revert the field to its previous value.

protected virtual void _(Events.FieldVerifying<SOOrder.orderNbr> e)
{
    var newValue = e.NewValue as string;
    if (string.IsNullOrEmpty(newValue) || newValue.Length > 10)
    {
        throw new PXSetPropertyException("Order number must be between 1 and 10 characters.");
    }
}

2. Row-Level Validation (RowPersisting)

The RowPersisting event is raised just before a record is saved to the database. This is the ideal place for validation that spans multiple fields, relates to other records in the cache, or involves database lookups.

protected virtual void _(Events.RowPersisting<SOOrder> e)
{
    var order = e.Row;
    
    // Check if the order has any lines
    if (Base.Transactions.Select().Count == 0)
    {
        e.Cancel = true;
        throw new PXException("Cannot save an order with no lines.");
    }
    
    // Validate discount exceeds total
    if (order.DiscountAmt > order.TotalAmount)
    {
        throw new PXException("Discount cannot exceed total amount.");
    }
}

3. Row-Selected Validation (Non-Blocking)

The RowSelected event is raised after a record is selected from the database. While it doesn't directly control persistence, it's ideal for displaying warnings or informational messages that don't block the save operation.

protected virtual void _(Events.RowSelected<SOOrder> e)
{
    var order = e.Row;
    if (order == null) return;
    
    if (order.TotalAmount > 1000000)
    {
        e.Cache.RaiseExceptionHandling(
            order,
            e.Cache.GetField(nameof(SOOrder.totalAmount)),
            new PXSetPropertyException(
                "Order exceeds 1,000,000. Approval required.",
                PXErrorLevel.Warning));
    }
}

4. Attribute-Based Validation

For reusable validation logic, create custom attributes that implement IPXFieldVerifyingSubscriber or IPXRowPersistingSubscriber. This is the most maintainable approach for validation that needs to be applied to multiple DACs or fields.

public class PositiveValueAttribute : PXEventSubscriberAttribute, IPXFieldVerifyingSubscriber
{
    public void FieldVerifying(PXCache sender, PXFieldVerifyingEventArgs e)
    {
        if (e.NewValue is decimal value && value < 0)
        {
            throw new PXSetPropertyException("Value must be positive.");
        }
    }
}

// Usage in DAC
[PositiveValue]
[PXDecimal(2)]
public virtual decimal? UsrMinimumAmount { get; set; }

Cross-Table Persistence

Enterprise applications often involve multiple tables that need to be persisted together. Here's how to handle cross-table persistence effectively:

1. Using Multiple Caches in the Same Graph

When working with multiple caches in the same graph, all changes are persisted in a single transaction when Persist() is called. This ensures data consistency across related tables.

public class SOOrderEntry : PXGraph<SOOrderEntry>
{
    public PXSelect<SOOrder> Orders;
    public PXSelect<SOLine, Where<SOLine.orderNbr, Equal<Current<SOOrder.orderNbr>>>> Transactions;
    
    // All modifications to Orders and Transactions are persisted together
}

2. Creating Related Records in RowInserted

The RowInserted event is the ideal place to create related records that need to be saved in the same transaction as the parent record.

protected virtual void _(Events.RowInserted<SOOrder> e)
{
    var order = e.Row;
    
    // Create default line items based on customer template
    var templateLines = GetDefaultLines(order.CustomerID);
    foreach (var template in templateLines)
    {
        var line = new SOLine
        {
            OrderNbr = order.OrderNbr,
            InventoryID = template.InventoryID,
            Quantity = template.Quantity,
            UnitPrice = template.UnitPrice
        };
        Base.Transactions.Insert(line);
    }
}

3. Using a Separate Graph for Related Records

When the related record has its own complex logic or events that shouldn't interfere with the current graph, create a separate graph instance:

protected virtual void _(Events.RowPersisted<SOOrder> e)
{
    if (e.TranStatus != PXTranStatus.Completed) return;
    
    var order = e.Row;
    
    // Use a separate graph to create audit record
    using var auditGraph = PXGraph.CreateInstance<AuditGraph>();
    var audit = new OrderAudit
    {
        OrderNbr = order.OrderNbr,
        Action = "Created",
        UserID = Base.Accessinfo.UserID,
        Timestamp = DateTime.Now
    };
    auditGraph.AuditRecords.Insert(audit);
    auditGraph.Actions.PressSave();
}

Transaction Handling

Understanding transaction behavior is crucial for maintaining data integrity:

Transaction Flow

• A transaction is opened when Persist() is called
• The transaction remains open during RowPersisting events
• The transaction is committed after all records are processed
• If any exception is thrown, the transaction is rolled back

Handling Rollbacks

[PXOverride]
public void Persist(Action baseMethod)
{
    try
    {
        // Custom pre-persist logic
        ValidateAllData();
        
        // Invoke the base persist
        baseMethod();
    }
    catch (PXException ex)
    {
        // Log the error, but let it bubble up to the UI
        PXTrace.WriteError("Persistence failed: " + ex.Message);
        throw;
    }
    catch (Exception ex)
    {
        // Log unexpected errors
        PXTrace.WriteError("Unexpected error during persist: " + ex.Message);
        throw new PXException("An unexpected error occurred while saving.");
    }
}

Performance Optimization

For high-volume data persistence, apply these optimization techniques:

1. Batch Operations with PXDatabase

// Bulk update using PXDatabase
public void BulkUpdateOrders(List<int> orderIDs, decimal discountPercent)
{
    string sql = @"
        UPDATE SOOrder 
        SET DiscountPercent = @Discount
        WHERE OrderNbr IN ({0})";
    
    var parameters = new List<object>
    {
        new PXDataField<int>("@Discount", discountPercent)
    };
    
    // Build the parameter list for the IN clause
    for (int i = 0; i < orderIDs.Count; i++)
    {
        parameters.Add(new PXDataField<int>("@id" + i, orderIDs[i]));
    }
    
    string inClause = string.Join(",", orderIDs.Select((id, i) => $"@id{i}"));
    PXDatabase.Execute(string.Format(sql, inClause), parameters.ToArray());
}

2. Avoid Unnecessary Cache Operations

// ❌ Inefficient: Full cache refresh
foreach (var line in Base.Transactions.Select())
{
    line.UnitPrice *= 1.1m;
    Base.Transactions.Update(line); // Causes cache operations for each line
}
Base.Transactions.Cache.Persist();

// ✅ Efficient: Direct PXDatabase update
string updateSql = @"
    UPDATE SOLine 
    SET UnitPrice = UnitPrice * 1.1
    WHERE OrderNbr = @OrderNbr";
PXDatabase.Execute(updateSql, 
    new object[] { new PXDataField("@OrderNbr", orderNbr) });
// Then refresh the cache
Base.Transactions.Cache.RaiseRowSelecting(order);

3. Use Lazy Loading and Deferred Operations

// Delaying expensive operations until needed
private Lazy<List<Customer>> _customerCache = new Lazy<List<Customer>>(LoadCustomers);

private List<Customer> LoadCustomers()
{
    // Expensive database query
    return Base.Customers.Select().ToList();
}

Complete Example: Service Layer with Persistence

Here's a comprehensive example combining the techniques covered in this series:

public class OrderProcessingService
{
    private readonly SOOrderEntry _graph;
    private bool _isProcessing = false;

    public OrderProcessingService()
    {
        _graph = PXGraph.CreateInstance<SOOrderEntry>();
    }

    public SOOrder CreateOrder(CreateOrderRequest request)
    {
        if (request == null) throw new ArgumentNullException(nameof(request));
        if (string.IsNullOrEmpty(request.CustomerID)) 
            throw new PXException("Customer is required.");

        try
        {
            // Insert the order
            var order = new SOOrder
            {
                CustomerID = request.CustomerID,
                OrderDate = DateTime.Today,
                Description = request.Description
            };
            order = _graph.Orders.Insert(order);

            // Insert lines
            foreach (var lineRequest in request.Lines)
            {
                var line = new SOLine
                {
                    OrderNbr = order.OrderNbr,
                    InventoryID = lineRequest.InventoryID,
                    Quantity = lineRequest.Quantity,
                    UnitPrice = lineRequest.UnitPrice
                };
                _graph.Transactions.Insert(line);
            }

            // Validate and save
            _graph.Actions.PressSave();
            
            return order;
        }
        catch (Exception ex)
        {
            // Rollback is automatic if an exception is thrown
            throw new PXException($"Failed to create order: {ex.Message}", ex);
        }
    }

    public void UpdateOrderLines(int orderNbr, List<LineUpdateRequest> updates)
    {
        if (_isProcessing) return;
        
        _isProcessing = true;
        try
        {
            // Use PXDatabase for bulk update
            var sql = new StringBuilder("UPDATE SOLine SET UnitPrice = CASE OrderNbr");
            var parameters = new List<object>();
            
            foreach (var update in updates)
            {
                sql.Append($" WHEN {update.OrderNbr} THEN @price_{update.OrderNbr}");
                parameters.Add(new PXDataField<decimal>($"@price_{update.OrderNbr}", update.UnitPrice));
            }
            sql.Append(" END WHERE OrderNbr IN ({0})");
            
            PXDatabase.Execute(string.Format(sql.ToString(), string.Join(",", updates.Select(u => u.OrderNbr))), 
                parameters.ToArray());
            
            // Refresh the cache
            _graph.Transactions.Cache.RaiseRowSelecting(null);
        }
        finally
        {
            _isProcessing = false;
        }
    }
}

Series Recap and Best Practices

The Golden Rules of Data Persistence in Acumatica

• Use the Right Event for the Right Job: FieldVerifying for field-level validation, RowPersisting for final validation, RowPersisted for post-save actions.
• Always Prevent Recursion: Use flags or status checks to avoid infinite loops when updating data within events.
• Prefer Cache Operations for Data with Events: Use Insert/Update/Delete when you need event logic to execute.
• Use PXDatabase for Performance: When event logic isn't needed and you need high performance, use PXDatabase directly.
• Handle Transactions Properly: Let the framework manage transactions, but be aware of where you are in the persistence flow.
• Validate Early and Often: Use field-level validation for immediate feedback and row-level validation for final checks.