Solved

What the best practice for doing cascading deletes?

  • 15 February 2023
  • 6 replies
  • 240 views
D
Rank
Userlevel 7
Badge +5

I have a pair of DACs that are in a loose header/detail relationship. It isn’t directly parent/child but the header and detail fields are joined by shared data value, like a transaction number.

The header DAC is linked to a field on SOLine using the same transaction number value.

If the user attempts to delete the SOLine record, I want to check to see if it is okay to delete the related Header DAC. The Header DAC, in turn, needs to see if the detail DAC records can be deleted.

If the Header DAC and Detail DAC records can be deleted, then I’ll let the SOLine.RecordDeleting event continue. If there is a problem, I’ll set e.Cancel = true.

My question is - in what event should I do the deletion of the related DACs?  I need to instantiate a PXGraph for the Header and for each of the Detail records (each one could be a different PXGraph) because there are other tables that need to be updated.

I’m not supposed to be creating PXGraphs in event handlers (I believe).

Do I do this in SOLine.Persist when it is processing the deletion and create the HeaderGraph to invoke it’s Delete logic (which will, in turn, call the DetailDAC delete logic in the headerGraph.Persist method)?  I’ll wrap these in a transaction so if there is a failure everything will roll back.

icon

Best answer by Brian Stevens 15 February 2023, 22:05

View original

6 replies

Leonardo Justiniano
Rank
Userlevel 6
Badge +4

Hi @Django 

 

Define Parent Child links using PXParent Attribute. Example

Reference: Acumatica PXParent behaviour with table with Cascade delete - Stack Overflow

 

 

Leo
Brian Stevens
Rank
Userlevel 6
Badge +4

I agree with @Leonardo Justiniano about best practice being to just use PXParent in most cases and make the strict Parent Child Relationship.  However, there are times you do not want to automatically delete the children with the parent but rather require the manual removal of the child records or else prevent removal of the parent.

 

If you just want to check and prevent deleting one record based on the presence of another related record, that’s handled via Referential Integrity Check.  Check the answer from Brendan from the following StackOverflow post for explanation.

Reference Post: Acumatica PXParent behaviour with table with Cascade delete - Stack Overflow

 

If you really need to do something based on a delete manually, the RowPersisting event and test for e.Operation == PXDBOperation.Delete.  You can find examples of this in the code repository if you look for PXDBOperation.Delete.

// Remove attributes for the Repair when deleting the Repair
if (e.Operation == PXDBOperation.Delete)
{
	using (PXTransactionScope ts = new PXTransactionScope())
	{
		PXDatabase.Delete<CSAnswers>(new PXDataFieldRestrict("RefNoteID", PXDbType.UniqueIdentifier, row.NoteID));
		ts.Complete(this);
	}
}

 

Changing the world, one byte at a time - https://bstevens.net/ (formerly acumatica.dev)
D
Rank
Userlevel 7
Badge +5

My (limited) understanding of PXParent is used when defining a more traditional header/detail relationship like SOOrder and SOLine where SOLine always has a parent. Happy to be told otherwise. :)

The challenge, I think, is that my Detail DAC records can exist all by themselves. They do not have to have a Header DAC.

Imagine that my Detail DAC is a sheet of paper with some information on it.  I can have sheets of paper that aren’t related in any way.

But then I get a folder and put some of those sheets of paper into the folder and give the folder a # and I also put the folder # on the sheets of paper to “link” them.  The folder is my Header DAC. It may have zero, one or many Sheets.  A Sheet may have zero or one Folder.

When deleting a folder, I need to examine any sheets it has to make sure they can be deleted (e.g. Sheet.Status != Released). Just detecting the presence of the related record isn’t specific enough, unfortunately.

I think that I can read the records to see if we can proceed with the deletion or not - I don’t need a graph (outside of “this” or “Base”) to look at the DACs and decide if we can proceed or not.

I’m fairly certain I need to use the proper graphs to do the deletions properly (these PXGraphs are from a third-party ISV so I can’t/don’t want to replicate their underlying logic). I’m just not sure where I should write the code to instantiate the PXGraphs.

 

 

 

 

 

 

 

Brian Stevens
Rank
Userlevel 6
Badge +4

In that case, with my very limited understanding of your need… if you are adamant that PXParent and ReferentialIntegrityAttribute are insufficient options to meet your need, you can also try adding your final check in RowDeleting, RowPersisting (as describe above) or override Persist and perform a final check to totally prevent saving a delete.

 

Changing the world, one byte at a time - https://bstevens.net/ (formerly acumatica.dev)
D
Rank
Userlevel 7
Badge +5

I appreciate your thoughts and suggestions.

Reading more on PXParent, I see that you don’t have to have it combined with PXDBDefault.

Presumably, if a Sheet doesn’t belong to a Folder then it will have no parent and that shouldn’t be a problem.

Thank you!

 

D
Rank
Userlevel 7
Badge +5

Just adding a final note and for future me who will probably Google this again and find this question: PXParent did the trick on the last field of the foreign key in the other table. And, in the DAC of the other table, I set up a public static class FK to link back to the parent table.

public static class FK {    public class OrderLine : SOLine.PK.ForeignKeyOf<SOLine>.By<SOOrderType, SOOrderNbr, SOOrderLineNbr> { }  }
#region SOOrderLineNbr  [PXDBInt()]  [PXUIField(DisplayName = "Order Line Nbr")]  [PXParent(typeof(FK.OrderLine))]  public virtual int? SOOrderLineNbr { get; set; }  public abstract class soOrderLineNbr : PX.Data.BQL.BqlInt.Field<soOrderLineNbr> { }  #endregion

When the SOLine record is deleted, my record is deleted in the same transaction.

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