Get all Fields from current row of Join Table

  • 20 August 2022
  • 3 replies

Userlevel 1

I Have joined 2 tables TSNewQCOrder and TSNewQCOrderSpecializedTest.

I write the view.

public PXSelectJoin<TSNewQCOrder,
             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,
              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?




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

View original

3 replies

Userlevel 7
Badge +18

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



 PXResultset<SOShipment> results =
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

@Naveen Boga Thank you so much for your support.

Userlevel 7
Badge +18

@rajanip50  Most welcome :)


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