Referential Integrity

Data structures often include parent-child relationships in various forms. Sometimes the parent is a container such as an order header with the child being the order details. In the case of Acumatica, this sometimes exists in multiple tiers, such as SOLineSplit being a child to SOLine which is a child to SOOrder. In that example, deleting the parent is acceptable provided it also removes the child records.

Sometimes the parent-child relationship is that of master data as the parent, and as such, you should be unable to delete the master data record when child records exist. For instance, once an item is used on a Sales Order, you would not want to delete the item and subsequently remove the sales order lines. InventoryItem includes use of PXReferentialIntegrityCheckAttribute to prevent such a catastrophy.

To understand how this attribute works, we only need to look at the summary attached to the attribute. In our case, we are interested in using the attribute on the parent only. “In case when only parent-PX.Data.IBqlTable is included in referential integrity check, only children presence checking will be performed on parent-row deleting.” For convenience, the full summary from the attribute is below:

Passively includes targeted PX.Data.IBqlTable in referential integrity check without creating any PX.Data.ReferentialIntegrity.References.
PX.Data.ReferentialIntegrity.References could be declared explicitly by using PX.Data.ReferentialIntegrity.Attributes.PXForeignReferenceAttribute (on child
side).
Certain rows could be excluded from referential integrity check by using PX.Data.ReferentialIntegrity.Attributes.PXExcludeRowsFromReferentialIntegrityCheckAttribute.
In case when only parent-PX.Data.IBqlTable is included in referential integrity check, only children presence checking will be performed on parent-row deleting.
In case when only child-PX.Data.IBqlTable is included in referential integrity check, only parent presence checking will be performed on child-row inserting.
In case when both child-PX.Data.IBqlTable and parent-PX.Data.IBqlTable are included in referential integrity check, both checks will be performed on corresponding events.

To enable this data integrity feature, we simply need to perform 2 steps. Make the parent-child relationship and attach the attribute. In our example, our master data (parent) is Manufacturer, and our child is ItemManufacturer.

In the child DAC (ItemManufacturer), define the foreign reference:

#region ManufacturerID
[PXDBInt(IsKey = true)]
[PXDefault]
[PXForeignReference(typeof(Field<manufacturerID>.IsRelatedTo<Manufacturer.manufacturerID>))]
[PXSelector(
	typeof(Search<Manufacturer.manufacturerID>),
	typeof(Manufacturer.manufacturerCD),
	SubstituteKey = typeof(Manufacturer.manufacturerCD),
	Filterable = true
	)]
[PXUIField(DisplayName = Messages.FldManufacturerName)]
public virtual int? ManufacturerID { get; set; }
public abstract class manufacturerID : PX.Data.BQL.BqlInt.Field<manufacturerID> { }

In the parent DAC (Manufacturer), use the PXReferentialIntegrityCheck attribute:

#region ManufacturerID
[PXDBIdentity]
[PXReferentialIntegrityCheck]
public virtual int? ManufacturerID { get; set; }
public abstract class manufacturerID : PX.Data.BQL.BqlInt.Field<manufacturerID> { }
#endregion

The result will be an error message preventing removal of the “master data” (Parent DAC) when it is in use in a Child DAC. (Sensitive data has been blurred for the blog post.)

While clearly the summary of the attribute shows that it can do more, our focus was on ensuring master data is not deleted. Of course if you prefer, you can always go into every graph using your master data and include validation to check for the presence of child data manually, but PXReferentialIntegrityCheck in the DAC will make the validation universal to anywhere that DAC is used, not to mention makes it quick and easy to get the job done.

Leave a Reply