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 @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.

@Leonardo Justiniano 

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

@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.

 

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.

 

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.

Reply


Terms & ConditionsCookie settings