Skip to main content

Use an Acumatica GPT (ChatGPT) to write boiler plate code for DACs and BQL or FBQL queries

  • 24 July 2024
  • 1 reply
  • 219 views

MichaelShirk
Captain II
Forum|alt.badge.img+1

If you’re like me, you don’t enjoy typing out long BQL queries, or writing all the boiler plate code for a new DAC, with all its properties, abstract classes, and attributes.

Here’s a little tip I’ve discovered to save me a lot of time there.

This is one of the very positive uses for ChatGPT, and writing boiler plate code is something it is quite good at, if you use a GPT that has been trained specifically for Acumatica. 
This is NOT a sales pitch, but on the GPT store, you can get the Acumatica Developer Assistant GPT (by Evan Trowbridge). The usage is throttled, but you can still use it a few times per hour for free. It is substantially more accurate than regular ChatGPT.
 


A simple prompt with a few instructions can get you most of the code for a DAC in a few seconds. You’ll still need to make some adjustments, but this will get you 95% of the way there.

Here’s a small example. I will probably refine my prompt so I can add more information to it and have the resulting code more complete, but it’s really nice not having to type it out by hand. It’s similar to using the “Generate from Database Members” functionality in the customization project editor, but much more flexible.

Prompt: 

“Write the code for a projection DAC using fluent BQL to retrieve the following fields.
The main DAC should be SOOrder.
SOOrder.OrderDate
SOOrder.OrderType
SOOrder.OrderNbr
SOOrder.OrderDesc
SOOrder.CustomerID
SOLine.InventoryID
SOLine.SiteID
SOLine.UOM
SOLine.OrderQty
SOLine.CuryUnitPrice
SOLineSplit.LotSerialNbr”

 

And a few seconds later, most of the work was done for you!
 

using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.Objects.AR;
using PX.Objects.IN;
using PX.Objects.SO;

namespace AcumaticaProjectionExample
{
    [PXProjection(typeof(SelectFrom<SOOrder>
        .InnerJoin<SOLine>.On<SOLine.orderType.IsEqual<SOOrder.orderType>
            .And<SOLine.orderNbr.IsEqual<SOOrder.orderNbr>>>
        .LeftJoin<SOLineSplit>.On<SOLineSplit.orderType.IsEqual<SOLine.orderType>
            .And<SOLineSplit.orderNbr.IsEqual<SOLine.orderNbr>>
            .And<SOLineSplit.lineNbr.IsEqual<SOLine.lineNbr>>>), Persistent = false)]
    public class SOOrderProjection : IBqlTable
    {
        #region OrderDate
        [PXDBDate(BqlField = typeof(SOOrder.orderDate))]
        [PXUIField(DisplayName = "Order Date")]
        public virtual DateTime? OrderDate { get; set; }
        public abstract class orderDate : BqlDateTime.Field<orderDate> { }
        #endregion

        #region OrderType
        [PXDBString(2, IsFixed = true, BqlField = typeof(SOOrder.orderType))]
        [PXUIField(DisplayName = "Order Type")]
        public virtual string OrderType { get; set; }
        public abstract class orderType : BqlString.Field<orderType> { }
        #endregion

        #region OrderNbr
        [PXDBString(15, IsUnicode = true, InputMask = "", BqlField = typeof(SOOrder.orderNbr))]
        [PXUIField(DisplayName = "Order Nbr")]
        public virtual string OrderNbr { get; set; }
        public abstract class orderNbr : BqlString.Field<orderNbr> { }
        #endregion

        #region OrderDesc
        [PXDBString(60, IsUnicode = true, BqlField = typeof(SOOrder.orderDesc))]
        [PXUIField(DisplayName = "Order Description")]
        public virtual string OrderDesc { get; set; }
        public abstract class orderDesc : BqlString.Field<orderDesc> { }
        #endregion

        #region CustomerID
        [PXDBInt(BqlField = typeof(SOOrder.customerID))]
        [PXUIField(DisplayName = "Customer ID")]
        public virtual int? CustomerID { get; set; }
        public abstract class customerID : BqlInt.Field<customerID> { }
        #endregion

        #region InventoryID
        [PXDBInt(BqlField = typeof(SOLine.inventoryID))]
        [PXUIField(DisplayName = "Inventory ID")]
        public virtual int? InventoryID { get; set; }
        public abstract class inventoryID : BqlInt.Field<inventoryID> { }
        #endregion

        #region SiteID
        [PXDBInt(BqlField = typeof(SOLine.siteID))]
        [PXUIField(DisplayName = "Site ID")]
        public virtual int? SiteID { get; set; }
        public abstract class siteID : BqlInt.Field<siteID> { }
        #endregion

        #region UOM
        [PXDBString(6, IsUnicode = true, BqlField = typeof(SOLine.uOM))]
        [PXUIField(DisplayName = "UOM")]
        public virtual string UOM { get; set; }
        public abstract class uOM : BqlString.Field<uOM> { }
        #endregion

        #region OrderQty
        [PXDBDecimal(6, BqlField = typeof(SOLine.orderQty))]
        [PXUIField(DisplayName = "Order Quantity")]
        public virtual decimal? OrderQty { get; set; }
        public abstract class orderQty : BqlDecimal.Field<orderQty> { }
        #endregion

        #region CuryUnitPrice
        [PXDBDecimal(6, BqlField = typeof(SOLine.curyUnitPrice))]
        [PXUIField(DisplayName = "Currency Unit Price")]
        public virtual decimal? CuryUnitPrice { get; set; }
        public abstract class curyUnitPrice : BqlDecimal.Field<curyUnitPrice> { }
        #endregion

        #region LotSerialNbr
        [PXDBString(100, IsUnicode = true, BqlField = typeof(SOLineSplit.lotSerialNbr))]
        [PXUIField(DisplayName = "Lot/Serial Number")]
        public virtual string LotSerialNbr { get; set; }
        public abstract class lotSerialNbr : BqlString.Field<lotSerialNbr> { }
        #endregion
    }
}

 

Happy coding!

 

DISCLAIMER NOTE: Do not use code from generative AI models without a careful review and thorough understanding of what it’s doing!

Did this topic help you find an answer to your question?

1 reply

Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2640 replies
  • July 26, 2024

Thank you for sharing this tip with the community @MichaelShirk!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings