X++ select statement on related tabled

Question

I need help to get data from prodtable that are related to salesline. My task is to get all Prodid that are related to current salesid. Related field prodtable.inventrefid == salesline.salesid. But the values are a bit differend to join so I get no data. Inventrefid have eg. ZPR0000011 and sales id is ZS00000011.

salesline tablebuffer = this.cursor();  
while select ProdId, CollectRefProdId from prodtable where prodtable.inventrefid == 'ZPR00000165'

Answer 1

The main problem I immediately see is:

prodtable.inventrefid == 'ZPR00000165'

inventRefId would be your SalesId, which is ZS00000011 not your ProdId.

An example of a more correct query is below. You can refine it by joining the two selects together so you get all related ProdTable records to all SalesLine records for a given SalesId and you can also specify fields in the queries so that you are not returning the entire buffer.

SalesLine           salesLine;
ProdTable           prodTable;

/*
This just chooses the first sales line with that salesid. You would need to join these together
if you wanted to do all sales lines in one query.
*/
select firstOnly salesLine
    where salesLine.SalesStatus                 == SalesStatus::Backorder       &&
          salesLine.SalesId                     == 'ZS00000011';

while select prodTable
    where prodTable.InventRefTransId            == salesLine.InventTransId      &&
          prodTable.InventRefId                 == salesLine.SalesId            &&
          prodTable.InventRefType               == InventRefType::Sales
{
    info(strFmt("Found related ProdTable record %1 - %2 (%3)", prodTable.ProdId, prodTable.CollectRefProdId, prodTable.RecId));
}