Solved

Have a Process Screen, Its data source is a projection view that displays fine but when I click on select, the row blanks out

  • 27 December 2023
  • 15 replies
  • 162 views
E
Rank
Userlevel 2
Badge

I have a custom process screen that loads fine from a projection view created on the customer, location, InventoryItem and ARSalesPrice tables.  The data loads fine, but soon as I click on the select for one of the Lines, the whole row blanks out the data it was displaying just fine.  

Wondering if anyone has any ideas on how to even approach this one. 

The error message says 

Operand type clash: datetime2 is incompatible with int

It is a SQL Error: 

Stack trace here: 

t PX.Data.PXFirstChanceExceptionLogger.ProfilerFirstChanceException(Object o, FirstChanceExceptionEventArgs args) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at PX.Data.PXSqlDatabaseProvider.ExecuteReaderInternal(IDbCommand command, CommandBehavior behavior) at PX.Data.PXSelectResult.PXSelectResultEnumerator.System.Collections.IEnumerator.MoveNext() at PX.Data.PXCache`1.readItem(TNode item, Boolean donotplace, Boolean byNoteID) at PX.Data.PXCache`1.Update(IDictionary keys, IDictionary values) at PX.Data.PXGraph.ExecuteUpdate(String viewName, IDictionary keys, IDictionary values, Object[] parameters) at PX.Web.UI.PXBaseDataSource.ExecuteUpdate(String viewName, IDictionary keys, IDictionary values, IDictionary oldValues) at PX.Web.UI.PXDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) at System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) at PX.Web.UI.PXGrid.PerformUpdate(IOrderedDictionary keys, IOrderedDictionary values, IOrderedDictionary oldValues) at PX.Web.UI.PXGrid.CommitModifiedRows(PXGridLevel level, XmlElement changes, XmlWriter writer) at PX.Web.UI.PXGrid.CommitClientChanges(XmlElement changes) at PX.Web.UI.PXGrid.PX.Web.UI.IPXDataControl.CommitDataChanges(String clientData) at PX.Web.UI.PXGrid.ExecuteCallback(PXCallbackCommand cmd, String strData) at PX.Web.UI.PXCallbackManager.CommitDataChanges(XmlNode changesNode, PXCallbackExecMethod executeMethod) at PX.Web.UI.PXCallbackManager.ProcessCallbackInternal(String strData, PXCallbackExecMethod executeMethod) at PX.Web.UI.PXCallbackManager.ProcessCallBack(String strData, PXCallbackExecMethod executeMethod) at System.Web.UI.Page.PrepareCallback(String callbackControlID) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequest(HttpContext context) at PX.Web.UI.PXPage.ProcessRequest(HttpContext context) at ASP.cstpublished_pages_jm_jm502011_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error) at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb) at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags) at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags) at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus) at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus) at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags) at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

The selected field definition: 

#region Selected
[PXBool]
[PXDefault(false, PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "Selected")]
public virtual bool? Selected { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
public abstract class selected : BqlBool.Field<selected> { }
#endregion

and the projection view: 

[PXProjection(typeof(SelectFrom<Customer>
            .InnerJoin<Location>.On<Customer.bAccountID.IsEqual<Location.bAccountID>
            >
            .CrossJoin<InventoryItem>
            .InnerJoin<INSiteStatus>.On<InventoryItem.inventoryID.IsEqual<INSiteStatus.inventoryID>>
            .InnerJoin<ARSalesPrice>.On<Customer.bAccountID.IsEqual<ARSalesPrice.customerID>
            .And<InventoryItem.inventoryID.IsEqual<ARSalesPrice.inventoryID>>
            .And<ARSalesPrice.priceType.IsEqual<PriceTypeList.customer>>
            >
            .Where<Customer.status.IsEqual<CustomerStatus.active>.And<InventoryItem.itemStatus.IsEqual<InventoryItemStatus.active>
                .And<InventoryItem.stkItem.IsEqual<True>>
                .And<InventoryItem.isTemplate.IsEqual<False>>
                .And<ARSalesPriceExt.usrSrItemClassID.IsNull>
                .And<ARSalesPrice.breakQty.IsEqual<decimal0>>
                .And<ARSalesPrice.salesPrice.IsGreater<decimal0>>
                .And<ARSalesPrice.effectiveDate.IsLessEqual<@P.AsDateTime>>
                .And<ARSalesPrice.expirationDate.IsGreater<@P.AsDateTime>>
                >>
            .AggregateTo<GroupBy<Customer.bAccountID>, GroupBy<Location.locationID>, GroupBy<InventoryItem.inventoryID>,
            GroupBy<Location.locationCD>, GroupBy<Customer.acctCD>, GroupBy<InventoryItem.inventoryCD>, GroupBy<InventoryItem.descr>, GroupBy<InventoryItem.salesUnit>>))]

 

 
icon

Best answer by edwardmcgovern53 3 February 2024, 16:40

View original

15 replies

darylbowman
Rank
Badge +11

You’re using @P.AsDateTime in your projection query. I’m guessing that’s the issue.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
E
Rank
Userlevel 2
Badge

Why is that an issue? I have used it before with projection views doing the same exact thing? 

 

E
Rank
Userlevel 2
Badge

Thats getting set in the constructor for the projection view: 

View.ReadOnly.Select(this,
                    new object[] { DateTime.Today, DateTime.Today })
                .Cast<PXResult<Customer, Location, InventoryItem, INSiteStatus, ARSalesPrice>>())

E
Rank
Userlevel 2
Badge

Sorry cut it off see bold italic: 

foreach (PXResult<Customer, Location, InventoryItem, INSiteStatus, ARSalesPrice> results in
    SelectFrom<Customer>
    .InnerJoin<Location>.On<Customer.bAccountID.IsEqual<Location.bAccountID>
    >
    .CrossJoin<InventoryItem>
    .InnerJoin<INSiteStatus>.On<InventoryItem.inventoryID.IsEqual<INSiteStatus.inventoryID>>
    .InnerJoin<ARSalesPrice>.On<Customer.bAccountID.IsEqual<ARSalesPrice.customerID>
    .And<InventoryItem.inventoryID.IsEqual<ARSalesPrice.inventoryID>>
    .And<ARSalesPrice.priceType.IsEqual<PriceTypeList.customer>>
    >
    .Where<Customer.status.IsEqual<CustomerStatus.active>.And<InventoryItem.itemStatus.IsEqual<InventoryItemStatus.active>
        .And<InventoryItem.stkItem.IsEqual<True>>
        .And<InventoryItem.isTemplate.IsEqual<False>>
        .And<ARSalesPriceExt.usrSrItemClassID.IsNull>
        .And<ARSalesPrice.breakQty.IsEqual<decimal0>>
        .And<ARSalesPrice.salesPrice.IsGreater<decimal0>>
        .And<ARSalesPrice.effectiveDate.IsLessEqual<@P.AsDateTime>>
        .And<ARSalesPrice.expirationDate.IsGreater<@P.AsDateTime>>
        >>
    .AggregateTo<GroupBy<Customer.bAccountID>, GroupBy<Location.locationID>, GroupBy<InventoryItem.inventoryID>,
    GroupBy<Location.locationCD>, GroupBy<Customer.acctCD>, GroupBy<InventoryItem.inventoryCD>, GroupBy<InventoryItem.descr>, GroupBy< InventoryItem.salesUnit>
    >
    
    .View.ReadOnly.Select(this,
        new object[] { DateTime.Today, DateTime.Today })
    .Cast<PXResult<Customer, Location, InventoryItem, INSiteStatus, ARSalesPrice>>())

darylbowman
Rank
Badge +11

Hmm. Perhaps I’m naive. I’ve never seen that before. I understood a projection to be a rather delicate mashup of several tables.

So you defined a projection. Could you post the whole projection class, fields, and all?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
E
Rank
Userlevel 2
Badge

 

[Serializable]
[PXHidden]
[PXProjection(typeof(SelectFrom<Customer>
            .InnerJoin<Location>.On<Customer.bAccountID.IsEqual<Location.bAccountID>
            >
            .CrossJoin<InventoryItem>
            .InnerJoin<INSiteStatus>.On<InventoryItem.inventoryID.IsEqual<INSiteStatus.inventoryID>>
            .InnerJoin<ARSalesPrice>.On<Customer.bAccountID.IsEqual<ARSalesPrice.customerID>
            .And<InventoryItem.inventoryID.IsEqual<ARSalesPrice.inventoryID>>
            .And<ARSalesPrice.priceType.IsEqual<PriceTypeList.customer>>
            >
            .Where<Customer.status.IsEqual<CustomerStatus.active>.And<InventoryItem.itemStatus.IsEqual<InventoryItemStatus.active>
                .And<InventoryItem.stkItem.IsEqual<True>>
                .And<InventoryItem.isTemplate.IsEqual<False>>
                .And<ARSalesPriceExt.usrSrItemClassID.IsNull>
                .And<ARSalesPrice.breakQty.IsEqual<decimal0>>
                .And<ARSalesPrice.salesPrice.IsGreater<decimal0>>
                .And<ARSalesPrice.effectiveDate.IsLessEqual<@P.AsDateTime>>
                .And<ARSalesPrice.expirationDate.IsGreater<@P.AsDateTime>>
                >>
            .AggregateTo<GroupBy<Customer.bAccountID>, GroupBy<Location.locationID>, GroupBy<InventoryItem.inventoryID>,
            GroupBy<Location.locationCD>, GroupBy<Customer.acctCD>, GroupBy<InventoryItem.inventoryCD>, GroupBy<InventoryItem.descr>, GroupBy<InventoryItem.salesUnit>>))]
public class MYProjectionClass: IBqlTable
{
    #region Selected
    [PXBool]
    [PXDefault(false, PersistingCheck = PXPersistingCheck.Nothing)]
    [PXUIField(DisplayName = "Selected")]
    public virtual bool? Selected { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class selected : BqlBool.Field<selected> { }
    #endregion


    #region BAccountID
    [Customer(IsKey = true, DisplayName = "Customer ID", Visible = false, Enabled = false, Visibility = PXUIVisibility.Invisible, BqlField = typeof(Customer.bAccountID))]
    public virtual int? BAccountID { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class bAccountID : BqlInt.Field<bAccountID> { }
    #endregion

    #region AcctCD
    [PXDBString(30, IsUnicode = true, BqlField = typeof(Customer.acctCD))]
    [PXUIField(DisplayName = "Customer ID", Visibility = PXUIVisibility.SelectorVisible)]
    [PXDimension("BIZACCT")]
    public virtual string AcctCD { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class acctCD : BqlString.Field<acctCD> { }
    #endregion


    #region LocationID
    [PXDBInt(IsKey = true, BqlField = typeof(Location.locationID))]
    [PXUIField(DisplayName = "Location ID", Visible = false, Enabled = false, Visibility = PXUIVisibility.Invisible)]
    public virtual int? LocationID { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class locationID : BqlInt.Field<locationID> { }
    #endregion

    #region LocationCD            
    [CS.LocationRaw(typeof(Where<Location.bAccountID, Equal<Current<Location.bAccountID>>>), Visibility = PXUIVisibility.SelectorVisible, DisplayName = "Location ID", BqlField = typeof(Location.locationCD))]
    public virtual string LocationCD { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class locationCD : BqlString.Field<locationCD> { }
    #endregion


    #region InventoryID
    [PXDBInt(BqlField = typeof(InventoryItem.inventoryID))]
    [PXUIField(DisplayName = "Inventory ID", Visibility = PXUIVisibility.Visible, Visible = false)]
    public virtual int? InventoryID { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class inventoryID : BqlInt.Field<inventoryID> { }
    #endregion

    #region InventoryCD
    [InventoryRaw(IsKey = true, DisplayName = "Inventory ID", BqlField = typeof(InventoryItem.inventoryCD))]
    public virtual string InventoryCD { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class inventoryCD : BqlString.Field<inventoryCD> { }
    #endregion

    #region Descr
    [DBMatrixLocalizableDescription(Constants.TranDescLength, IsUnicode = true, BqlField = typeof(InventoryItem.descr))]
    [PXUIField(DisplayName = "Item Description", Visibility = PXUIVisibility.SelectorVisible)]
    public virtual string Descr { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class descr : BqlString.Field<descr> { }
    #endregion

    #region SalesUnit
    [INUnit(typeof(inventoryID), DisplayName = "Sales Unit", Visibility = PXUIVisibility.SelectorVisible, BqlField = typeof(InventoryItem.salesUnit))]
    public virtual string SalesUnit { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class salesUnit : BqlString.Field<salesUnit> { }
    #endregion


    #region CurrentPrice
    [PXPriceCost]
    [PXUIField(DisplayName = "Current Price")]
    public virtual decimal? CurrentPrice { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class currentPrice : BqlDecimal.Field<currentPrice> { }
    #endregion

    #region expirationDate
    [PXDBDate(BqlField = typeof(ARSalesPrice.expirationDate))]
    [PXUIField(DisplayName = "Expiration Date")]
    public virtual DateTime? ExpirationDate { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class expirationDate : BqlDateTime.Field<expirationDate> { }
    #endregion

    #region effectiveDate
    [PXDBDate(BqlField = typeof(ARSalesPrice.effectiveDate))]
    [PXUIField(DisplayName = "Effective Date")]
    public virtual DateTime? EffectiveDate { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "Acumatica Standard")]
    public abstract class effectiveDate : BqlDateTime.Field<effectiveDate> { }
    #endregion
}

E
Rank
Userlevel 2
Badge

Its not that complicated.  And its working in that the process form loads fine. Its when selected is checked the issue happens.  I’ve done the same exact thing with different tables and it works fine. 

darylbowman
Rank
Badge +11

Only other suggestion I have is to try moving the IsKey = true to the InventoryID field instead of InventoryCD, but that really has nothing to do with the datetime2.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
E
Rank
Userlevel 2
Badge

Same would kinda go for the ACCT field as opposed to the baccountid  but thats not anything to do with a date either. I could try moving the keys from anything integer as you say.  Not sure thats it.

E
Rank
Userlevel 2
Badge

Ok I found out why its happening.  Not sure on the fix.  Acumatica when you select a line in the grid runs a sql statment from the projection view (just select fields from projection view (and that is the full statment from the projection view) where and pastes in fields for the current record based on key setup in dax.     

The system pasted in @P0 which was aready in use in the projection.  so it was trying to put a currency id in the first key of the table which was integer.  Moral of the story is that, if you hit a data conversion error on selection of rows in a process screen and its a sql error according to Acumatica, then its most likely a parameter name being used twice for different fields.  

M
Rank
Userlevel 4
Badge

I would love to know what the fix for this is. 

I ran into the very same problem. In my case, I was able to work around it by setting every field as a key like this:

        #region SrvOrdType
        public abstract class srvOrdType : PX.Data.BQL.BqlString.Field<srvOrdType> { }
        [PXDBString(IsKey = true, BqlField = typeof(FSAppointmentDet.srvOrdType))]
        public virtual string SrvOrdType { get; set; }
        #endregion

But that’s a hacky work around. 

If anyone could shed light in the problem and the suggested fix, I would love to hear it.

E
Rank
Userlevel 2
Badge

I believe I was using IsKey = true to no avail.  Support had me add an actual Primary key to the DAC for the View.  

Like: 

 public class PK : PrimaryKeyOf<DACName>.By<field1, field2, ...>
 {
     public static DACName Find(PXGraph graph, type field1, type field2, ...)
         => Find(graph, field1, field2, ...);
     public abstract class field1 : PX.Data.BQL.BqlInt.Field<field1> { }
     public abstract class field2 : PX.Data.BQL.BqlInt.Field<field2> { }

….

 }

And it still didn’t work in this case.  The parameter I was using was the basecuryid, I was using to grab the currencyinfo record so I could do price calculations.  So instead of using basecuryid as a parameter like:  <Accessinfo.BaseCuryID.FromCurrent> (which converted into a parameter in processing) I filtered on another field and put some selection logic in the projection constructor.   I basically removed it.  From what I understand from opening a case that doing a projection view with parameters on a process screen is not recommended.  Odd thing is I have other screens doing just that, that work perfectly.  

So the solution was to take it out and do it in code. 

 

M
Rank
Userlevel 4
Badge

@edwardmcgovern53 did you set the Projection to Persistent = true?

BTW, the  IsKey = true worked for some fields, and for some it did not. This is really a frustrating bug...

E
Rank
Userlevel 2
Badge

No I was just reading about Persistent = true, I stumbled on it and will get it a whirl next time I hit this.  Never saw that property before.  Thanks for the input

M
Rank
Userlevel 4
Badge

@edwardmcgovern53 I’m not sure what the default is. But setting that true lets Acumatica know it can be changed. So, maybe it ends up bringing another cache into play at that point? IDK. Just a thought.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved