Hi,

can anyone tell me how I can use a refers clause with multiple fields in EasySQL?

For example, what I want to write is something like the following:

where {tdpur413.orno, tdpur413.pono, tdpur413.sqnb} refers to tdpur401

But that syntax gives an error when I try to run it.

Is this possible in EasySQL, and if so how?

Many thanks,

Is there an index on tdpur401? Never done this myself, but found this code:

select tdinv001.stoc:itm.stoc, tdinv001.ordr:itm.ordr,
tdinv001.allo:itm.allo
from tdinv001
where {:tdpur041.cwar,
:tdpur041.item, :tdpur041.cntr} refers to
tdinv001._index1
endselect


But to me this is the same as "table._index1 = {:x,:y,:z}".

In ERP LN EasySQL, you can use combination fields in your where clause.

For example, the Purchase Order Activities table (tdpur413) and the Purchase Order Lines table (tdpur401) both have a combo (index) field defined as cmba and it includes orno, pono and sqnb.

The following EasySQL Query prints all the Purchase Order Activities for Open Purchase Orders ...

select
tdpur401.orno, | Purchase Order
tdpur401.pono, | Position
tdpur401.item, | Item
tdpur413.acti | Activity
from
tdpur400, | Purchase Orders
tdpur401, | Purchase Order Lines
tdpur413 | Purchase Order Activities
where
tdpur413.cmba REFERS TO tdpur401.cmba and
tdpur401.orno REFERS TO tdpur400.orno and
tdpur400.hdst <> tdpur.hdst.closed and
tdpur400.hdst <> tdpur.hdst.cancelled
order by
tdpur401.orno,
tdpur401.pono

Hope this helps.

Mike

Hi Mike,

thanks, but what I am looking for is how to do it when there is not a combined or index field you can use. Maybe the example I gave wasn't a good one....

Let's assume I want to lookup information from cprao020, and I have tcibd001.

What I want to be able to do is something like:

where {"ACT",10,tcibd001.item} refers to cprao020._index4

but can you? And if so how, as the above doesn't work!

(Still not necessarily a logical example I know, but hopefully you get the gist)

Thanks,

Hi Mike,

thanks, but what I am looking for is how to do it when there is not a combined or index field you can use. Maybe the example I gave wasn't a good one....

Let's assume I want to lookup information from cprao020, and I have tcibd001.

What I want to be able to do is something like:

where {"ACT",10,tcibd001.item} refers to cprao020._index4

but can you? And if so how, as the above doesn't work!

(Still not necessarily a logical example I know, but hopefully you get the gist)

Thanks,

The
where {tdpur413.orno, tdpur413.pono, tdpur413.sqnb} refers to tdpur401
and
where {"ACT",10,tcibd001.item} refers to cprao020._index4
will not work in EasySQL.

However, something like this will:

where tdpur413.orno REFERS TO tdpur401.orno and
tdpur413.pono REFERS TO tdpur401.pono and
tdpur413.sqnb REFERS TO tdpur401.sqnb

Hi Mike,

thanks, but what I am looking for is how to do it when there is not a combined or index field you can use. Maybe the example I gave wasn't a good one....

Let's assume I want to lookup information from cprao020, and I have tcibd001.

What I want to be able to do is something like:

where {"ACT",10,tcibd001.item} refers to cprao020._index4

but can you? And if so how, as the above doesn't work!

(Still not necessarily a logical example I know, but hopefully you get the gist)

Thanks,

Why can't you just say cprao020._index4 = {"ACT",10,tcibd001.item}? is there some reason you can't just reverse it - it is the same thing. I must be missing something on this.

Hi Mark,

you need the refers because otherwise you will get no data if there is no match, whereas what you want is blanks (unref clear)

Does that explain?

Hi,

if I try this, I get the error "Reference allowed for index only"

It makes sense now. Of couse then what you can do is the basic part to get the records in the easy sql statements. Then in the report you do the find for additional data using something like a detail layout.

detail.1:
before.layout:
tiitm001.desc = "" | Desc is blank if they send the wrong item to report
select tiitm001.desc
from tiitm001
where tiitm001._index1 = {:some.detail.item}
selectdo
endselect


Do you see what I am saying? Basically you simulate the unref clear piece of it - get basic record in easysql, get addition info (if it exists) in report.