hello,

need some help, i'm trying to combine the quantity for similar item code and display it on a session.

however the group by doens't seems to work properly on the display.

but when i output it as a message the quantity already been sum up.

what is going on? thanks

It seems not a select problem, but a display session problem. In other words, the select and group by are working correctly, but the display of this value seems not to be correct.
Post the part of code that displays the value, could be helpful to determine the problem.

Before.display won't filter the records on screen. You should use another event(before.program is appropriated)

If the the session does NOT has a DAL you can use the following


domain tcqiv1 sum.qhnd
domain tcitem hold.item

main.table.io:
after.read:
if hold.item <> whinr140.item then
select sum(whinr140.qhnd):sum.qhnd
|* The same select clause here **|
as set with 1 rows
selectdo
whinr140.qhnd = sum.qhnd(i)
endselect
else
skip.io("")
endif
hold.item = whinr140.item

U r probably displaying table field 'whinr140.qhnd' and not displaying the variable 'qhnd' . Alternatively write the code in before.display of 'qhnd' .

Before.display won't filter the records on screen. You should use another event(before.program is appropriated)

If the the session does NOT has a DAL you can use the following


domain tcqiv1 sum.qhnd
domain tcitem hold.item

main.table.io:
after.read:
if hold.item <> whinr140.item then
select sum(whinr140.qhnd):sum.qhnd
|* The same select clause here **|
as set with 1 rows
selectdo
whinr140.qhnd = sum.qhnd(i)
endselect
else
skip.io("")
endif
hold.item = whinr140.item


hi ralph, this is a good example however i'm extracting from whinr140, dal script exist so i can't use this. any other ideas?

U r probably displaying table field 'whinr140.qhnd' and not displaying the variable 'qhnd' . Alternatively write the code in before.display of 'qhnd' .


|****************************** program section ********************************

before.program:
if is.mmt.satellite() then
inputfield.invisible("whinr140.cwar")
inputfield.invisible("whinr140.loca")
endif


field.whinr140.item:
before.display:

select whinr140.item, sum(whinr140.qhnd):qhnd
from whinr140
where whinr140.cwar = :whinr140.cwar AND whinr140.loca = :whinr140.loca
and whinr140.item = :whinr140.item
group by whinr140.item
selectdo
|message("%s", whinr140.item)
|message("%d", qhnd)
endselect


output will show qhnd combine the quantity up but the item still repeat itself
as per screen shot, the first inv on hand is ( qhnd ) and the 2nd inv on hand is whinr140.qhnd

question here is how can i only show the item code once.

In this case you can use a DAL Hook


before.get.object()

Syntax:

function function extern long before.get.object (long direction)

Description


Use this to program checks that determine whether reading a record is permitted. The argument is set by the STP or CDAS and specifies the object being read. The possible values are:

DAL_GET_FIRST DAL_GET_NEXT DAL_GET_PREV

DAL_GET_LAST DAL_FIND DAL_GET_CURR

This object hook replaces the before.read subsection of the main.table.io event section in a UI script. If there is a DAL for an object set, this hook is called to perform the necessary checks. Any before.read sections in the UI script are ignored. So, if a UI script contains before.read sections for the main table, you must replace these by before.get.object() hooks in the DAL.
Return value

Returns
The hook returns 0 if reading of the record is permitted. Negative return values (DALHOOKERROR) are ignored by the calling program.



But this may cause you some trouble when using other sessions. Be careful for this.

On the other hand you should make some interaction between DAL and Program Script.

Surely it is not the greatest solution, but have a chance of work =)

You need to define a program variable for the summed quantity and move the summing logic to the before display of the program variable.

for example:


domain tcqiv1 p.item.qhnd.c


field.p.item.qhnd.c:
before.display:
get.total.qhnd()


functions:

function get.total.qhnd()
{
p.item.qhnd.c = 0

select whinr140.item, sum(whinr140.qhnd):p.item.qhnd.c
from whinr140
where whinr140.item = :whinr140.item
group by whinr140.item
selectdo
endselect
}

question here is how can i only show the item code once.

Then code written in after.read section to skip records should work . Other ERP Ln specific code too may work .

actually the sum i can get, but the real problem is filtering to allow the same item appear only once.

select distinct not supported, really a laughing stock.

do you try the before.get.object() on dal?

do you try the before.get.object() on dal?

hi ralph, how i wish also, whinr140 is a standard table, won't be able to access the dal script.

anyhow for the time being i will just have to use the report preview, report can bind the quantity and filter the duplicate records out.