How do I save value in user field added to INItemPlan?

Question

After using a custom "Tag" table to create a Sales Order destined to be fulfilled via PO, I need to attach my custom "Tag ID" to the INItemPlan record for future use in the purchasing process. While I was able to do this successfully in other places, repeating the same methodology is not working now.

In the code, the INItemPlanExt object is retrieved for the INItemPlan object. A value is assigned to the UsrTagID field, and the data can be retrieved from the cache following the Persist.

The code below shows the lookup from the SOLine to the SOLineSplit(s) to the INItemPlan record that is intended to handle purchase via the PO Create graph later. For testing purposes, it sets the value, persists the data, and then retrieves it again. The trace shows that the value appears to be written and retrievable, but the database does not show the value in the UsrTagID field when looking directly into SQL after the fact. I don't see where the code might reset the record in the database as this code is executed as the last call in the "Create SO" action that was added to the menu.

(A) Is there an error in the first section shown in how the UsrTagID field is retrieved and set/saved?

(B) Is there a better way to store the Tag ID on the INItemPlan (database table) record?

(C) Is there something I should look for that might be resetting the data elsewhere? (Although I may have missed something, I didn't find anything unexpected in event handlers.)

Version is: Acumatica 2018R1 Build 18.114.0018

public static void StoreSoTagID(SOOrderEntry graph, int? tagID, string orderType, string orderNbr, int? lineNbr)
{
    PXResultset<SOLine> Results = PXSelectJoin<SOLine,
                                InnerJoin<SOOrder, On<SOOrder.orderNbr, Equal<SOLine.orderNbr>,
                                                    And<SOOrder.orderType, Equal<SOLine.orderType>>>,
                                InnerJoin<SOLineSplit, On<SOLineSplit.orderType, Equal<SOLine.orderType>,
                                                    And<SOLineSplit.orderNbr, Equal<SOLine.orderNbr>,
                                                    And<SOLineSplit.lineNbr, Equal<SOLine.lineNbr>>>>,
                                InnerJoin<INItemPlan, On<INItemPlan.planID, Equal<SOLineSplit.planID>>
                                                      >>>,
                                Where<SOLine.orderType, Equal<Required<SOLine.orderType>>,
                                    And<SOLine.orderNbr, Equal<Required<SOLine.orderNbr>>,
                                    And<SOLine.lineNbr, Equal<Required<SOLine.lineNbr>>>>>>
                                .Select(graph, orderType, orderNbr, lineNbr);

    foreach (PXResult<SOLine, SOOrder, SOLineSplit, INItemPlan> result in Results)
    {
        INItemPlan plan = result;
        INItemPlanExt planExt = PXCache<INItemPlan>.GetExtension<INItemPlanExt>(plan);
        planExt.UsrTagID = tagID;
        graph.Caches[typeof(INItemPlanExt)].Update(planExt);
        graph.Caches[typeof(INItemPlanExt)].Persist(PXDBOperation.Update);
        PXTrace.WriteInformation("Setting: {0} {1}", plan.PlanID, planExt.UsrTagID);
    }

    Results = PXSelectJoin<SOLine,
                InnerJoin<SOOrder, On<SOOrder.orderNbr, Equal<SOLine.orderNbr>,
                                    And<SOOrder.orderType, Equal<SOLine.orderType>>>,
                InnerJoin<SOLineSplit, On<SOLineSplit.orderType, Equal<SOLine.orderType>,
                                    And<SOLineSplit.orderNbr, Equal<SOLine.orderNbr>,
                                    And<SOLineSplit.lineNbr, Equal<SOLine.lineNbr>>>>,
                InnerJoin<INItemPlan, On<INItemPlan.planID, Equal<SOLineSplit.planID>>
                                      >>>,
                Where<SOLine.orderType, Equal<Required<SOLine.orderType>>,
                    And<SOLine.orderNbr, Equal<Required<SOLine.orderNbr>>,
                    And<SOLine.lineNbr, Equal<Required<SOLine.lineNbr>>>>>>
                .Select(graph, orderType, orderNbr, lineNbr);

    foreach (PXResult<SOLine, SOOrder, SOLineSplit, INItemPlan> result in Results)
    {
        INItemPlan plan = result;
        INItemPlanExt planExt = PXCache<INItemPlan>.GetExtension<INItemPlanExt>(plan);

        PXTrace.WriteInformation("Poll DB: {0} {1}", plan.PlanID, planExt.UsrTagID);
    }
}

While I have had a problem in the past where I made the DAC definition PXString instead of PXDBString, I did verifiy the DAC to ensure that I didn't repeat the error this time.

Maybe not your error but I think you should never work with extended cache so replace graph.Caches[typeof(INItemPlanExt)] with graph.Caches[typeof(INItemPlan)] — Hugues Beauséjour
Thanks! While that did not fix the problem, that change still returns the same results without having to reach for the extended cache. I thought I had to reference the DAC where the field was defined. Is "Caches" interacting directly with the database table, thus eliminating the DAC as the middleman? — Brian Stevens
Besides GetExtension and BQL you typically want to avoid referencing extended DAC, especially for events. — Hugues Beauséjour
Caches is the records in memory, it contains collection such as Updated/Inserted... — Hugues Beauséjour
If that's the case you probably want to do same for update too: .Update(plan). Pretty sure that should work too. — Hugues Beauséjour

Brian Stevens Brian Stevens · Accepted Answer · 2019-01-03T22:40:50

The solution is in the comments, so pulling the comments from HB_ACUMATICA out into a solution for others to find easily...

graph.Caches[typeof(INItemPlan)].Update(plan);
graph.Caches[typeof(INItemPlan)].Persist(plan, PXDBOperation.Update);

When working with the cache, do not reference the Ext DAC. Work with the base cache. Acumatica knows how to connect the extended cache data for you. A combination of referencing the plan object in the Persist and also referencing the base DAC instead of the Ext DAC resulted in my data being written to the database table as desired.

How do I save value in user field added to INItemPlan?

1 Answers