Hi,

i created query to print list of lot no. that have same first 9 digit with lot no that link with certificate no. entered by user.
User will insert certificate no. in query form.
In report, system is required to print out any lot no. and item that have same first 9 digit lot no.

For example:
user insert Certificate no: B-11
then in system, the cert no. link to lot no. 2312-0516-001
so system will print;
2312-0516-005
2312-0516-010
2312-0516-011

i hv put a script in the report, but not print at all.

declaration:

extern domain tcmcs.st10m a



before.program:
a = whltc100.clot (1;9)

detail.1:
before.layout:
if whltc100.clot (1;9) = a then
lattr.print = true
else
lattr.print = false
endif

Have you put it in debug mode and stepped thru it to see what might be happening? Are 1-9 really equal - I have seen people confuse a 0 and upper case o when typing. I also notice that a is length of 10 - maybe just make it 9. Nothing else really jumped out at me when reading this.

As Mark already mentioned try to change domain declatration to a length of 9
e.g tcmcs.str9
or
change if statement to
if whltc100.clot (1;9) = a(1;9) then

Regards,
Juergen

if i not write the script, system only print out lot no. that has the same certificate no.

however, i would like to have report that will print all lot no. that have same 9first digit with lot no. that link to certificate no.

i'm not sure if i need to assign the variable at query or at report.
do you have any idea?

You should be able to do it either way. You can assign the value in the session for the first 9 and pass it to the report or assign it in the script. Both ways should work. If you did it in the session script you could actually use the layout print expression to test the value or use it in the report script. I have done it several ways to filter data into a report.

In your case I would put it in debug mode and check what the variable "a" was being set to when the report first runs. Also run the report at least twice consecutively to make sure "a" gets set to a new value. Then step thru and make sure the if statement is also working. If you know some lots are not being printed run them one at a time to see why. I think the method you selected is fine you just need to step thru it a few times to see where the issue might be - data or coding.

Hi,

My report is still print out lot no that link to the certificate no only.
It doesn't print any other lots that have same first 9 number.
anyone has idea on this?
tq.

Hi,

i'm now trying the other way around.
In Query user will insert certificate number.

select
whltc100.cert, | Certificate Number
whltc100.clot, | Lot
whltc100.item, | Item
whltc100.orno, | Order
whltc100.ldat | Lot Date
from
whinr110, | Inventory Transactions by Item and Warehouse
whltc100 | Lots
where
whltc100.cert >= whltc100.cert.f and
whltc100.cert <= whltc100.cert.t


In report, sys will print all lot no that have same first 9digit with lot no that link to the certificate no.
below is the report script.

declaration:

table twhltc100

extern domain tcclot whinr110.clot
domain tcclot whltc100.clot
domain tcclot lot.no
domain tcmcs.st10m lot.ref


before.program:
lot.ref = whltc100.clot(1;9)

detail.1:
before.layout:
select whinr110.*
from whinr110
where whinr110.clot(1;9) = lot.ref |whltc100.clot(1;9)
selectdo
lot.no=whinr110.clot
selectempty
lot.no=""
endselect


but i got error unknown column "lot.ref"

may i know where is my mistake?

please use
where whinr110.clot(1;9) = :lot.ref

Hi,

It might fail as well.

You might try using

where whinr110.clot(1;9) = {:lot.ref(1;9)}

But, it might fail as well. Looking at you code, it seems you are trying to print all the lots from table whinr110. But, your code will just read the records and will print only one lot. You will need to call layout.again() or r.s.after.detail.1 (http://www.baanboard.com/baanboard/showthread.php?t=27794)() in order to print all the lots which related to the certificate number coming from whltc100.

Also, you will need to use a sorted field i.e. before.field layout on your report to avoid printing duplicate lots.

Instead, what you can do is, simly put your whinr110 and whltc100 in your main query and print both certificate number and lot number on your report.

User can sort the data as per their requirement on excel.

e.g.

select
whltc100.cert, | Certificate Number
whinr110.clot, | Lot
whltc100.item, | Item
whltc100.orno, | Order
whltc100.ldat | Lot Date
from
whinr110, | Inventory Transactions by Item and Warehouse
whltc100 | Lots
where
whinr110.clot >= whinr110.clot.f and
whinr110.clot <= whinr110.clot.t and
whinr110.clot refers to whltc100
whltc100.cert <> ""

Hi Bhushan,

At first, i hv tried to persuade my users to sort out from Excel and just download from query the lot and cert no.
But they rejected and would like to have a simplest method to track the lot no based on cert no.

So that's the reason i hv to create this query :(