Solved

Get all Fields from current row of Join Table

  • 20 August 2022
  • 3 replies
  • 460 views

Userlevel 1
Badge

I Have joined 2 tables TSNewQCOrder and TSNewQCOrderSpecializedTest.

I write the view.

public PXSelectJoin<TSNewQCOrder,
         InnerJoin<TSNewQCOrderSpecializedTest,
             On<TSNewQCOrderSpecializedTest.qCOrderType, Equal<TSNewQCOrder.qCOrderType>,
                  And<TSNewQCOrder.qCOrderNbr, Equal<TSNewQCOrderSpecializedTest.qCOrderNbr>>>>,
         Where<TSNewQCOrder.qCOrderNbr, IsNotNull>, OrderBy<Desc<TSNewQCOrder.createdDateTime>>> TSSOShipmentQualityOrderView;
 

 protected virtual IEnumerable tSSOShipmentQualityOrderView()
        {
            SOShipment rec = Base.Document.Current; 
            PXSelectBase<TSNewQCOrder> cmd = new PXSelectJoin<TSNewQCOrder, InnerJoin<TSNewQCOrderSpecializedTest,
                On<TSNewQCOrderSpecializedTest.qCOrderType,
              Equal<TSNewQCOrder.qCOrderType>, And<TSNewQCOrder.qCOrderNbr, Equal<TSNewQCOrderSpecializedTest.qCOrderNbr>>>>,
                      Where<TSNewQCOrder.refDoc, Equal<Required<TSNewQCOrder.refDoc>>,
                          And<TSNewQCOrder.docNbr, Equal<Required<TSNewQCOrder.docNbr>>>>, OrderBy<Asc<TSNewQCOrder.createdDateTime>>>(Base);
            if (rec != null)
            {
                if (rec.ShipmentNbr != null)
                {
                    foreach (PXResult<TSNewQCOrder, TSNewQCOrderSpecializedTest> line in cmd.Select("E", rec.ShipmentNbr))
                    {
                        TSNewQCOrder row = line;
                        TSNewQCOrderSpecializedTest row1 = line;
                        yield return new PXResult<TSNewQCOrder, TSNewQCOrderSpecializedTest>(row, row1);
                    }
                }
            }
        }

 

On this (View Quality Order)  Action button all rows are coming in popup. 

when I am trying to get the current row, I am getting only 1 table (TSNewQCOrder) fields. I did not get fields from this TSNewQCOrderSpecializedTest table.

How i can get all the fields from both tables?

 

 

icon

Best answer by Naveen Boga 21 August 2022, 12:20

View original

3 replies

Userlevel 7
Badge +17

Hi @rajanip50  If you wanted to fetch the records from the 2nd DAC then you need to use the PXResultSet like below.

Link: https://stackoverflow.com/questions/53070800/what-is-the-proper-way-to-update-values-of-dacs-retrieved-via-pxresultset

 

 PXResultset<SOShipment> results =
PXSelectJoin<SOShipment,
InnerJoin <SOShipLine, On<SOShipLine.shipmentNbr, Equal<SOShipment.shipmentNbr>>,
InnerJoin <SOLine, On<SOLine.orderType, Equal<SOShipLine.origOrderType>,
And<SOLine.orderNbr, Equal<SOShipLine.origOrderNbr>, And<SOLine.lineNbr, Equal<SOShipLine.origLineNbr>>>>
>>,
Where<SOShipment.shipmentNbr, Equal<Required<SOShipment.shipmentNbr>>>>
.Select(docgraph, docgraph.Document.Current.ShipmentNbr);

PXTrace.WriteError("Shipment {0} - Records {1}", docgraph.Document.Current.ShipmentNbr, results.Count);

foreach (PXResult<SOShipment, SOShipLine, SOLine> record in results)
{
SOShipment shipment = (SOShipment)record;
SOShipLine shipmentLine = (SOShipLine)record;
SOLine sOLine = (SOLine)record;
==============================================
shipmentLine.UnitCost = GetReturnUnitCost(sOLine.OrigOrderType, sOLine.OrigOrderNbr, sOLine.OrigLineNbr, sOLine.CuryInfoID);
shipmentLine.ExtCost = shipmentLine.Qty * shipmentLine.UnitCost;
PXTrace.WriteError(string.Format("{0} {1}-{2} = {3} / {4}", shipmentLine.LineType, shipmentLine.ShipmentNbr, shipmentLine.LineNbr, shipmentLine.Qty, shipmentLine.UnitCost));
==============================================
}

 

Userlevel 1
Badge

@Naveen Boga Thank you so much for your support.

Userlevel 7
Badge +17

@rajanip50  Most welcome :)

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