How to resolve a Result set index too big on Custom Field?

The error sounds to me like it would be a problem with the view. Can you detail how you're inserting?

Hi Daryl,

Currently, I only use the PXSAVE / PXInsert standard functionality. 

I added an Entry graph when I created the screen and only added the following:
 

// Standard
public PXSave<LTFGHeader> Save;
public PXCancel<LTFGHeader> Cancel;
public PXInsert<LTFGHeader> Insert;
public PXDelete<LTFGHeader> Delete;
public PXCopyPasteAction<LTFGHeader> CopyPaste;
public PXFirst<LTFGHeader> First;
public PXPrevious<LTFGHeader> Prev;
public PXNext<LTFGHeader> Next;
public PXLast<LTFGHeader> Last;

// Selection
public SelectFrom<LTFGHeader>.View FinishedGoods;

Then the LTFGHeader  DAC:

using System;
using PX.Data;
using PX.Objects.CS;
using PX.Objects.IN;
using PX.Objects.GL;
using PX.Data.ReferentialIntegrity.Attributes;

namespace MALTFinishedGoods
{
    [Serializable]
    [PXCacheName("LTFGHeader")]
    public class LTFGHeader : IBqlTable
    {
        //public class PK : PrimaryKeyOf<LTFGHeader>.By<batchNbr>
        //{
        //    public static LTFGHeader Find(PXGraph graph, int? batchNbr) => FindBy(graph, batchNbr);
        //}

        #region FinGoodsID
        [PXDBIdentity(IsKey = true)]
        public virtual int? FinGoodsID { get; set; }
        public abstract class finGoodsID : PX.Data.BQL.BqlInt.Field<finGoodsID> { }
        #endregion

        #region FinGoodsCD
        [PXDBString(45, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Description")]
        public virtual string FinGoodsCD { get; set; }
        public abstract class finGoodsCD : PX.Data.BQL.BqlString.Field<finGoodsCD> { }
        #endregion

        #region CreatedByID
        [PXDBCreatedByID()]
        [PXUIField(DisplayName = "Created By ID")]
        public virtual Guid? CreatedByID { get; set; }
        public abstract class createdByID : PX.Data.BQL.BqlGuid.Field<createdByID> { }
        #endregion

        #region CreatedByScreenID
        [PXDBCreatedByScreenID()]
        [PXUIField(DisplayName = "Created By Screen ID")]
        public virtual string CreatedByScreenID { get; set; }
        public abstract class createdByScreenID : PX.Data.BQL.BqlString.Field<createdByScreenID> { }
        #endregion

        #region CreatedDateTime
        [PXDBCreatedDateTime()]
        public virtual DateTime? CreatedDateTime { get; set; }
        public abstract class createdDateTime : PX.Data.BQL.BqlDateTime.Field<createdDateTime> { }
        #endregion

        #region LastModifiedByID
        [PXDBLastModifiedByID()]
        [PXUIField(DisplayName = "Last Modified By ID")]
        public virtual Guid? LastModifiedByID { get; set; }
        public abstract class lastModifiedByID : PX.Data.BQL.BqlGuid.Field<lastModifiedByID> { }
        #endregion

        #region LastModifiedByScreenID
        [PXDBLastModifiedByScreenID()]
        [PXUIField(DisplayName = "Last Modified By Screen ID")]
        public virtual string LastModifiedByScreenID { get; set; }
        public abstract class lastModifiedByScreenID : PX.Data.BQL.BqlString.Field<lastModifiedByScreenID> { }
        #endregion

        #region LastModifiedDateTime
        [PXDBLastModifiedDateTime()]
        public virtual DateTime? LastModifiedDateTime { get; set; }
        public abstract class lastModifiedDateTime : PX.Data.BQL.BqlDateTime.Field<lastModifiedDateTime> { }
        #endregion

        #region BatchNbr
        [PXDBString(15, IsKey = true, IsUnicode = true, InputMask = ">CCCCCCCCCCCCCCC")]
        [PXUIField(DisplayName = "Batch Number", Visibility = PXUIVisibility.SelectorVisible)]
        [PXDefault(PersistingCheck = PXPersistingCheck.NullOrBlank)]
        [AutoNumber(typeof(INSetupExt.usrFinGoodsBatchNbr), typeof(LTFGHeader.batchDate))]
        [PXSelector(typeof(Search<LTFGHeader.batchNbr>))]
        public virtual string BatchNbr { get; set; }
        public abstract class batchNbr : PX.Data.BQL.BqlString.Field<batchNbr> { }
        #endregion

        #region BranchID
        [Branch]
        [PXUIField(DisplayName = "Branch ID")]
        public virtual int? BranchID { get; set; }
        public abstract class branchID : PX.Data.BQL.BqlInt.Field<branchID> { }
        #endregion

        #region Noteid
        [PXNote()]
        [PXUIField(DisplayName = "Noteid")]
        public virtual Guid? Noteid { get; set; }
        public abstract class noteid : PX.Data.BQL.BqlGuid.Field<noteid> { }
        #endregion

        #region Status
        [PXDBString(45, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Status")]
        [PXDefault("OH")]
        [PXStringList(new string[] { "OH", "B", "R", "RV" }, new string[] { "On-Hold", "Balanced", "Released", "Reversed" })]
        public virtual string Status { get; set; }
        public abstract class status : PX.Data.BQL.BqlString.Field<status> { }
        #endregion

        #region BatchType
        [PXDBString(45, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Batch Type")]
        [PXDefault("B")]
        [PXStringList(new string[] { "B", "BRV" }, new string[] { "Batch", "Batch Reversal" })]
        public virtual string BatchType { get; set; }
        public abstract class batchType : PX.Data.BQL.BqlString.Field<batchType> { }
        #endregion

        #region ProcessType
        [PXDBString(45, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Process Type")]
        [PXDefault("M")]
        [PXStringList(new string[] { "M", "P", "S", "O" }, new string[] { "Market", "Processed", "Seed", "Other" })]
        public virtual string ProcessType { get; set; }
        public abstract class processType : PX.Data.BQL.BqlString.Field<processType> { }
        #endregion

        #region ProcessTime
        [PXDBDecimal()]
        [PXUIField(DisplayName = "Processing Time (Hour)")]
        public virtual Decimal? ProcessTime { get; set; }
        public abstract class processTime : PX.Data.BQL.BqlDecimal.Field<processTime> { }
        #endregion

        #region BatchDate
        [PXDBDate()]
        [PXDefault(typeof(AccessInfo.businessDate))]
        [PXUIField(DisplayName = "Batch Date")]
        public virtual DateTime? BatchDate { get; set; }
        public abstract class batchDate : PX.Data.BQL.BqlDateTime.Field<batchDate> { }
        #endregion

        #region TranPeriodID
        [PeriodID]
        public virtual String TranPeriodID { get; set; }
        public abstract class tranPeriodID : PX.Data.BQL.BqlString.Field<tranPeriodID> { }
        #endregion

        #region PostPeriod
        [PXDBString(45, IsUnicode = true, InputMask = "")]
        [INOpenPeriod(
            sourceType: typeof(batchDate),
            branchSourceType: typeof(branchID),
            masterFinPeriodIDType: typeof(tranPeriodID),
            IsHeader = true)]
        [PXDefault]
        [PXUIField(DisplayName = "Post Period")]
        //[OpenPeriod(null, typeof(Batch.dateEntered), typeof(Batch.branchID), masterFinPeriodIDType: typeof(Batch.tranPeriodID), IsHeader = true)]
        public virtual string PostPeriod { get; set; }
        public abstract class postPeriod : PX.Data.BQL.BqlString.Field<postPeriod> { }
        #endregion

        #region OutItemAmt
        [PXDBDecimal()]
        [PXDefault(TypeCode.Decimal,"0.0")]
        [PXUIField(DisplayName = "Amount", Enabled = false)]
        public virtual Decimal? OutItemAmt { get; set; }
        public abstract class outItemAmt : PX.Data.BQL.BqlDecimal.Field<outItemAmt> { }
        #endregion

        #region OutItemQty
        [PXDBDecimal()]
        [PXDefault(TypeCode.Decimal,"0.0")]
        [PXUIField(DisplayName = "OutItemQty", Enabled = false)]
        public virtual Decimal? OutItemQty { get; set; }
        public abstract class outItemQty : PX.Data.BQL.BqlDecimal.Field<outItemQty> { }
        #endregion

        #region MaterialAmt
        [PXDBDecimal()]
        [PXDefault(TypeCode.Decimal,"0.0")]
        [PXUIField(DisplayName = "Amount", Enabled = false)]
        public virtual Decimal? MaterialAmt { get; set; }
        public abstract class materialAmt : PX.Data.BQL.BqlDecimal.Field<materialAmt> { }
        #endregion

        #region MaterialQty
        [PXDBDecimal()]
        [PXDefault(TypeCode.Decimal,"0.0")]
        [PXUIField(DisplayName = "MaterialQty", Enabled = false)]
        public virtual Decimal? MaterialQty { get; set; }
        public abstract class materialQty : PX.Data.BQL.BqlDecimal.Field<materialQty> { }
        #endregion

        #region LabourAmt
        [PXDBDecimal()]
        [PXDefault(TypeCode.Decimal,"0.0")]
        [PXUIField(DisplayName = "Amount", Enabled = false)]
        public virtual Decimal? LabourAmt { get; set; }
        public abstract class labourAmt : PX.Data.BQL.BqlDecimal.Field<labourAmt> { }
        #endregion

        #region LabourQty
        [PXDBDecimal()]
        [PXDefault(TypeCode.Decimal,"0.0")]
        [PXUIField(DisplayName = "LabourQty", Enabled = false)]
        public virtual Decimal? LabourQty { get; set; }
        public abstract class labourQty : PX.Data.BQL.BqlDecimal.Field<labourQty> { }
        #endregion

        #region RawMaterial
        [PXDBDecimal()]
        [PXDefault(TypeCode.Decimal,"0.0")]
        [PXUIField(DisplayName = "RawMaterial", Enabled = false)]
        public virtual Decimal? RawMaterial { get; set; }
        public abstract class rawMaterial : PX.Data.BQL.BqlDecimal.Field<rawMaterial> { }
        #endregion
    }
}

Hi, @DewaldH  There are a couple of suggestions from my end that may help you!

1.  Instead of declaring all the standard actions, you can declare your graph like below, by default Acumatica will provide the standard actions to the form.

Ex:  public class mytestgraph : PXGraph<mytestgraph,FinishedGoods >

{

    public SelectFrom<LTFGHeader>.View FinishedGoods;

}

2. You already mentioned that the PXDBIdentity field i.e. FinGoodsID is a KEY field and no need to declare another field i.e. BatchNbr as KEY (you can remove this)
3. The below fields are static dropdown fields, hence size should not be 45, provide the database size accordingly.
   • Status
   • BatchType
   • ProcessType,
4.  For all the Decimal fields, the default attribute should be declared like below.
   •  [PXDefault(TypeCode.Decimal, "0.00", PersistingCheck = PXPersistingCheck.Nothing)]

Apart from this, I don’t see any issue with the DAC and Graph code. 

Can you please provide the below details?

1. How many records are there in this table? -LTFGHeader
2. How did you declare the decimal fields in the database like scale and precisions?

Hi Naveen, 

Thanks for the tips, I have adjusted my DAC and Graph accordingly. 

I cleared all the tables and re-published with a clean-up.

Afterwards, I added a record and saved. Works perfectly. 

But as soon as I navigate, reload or try to add another one, I get this:

So, to answer your questions:

1. Currently, only one record.
2. Table declarations are as follows:

   

   I originally had it as decimal(25,6) but tried different variations but with the same result. 

Thanks

HI @DewaldH  There are minor changes required on the data base sizes but I’m sure that will not cause this issue.

This error is strange to me!!

If possible, can you please create a separate customization package for this form and share (including .aspx files, Database scripts (tables), DAC file, and Graph code file). I can check from my end and let you know.

Hi Naveen,

I am definitely going to try that today and let you know.

Thanks

So I have created a new instance of Acumatica, recreated everything from sractch but this time, I left out the Raw Materials field and now the error moves to the previous field. 

Error: An error occurred during processing of the field LabourQty: Result set index too big..

Trace:

5/04/2022 10:26:26 AM Error:
Error: An error occurred during processing of the field LabourQty: Result set index too big..

System.IndexOutOfRangeException: Result set index too big.
   at Devart.Data.MySql.MySqlDataReader.a(Int32 A_0, String A_1)
   at Devart.Data.MySql.MySqlDataReader.IsDBNull(Int32 i)
   at PX.Data.PXDataRecord.GetString(Int32 i)
   at PX.MySql.PXMySqlDataRecord.GetDecimal(Int32 i)
   at PX.Data.PXDBDecimalAttribute.RowSelecting(PXCache sender, PXRowSelectingEventArgs e)
   at PX.Data.PXCache.OnRowSelecting(Object item, PXDataRecord record, Int32& position, Boolean isReadOnly)
   at PX.Data.PXCache.OnRowSelecting(Object item, PXDataRecord record, Int32& position, Boolean isReadOnly)
   at PX.Data.PXCache`1.Select(PXDataRecord record, Int32& position, Boolean isReadOnly, Boolean& wasUpdated)
   at PX.Data.PXView.CreateItem(PXCache cache, PXDataRecord record, Int32& position, Boolean isReadOnly, Boolean& wasUpdated)
   at PX.Data.PXView.CreateResult(PXCache[] caches, PXDataRecord rec, Boolean hascount, Boolean& overrideSort, Boolean& extFilter)
   at PX.Data.PXView.GetResult(Object[] parameters, PXFilterRow[] filters, Boolean reverseOrder, Int32 topCount, PXSearchColumn[] sorts, Boolean& overrideSort, Boolean& extFilter)
   at PX.Data.PXView.Select(Object[] currents, Object[] parameters, Object[] searches, String[] sortcolumns, Boolean[] descendings, PXFilterRow[] filters, Int32& startRow, Int32 maximumRows, Int32& totalRows)
   at PX.Data.PXGraph.ExecuteSelect(String viewName, Object[] currents, Object[] parameters, Object[] searches, String[] sortcolumns, Boolean[] descendings, PXFilterRow[] filters, Int32& startRow, Int32 maximumRows, Int32& totalRows)

I believe this is issue in the Devart library. Will update once I have more info.