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'
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));
}
The problem is I didn't know that I need to use Backorder and ::Sales in select. And since it didn't worked when I just did prodtable.inventrefid == salesline.salesid I was thinking the problem was that salesid was all ZS and in refid ZPR..
@RadosławMierzejewski It's helpful to look at the table relations when trying to create a select statement. See
\Data Dictionary\Tables\ProdTable\Relations\SalesOrder
to see how I knew those were needed in the select. Glad I could help!