Skip to main content

I have two UDF’s in the CRCase table.  Those UDF’s are derived from fields in a custom maintenance table.  The UDFs are ServiceTypeCD and RecordTypeCD and they come from the custom maintenance table.

When a user tries to delete a record from the maintenance table, I want to make sure that the record isn’t in use in the CRCase table (no references in CRCase to the record in the custom table).

This is the event I am using to trap this.

This is a BQL select statement that I would use if I was just pulling data from the CRCase table if I was not looking for a UDF in that table.  Just to make sure it compiles, I put CRCase.caseCD in the Where clause (twice).  These *should* be the UDF fields.  I don’t know how to reference the UDF’s in a BQL select for a standard Acumatica table.  

Any advice would be greatly appreciated.

		protected void _(Events.RowDeleting<SSGCRServiceTypes> e)
		{
			SSGCRServiceTypes row = (SSGCRServiceTypes)e.Row;

			if (row == null) return;

			CRCase finditem = SelectFrom<CRCase>.
				Where<CRCase.caseCD.IsEqual<@P.AsString>.
					And<CRCase.caseCD.IsEqual<@P.AsString>>>.
				View.Select(this, row.RecordTypeCD, row.ServiceTypeCD).TopFirst;

			if (finditem != null && MasterView.Current != null && MasterView.Cache.GetStatus(MasterView.Current) != PXEntryStatus.Deleted)
			{
				e.Cancel = true;
				throw new PXException(Helper.Messages.ServiceSupportInUse);
			}
		}

 

Hi @Leonardo Justiniano 

That did the trick.  Hopefully in two years when this happens again, I’ll find this post when I search for help.  :-)

Thank you again.

Hi @joe21 

Please recompile the SQL view. SQL Servers store the mapping to the original column type when you create the view. Dropping and Recreating the view should solve the issue.

 

@Leonardo Justiniano gave me solution to my original issue.  I implemented the advice and it was working great.

After re-working the customization, I changed the UDF fields to be of type INT rather than CHAR.  This solution was working before, but now that I changed the fields to INT (using ID’s rather than strings), it does not work any longer.

It is a super simple SQL View.  When I do a select in Query Analyzer, it returns the expected result.  But the View in the graph is not returning any data.

This is the SQL View:

CREATE VIEW Edbo]. SSGCRCaseUsrServiceTypeIDInUse]

AS 
    SELECT 
        CaseCD, 
        UsrServiceTypeID
    FROM 
        dbo.CRCase
GO
The corresponding DAC is:

{
    eSerializable]
    {PXCacheName("CRCase UsrServiceTypeID In Use")]
    public class SSGCRCaseUsrServiceTypeIDInUse : IBqlTable
    {
        #region Casecd
         PXDBString(10, IsUnicode = true, InputMask = "")]
        SPXUIField(DisplayName = "Casecd")]
        public virtual string Casecd { get; set; }
        public abstract class casecd : PX.Data.BQL.BqlString.Field<casecd> { }
        #endregion

        #region UsrServiceTypeID
                public virtual int? UsrServiceTypeID { get; set; }
        public abstract class usrServiceTypeID : PX.Data.BQL.BqlInt.Field<usrServiceTypeID> { }
        #endregion
    }
}

 

I tried using a view as follows but it doesn’t return any records when I check the view in the rowselected handler (just to test)

public SelectFrom<SSGCRCaseUsrServiceTypeIDInUse>.
Where<SSGCRCaseUsrServiceTypeIDInUse.usrServiceTypeID.
IsEqual<SSGCRServiceType.serviceTypeID.FromCurrent>>.View LinksExist;
 

I also tried getting the data with this but it also comes back null

SSGCRCaseUsrServiceTypeIDInUse finditem = SelectFrom<SSGCRCaseUsrServiceTypeIDInUse>.
    Where<SSGCRCaseUsrServiceTypeIDInUse.usrServiceTypeID.IsEqual<@P.AsInt>>.
    View.Select(this, row.ServiceTypeID);
 

While debugging, I verified that the row.ServiceTypeID has the correct value and that value exists in the CRCase table in the correct UDF.

I *could* go back and re-work it to store the CD value in the UDF as opposed to an INT value, but if anyone sees anything obviously wrong here, I’d rather not waste hours re-working it.  Plus, I don’t know for sure if that will even solve the issue.  It WAS working when searching by a string value.

 

@Leonardo Justiniano 

Great idea.  Thanks for that!  That would be super easy.

Hi @joe21 

 

You can create a SQL View with well defined column names that consume the corresponding UDF columns ( User Defined Function ?). Then having the DAC is routine.

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