Hi All,

I need to add new column in Warehouse Orders report (whinh2400m000)
I need to join between table whinh210 and tcibd001 to get the item description (tcibd001.dsca) from table tcibd001.

I tried to join between whinh210.item = tcibd001.item,
but the value came out wrongly.
Please refer to attachment for more details.
(From the attachment, can see the report only display column description correctly, but the other column is not right) please help :confused::(

Hi,

Try using :- whinh210._index2 refers to tcibd001

whinh210.item doesn't directly reference tcibd001 - it refers to whwmd400.item, which references tcibd001. You have to identify all three tables in the 'refers to' clause by using 'path':


and whinh210.item refers to tcibd001 path whwmd400.item



This is a way to summarize:


and whinh210.item refers to whwmd400
and whwmd400.item refers to tcibd001

Hi Dave,

I can directly use the following code to fetch data from tcibd001. Never required whwmd400. Anything specific you mean?


select
whinh210.orno, | Order
whinh210.item, | Item
tcibd001.dsca | Description
from
tcibd001, | Items - General
whinh210 | Inbound Order Lines
where
whinh210.orno >= whinh210.orno.f and
whinh210.orno <= whinh210.orno.t and
whinh210._index2 refers to tcibd001

Hi Bushan,

Thank you for your reply
I tried to use you code. but it still come out wrongly. May i know how to declare whinh210.orno.f and whinh210.orno.t? May refer to attachment warehouse orders2.png

Hi,

That piece of code was not for you. Anyway, I can see you want to get the item description in report script. If you are getting the item code from whinh210 from you program script you do not need to add whinh210 in you select loop.

function getdesc()
{
select tcibd001.dsca
from tcibd001
where tcibd001._index1 = :whinh210.item
selectdo
selectempty
tcibd001.dsca = ""
endselect
}

I am assuming you are getting the whinh210.item from your program script.

Hi Bushan,

Thank you for your reply,
Glad the report is okay. But user wants to add 'Order Amount' column in this report.
As i figure out, there is no 'Order Amount' field from table whinh210 and tcibd001.

May i know how to calculate 'Order Amount' in report script? From Warehouse Orders. I can see that (Order Quantity x order price = Order Amount).

Hi,

That depends upon your user. Order amounts are based on Order Types. If its a sales order you can pick it from tdsls401 i.e. pre-shipment and whinh431 after shipment. If its Purchase Order, tdpur401 before receipt or whinh312 after receipt. You should go ahead and ask your user about the logic.