You’re offline. This is a read only version of the page.
Announcements
102
I generate an excel file that has data from several tables, the problem is that when I join with InventTrans, duplicate values are displayed. How could I avoid this?
Another problem is when I do the join with PDSAdvancedPriceInfo to retrieve the value from the CalculatedUnitPrice field. When I add the join, no result is displayed.
Share
Report while select purchTable
where purchTable.OrderAccount==_dataContract.parmVendAccount() ||
purchTable.VendGroup==_dataContract.parmVendGroup()
&& purchTable.DeliveryDate >= _dataContract.parmFromDate ()
&& purchTable.DeliveryDate <= _dataContract.parmToDate ()
join logisticsPostalAddress
where purchTable.DeliveryPostalAddress==logisticsPostalAddress.RecId
join purchLine
where purchLine.PurchId==purchTable.PurchId
//join pdsAdvancedPriceInf
//where pdsAdvancedPriceInf.PurchLineRefRecId==purchLine.RecId
join inventDim
where purchLine.InventDimId==inventDim.inventDimId
join inventTrans
where inventTrans.inventDimId==inventDim.inventDimId
{
row ;
if (purchLine.PurchStatus==PurchStatus::Received || purchLine.PurchStatus==PurchStatus::Invoiced)
{
receivedStatus=PI_ReceivedStatus::FullyReceived;
cell=cells.item(row,5);
cell.value(enum2str(receivedStatus));
}
if(purchLine.PurchStatus!=PurchStatus::Received || purchLine.PurchStatus!=PurchStatus::Invoiced && inventTrans.StatusReceipt==StatusReceipt::Ordered)
{
receivedStatus=PI_ReceivedStatus::ToBeReceived;
cell=cells.item(row,5);
cell.value(enum2str(receivedStatus));
}
//cell=cells.item(row,14);
// cell.value(pdsAdvancedPriceInf.CalculatedUnitPrice);
} 
A join allows to link records, not to do a calculation like this. You either need a computed column, or you need another database query for each line to find transactions statuses.
What I need to do is to create the Received Status field that will be populated with: Received status – this will be populated with:
I use InventTrans to verify each transaction
What you should depends on what result you want to get.
I see that you're using InventTrans to check if StatusReceipt == Ordered. But what if there is one transaction Ordered and another in a different state? What do you want to do then?
Hi Pavel Ioana,
You can use group by to avoid duplicate records. Also make use of the view - You can join all the tables to the view - On the view you can add group by and use that view for excel generation.
Thanks,
Girish S.
#1
André Arnaud de Cal...
293,302
Super User 2025 Season 1
#2
Martin Dráb
232,108
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
All responses (
Answers (
