Two PXDatabase Use Cases

In most cases, developers in the Acumatica community are discouraged from using PXDatabase directly. The “proper” methodology is to go through graphs and views or utilize PXSelect, SelectFrom, etc. in most cases. Let’s face it. Sometimes, all that overhead just kills performance, and you just need to get to data in a really fast way. Whatever your reason, there are a couple of use cases that sent me into research mode. There are plenty of examples of common uses of PXDatabase, but these two are shared with no warranty, express or implied.

Case 1: Testing for a field that may or may not exist in a table (Generic method)
A developer on the Acumatica Developer discord server asked for a way to check if the field CreatedByID exists in a table. This can be accomplished by using a generic method passing in the DAC for T, i.e. var MyResult = GetCreatedByID<SOOrder>();

public static Guid? GetCreatedByID<T>() where T: IBqlTable
{
    using (PXDataRecord record = 
        PXDatabase.SelectSingle<T>(new PXDataField("createdByID")))
    {
        if (record != null)
            return (Guid?)record.GetGuid(0);
    }
    return null;
}

The code above is for simplicity, but be sure to add PXDataFieldRestrict as needed.

One important note here is that sometimes you can skip the “using” part and just try/catch to retrieve the record. However, in an obscure use use case, I found that the exception created when the table does not contain the field, the exception is passed up the line and caused unexpected results. By using “using” instead of just throwing this into a try block, the error effectively ends up being thrown out. I cannot get into the details of that, but let’s just say I learned it the hard way.

This is a similar example provided by Microsoft Copilot:

using PX.Data;

public class ExampleClass
{
    public void RetrieveSingleRecord()
    {
        using (PXDataRecord record = PXDatabase.SelectSingle<MyTable>(
            new PXDataField<MyTable.field1>(),
            new PXDataField<MyTable.field2>(),
            new PXDataFieldRestrict<MyTable.field3>(PXDbType.NVarChar, 50, "SomeValue")
        ))
        {
            if (record != null)
            {
                var field1Value = record.GetValue<MyTable.field1>();
                var field2Value = record.GetValue<MyTable.field2>();

                PXTrace.WriteInformation($"Field1: {field1Value}, Field2: {field2Value}");
            }
            else
            {
                PXTrace.WriteInformation("No record was found.");
            }
        }
    }
}

Case 2: A record needs to be deleted if “myField1” is 0 and “myField2” is a bool? field that is null. Unlike testing a field for a value as in myField1, checking for a null value requires more explicit code.

PXDatabase.Delete<MyDAC>(
    new PXDataFieldRestrict("myField1", 0),
    new PXDataFieldRestrict("myField2", PXDbType.Bit, 1, null, PXComp.ISNULL)

Credit to jjacob on StackOverflow for this solution which I found to be very elusive in documentation.

While there are many other use cases, let me reiterate my previous warning: These examples are given without warranty – do your research and use caution. In the right conditions, PXDatabase is your friend, but it should be the exception and not the rule.

Happy coding!