Solved

Using SOOrder primary key as foreign key in another table


Userlevel 5
Badge

Hello--

I am attempting a customization and ran into an issue:

I need to use OrderNbr column from a sales order to populate fields in another table. The problem is, when a sales order is created, OrderNbr is not generated until after the record is saved (RowPersisted?).

This value is required in order to generate rows in another table and relate them to this specific sales order. These rows will need to be edited during the creation of the sales order, so it cannot be done after the SO is saved.

 

What are best practices for getting around this issue in general? Is there another column I can use to reference this specific SO before it is saved? Maybe I should be working in cache?

 

Thank you!!!

 

icon

Best answer by Naveen Boga 20 July 2021, 21:23

View original

10 replies

Userlevel 6
Badge +4

When setting field values from a parent record, remember to use PXParent to define the relationship so that the child record is deleted if you delete the parent.  Otherwise, you can find yourself with orphaned child records referencing a non-existent parent.

[PXDBDefault(typeof(ParentDAC.recordID))]
[PXParent(
typeof(Select<ParentDAC,
Where<ParentDAC.recordID,
Equal<Current<ThisDAC.recordID>>>>)
)]

Alternatively, you can do referential integrity checks so that you are prevented from deleting the “parent” record if referenced by your custom table.

[PXForeignReference(typeof(Field<MyDAC.customerID>
.IsRelatedTo<BAccount.bAccountID>))]

 

Userlevel 7
Badge +17

Hi @xkylewrightx  Please find the sample to where you get the OrderNbr at code level along with comments. 

	public delegate void PersistDelegate();
[PXOverride]
public void Persist(PersistDelegate del)
{

SOOrder row = Base.Document.Current;
if (row != null)
{
var Ordernbr = row.OrderNbr; // --> by this time Ordernbr will not generate

del(); // --> Internally SAVE will happens to the Sales Order document and OrderNbr will be generated here.

var SONbr = row.OrderNbr; // --> from here you can have retreive the OrderNbr and save it to the Other table

}
}

Userlevel 5
Badge

Edit: Ah never mind, I am calling it //here with Persist(Base.Persist); 

It seems to be working. 

 

 

Hi @Naveen B 

Is there a way to persist the SO from here?

Really appreciate your help!!

 protected void SOOrder_CustomerID_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e, PXFieldUpdated InvokeBaseHandler)
{
if(InvokeBaseHandler != null)
InvokeBaseHandler(cache, e);
var row = (SOOrder)e.Row;
PXTrace.WriteInformation("KRW: CustomerID FieldUpdated Event - Triggering update of WorkflowVars.");

if (row!= null)
{
//here
}

}

 

Userlevel 7
Badge +17

@xkylewrightx  you wanted to write the SAVE action inside the “CustomerID Fieldupdated” event ?

Userlevel 5
Badge

@Naveen B The rows that require OrderNbr are dependent on which customer is selected; I do not need to add these rows until after the customer has been selected so I figured I would do it all in this place.

I have no idea what I am doing, however, so let me know if there could be issues with this :joy:

  public class SOOrderEntry_Extension : PXGraphExtension<SOOrderEntry>
{

string ordernumber = "";

#region PersistDelegate
public delegate void PersistDelegate();
[PXOverride]
public void Persist(PersistDelegate baseMethod)
{
SOOrder row = Base.Document.Current;
if (row != null)
{
baseMethod();
ordernumber = row.OrderNbr;
}
}
#endregion

#region Event Handlers

protected void SOOrder_CustomerID_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e, PXFieldUpdated InvokeBaseHandler)
{
if(InvokeBaseHandler != null)
InvokeBaseHandler(cache, e);
var row = (SOOrder)e.Row;
PXTrace.WriteInformation("KRW: CustomerID FieldUpdated Event - Triggering update of WorkflowVars.");

if (row!= null)
{
Persist(Base.Persist);
PXTrace.WriteInformation(ordernumber);
}

//create some rows in WorkflowVars table

}

#endregion
public PXSelect<WorkflowVars, Where<WorkflowVars.refordernbr, Equal<Current<SOOrder.orderNbr>>>> WorkflowVars;

}

 

Userlevel 7
Badge +17

Hi @xkylewrightx  I don’t think we can write the Persist() in Fieldupdated event, actually it will go to infinite loop, when we update document caches in persist().

I understood that you want to generate a OrderNbr as soon as we select the Customer, but this is NOT recommended code. Can you please let me know your exact requirement?

 

Userlevel 5
Badge

@Naveen B  Any way to do that (access current OrderNbr before the order is manually saved) is acceptable. 

The full workflow is:

  • new sales order created (not manually saved yet)
  • customer selected from pop-up dialog in SOOrderEntry
  • row(s) created in WorkflowVars table based on customer selected and current SO
    • ie: 
      id OrderNbr Customer Customer Preference
      1 000102 Naveen B  
      2 000102 Naveen B  
  • User able to edit CustomerPreference column by editing grid in tab on SOOrderEntry
  • User manually saves SO (and WorkflowVars)
Userlevel 7
Badge +17

HI @xkylewrightx

I understood that you have a different table and you are trying saving the OrderNbr and Customer ID’s to that table by invoking the SAVE action in CustomerID fieldupdated event. (to maintain the foreign key relationship)

I don’t that is required, you just to provide PXDBDefault() for the OrderNbr and CustomerID field as soon as you save the SO, values will be inserted into your custom table. Please find the sample code below.

 

Custom Table DAC fields

  #region SOOrderNbr
        [PXDBString(15, IsUnicode = true)]
        [PXDBDefault(typeof(SOOrder.orderNbr))]
        public virtual string SOOrderNbr { get; set; }
        public abstract class sOOrderNbr : BqlString.Field<sOOrderNbr> { }
        #endregion 

 

  #region SOCustomerID
        [PXDBString(15, IsUnicode = true)]
        [PXDBDefault(typeof(SOOrder.customerID))]
        public virtual string SOCustomerID { get; set; }
        public abstract class sOCustomerID : BqlString.Field<sOCustomerID> { }
        #endregion 

 

 

id OrderNbr Customer Customer Preference
1 000102 Naveen B  
2 000102 Naveen B  

 

Hope this helps!!

Userlevel 5
Badge

@Naveen B Yes I think that this is the right approach, I will give it a shot. Cant thank you enough for all the help. 

Userlevel 7
Badge +17

Great :) let me know if you get any issues..

Reply


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