Hello,
so my requirement is to recreate tisfc0105m300 but instead of tisfc001.mitm it should display ticst001.sitm. I created a session, added all the fields from tisfc005 I need to the form (cprj - this is view field, cspa, cact and pdno) and ticst001 (sitm and ques). Now because tisfc005 has no references to ticst001 I need to link these two somehow. query.extend functions seem like a perfect tool for this. So I added this code:

before.program:
query.extend.select("ticst001.sitm, ticst001.ques", EXTEND_OVERWRITE)
query.extend.from("ticst001", EXTEND_OVERWRITE)
query.extend.where("ticst001._index1 = tisfc001.pdno", EXTEND_OVERWRITE)

And it doesn't work.
1. ticst001.sitm and ticst001.ques values are joined in some chaotic manner (definitely not by where-clause), there are less rows than should be and when i resize window i see as different values are fetched each time. Here is the resultant query that gets executed:

SELECT tisfc005.*,tisfc001.pdno,tisfc001.osta, ticst001.sitm, ticst001.ques
FROM tisfc005,tisfc001,ticst001
WHERE tisfc005._index2 = { :tisfc005.cprj} AND (
(ticst001._index1 = tisfc001.pdno)) AND
tisfc005.pdno REFERS TO tisfc001
|Based on index:2
ORDER BY tisfc005.cprj,tisfc005.cspa,tisfc005.cact,tisfc005.pdno,tisfc005.effn,tisfc005.dlin

2. Because there is one to many relationship between tisfc005.pdno and ticst001.sitm (ticst001 key is {pdno, pono}) for every row in tisfc005 there are multiple items in ticst001, they all should be displayed. But those are considered as one row, so when I try to mark one of them, the whole group (with identical tisfc005.pdno) gets marked :confused:
3. LN UI filters are absent for ticst001 fields (see attachment)

I tried populating ticst001 fields in read.view: section with pretty much same results. The best picture I can get is by adding this into before.display.object:

before.display.object:
select ticst001.sitm, ticst001.ques
from ticst001
where ticst001._index1 = {:tisfc005.pdno}
selectdo
endselect

but still only one ticst001.sitm is fetched for every tisfc005.pdno.

How this should be done properly? I have spent like two days on this already and I'm out of ideas :(

I am surprised no one shared any thoughts. This problem appears to be like a fairly routine one :)

So I made some progress. I've added sec.add.table("ticst001") call after query.extend.where() and now the join between tisfc005 and ticst001 is carried out properly and filters are there. The only problem left (and it pretty much nullifies my efforts) is that occurrences with identical tisfc005 key fields are treated as one row, so when I mark one of them all get marked.
I concluded that the problem broke down to the fact that tisfc005 can't be used as the main table for my session. So I switched to ticst005 and joined ticst001, tisfc005 with query extensions. Two new problems emerged:
1. I need to have tisfc005.cprj as view field and be able to browse through projects with next/previous view buttons. I can make it as view field but switching projects doesn't work (I guess by default it works only for main table index fields). I've made the needed corrections in the choice.prev.view and choice.next.view sections, and it seems to work but for some reason next.view button gets disabled after first run. I tried adding enable.commands("next.view") into after.choice to no avail :confused:
2. Standard search is only available for main table index fields. Is it possible to enable search by tisfc005.cprj somehow?

Ok, I overkilled it a bit :)

There are cprj, cact and cspa in ticst005, so I don't really need to join tisfc005. I see two possible solutions here:

1. Make a custom table with all the needed fields and add it as a main table to the session.
2. Add a new index on ticst005 with cprj.

I stick to 2 for the time being. What should I bear in mind here? Is altering standard table a valid practice?

Valid practice - I think it is, but what you have to watch for is future service packs or patches that might create an index where yours is. I believe this happed to us early on - a baan sp added an index 2 on a table where we already had one in production. Some of the standard sessions used it after the sp and had some issues with using the one we added. I do not recall us having an issue like but once - the same happened with a table also. You just have to watch it at each sp.