Hi,

I'am trying to create a display session showing data from 5 tables. This needs to be shown as multi occurence form. I have tried using Display.occ and related functions in selectdo part of query but i always end up showing only last row of select.

What is the correct way to program such a display session ?

Hi there... The best way is to select one main table first. (baan uses the principle of having one main table per session). Obviously your joins between the tables is significant - for example your choosen main table may not contain records however you might want to display the records on the other tables anyway. (could be a problem) Now that you have your main table you would call the other tables in your script based on the references defined. The best place to call the other tables is in the field section of the main table.


field.xxxxxxx:
before.display:
select ttpppxxx.*
from ttpppxxx
where ttpppxxx._indexZ =


Regards,
Rupert

I'm having the same problem. Is there anymore feedback about this issue.

More info is needed - Does your main table not contain data and your other tables have data? Or are you just having problems getting the data from the other tables? Not sure what your issue is?

Mark

My main table contains data, and I can see it when I go in debugg mode, its just not going to the form. I have a multi occ form and have the fields set up to be multi occ. Below is some sample code.

Note: Variables are declared in script


zoom.from.all:
on.entry:
import("tdsls420.orno",print.orno)
ignore.first.event = true
execute(find.data)



field.print.orno:
after.display:

select tdsls401.ofbp,
tdsls401.orno,
tdsls401.item:print.item,
tdsls401.oamt:print.total,
tdsls401.damt:print.discnt,
tcibd001.item,
tcibd001.dsca:print.dsca
from tdsls401,
tcibd001
where tdsls401.orno = :print.orno
and tdsls401.item = tcibd001.item
and tdsls401.sqnb = 0
selectdo
display("print.item")
display("print.dsca")
display("print.total")
endselect

Hi

First check if variable "print.orno" is not empty
Second check if records are exists in both table with the same item.
Third, why u written query in after.display?


Mahmood.

print.orno does contain a value, which is the order number, i checked this in debug mode. I'm only using tcibd001 to get the description on the sales item that is selected from tdsls401. Also, I was following an example I saw and that is why I used after.display. So, I don't know why its only printing the last record from the select statement, but I can see all of the data in debug mode.

What's the key table on the form? Your after display section will not add rows to the display, the main table and key you are using should determine the rows. If your key is just orno, it will only display 1 row for each orno, and given your logic display only the last value it had for those other display fields.

Your main table should have a key of Orno / Pono if you want multiple lines matching the tdsls401 table. Maybe you should even be using the tdsls401 table as your main table, it looks like that's so.

See this post by (http://www.baanboard.com/baanboard/showthread.php?t=16672) Hitesh Shah if you are trying to display multiple records without a main table display link. Typically when I write these type sessions my maintable would have order and item on it. Then my query - I usually use before.display would look like this:


select tdsls401.ofbp,
tdsls401.orno,
tdsls401.item:print.item,
tdsls401.oamt:print.total,
tdsls401.damt:print.discnt,
tcibd001.item,
tcibd001.dsca:print.dsca
from tdsls401,
tcibd001
where tdsls401.orno = :maintable.orno
and tdsls401.item = :maintable.item
and tdsls401.sqnb = 0
as set with 1 rows
selectdo
display("print.dsca")
display("print.total")
endselect


Notice that I am using two fields to get the description and total. If item is not available then you need to use the post above - Hitesh lays out how to get mutliple records like this.

On a side note - when posting queries or code you need to disable smiles in the post - keeps all them darn :)'s faces from appearing. It also makes the code easier to read if you use the code tags - check the board help on these.

Good Luck!

Mark

The table that I'm importing the order number from doesn't have an item associated with it. It has a postion number, but it is zero for all orders. What I'm trying to do is when I import the orno number from the table tdsls420, I then look for that order number in table tdsls401, and get all the items that within that order number.

I agree with others that first decision is main table selection. And all rest has to be decided later on.

My earlier post referred to by Mark will not work properly if the there are no records in the main table . Ideally when bringing records from other tables there should be 1-1 or many - 1 correspndence (either with group by clause or without it).

Yes, you are really fighting Baan if your main table does not have the key you want. Key = Rows.

Why not make tdsls401 the main table - it's just a display, right? Try the following:

On the session change the main table to tdsls401
On the form make the Orno / Position from tdsls401

Import the orno as you have done
Make a query extension to limit the tdsls401 orno to what you imported.
See This WIKI topic on Query Extends in Zooms (http://www.baanboard.com/programmers_manual_baanerp_help_functions_sql_query_extensions_query_extend_where) for info, post up if you need help there.

This should get you much closer. You may still need to pull in the description, but that can be done easily through a simliar query as what you have, but just select the tcibd001.dsca, put that on the form.

I would like to thank everyone for their input earlier. This is my last comment, but tdsls401 is the main table in the session. I'm importing the order number from tdsls420. With the order number imported, I then do a search in tdsls401 for that order number. Once it finds the order number, I then want the business partner, item,etc. There are records (the order number) in the main table. For instances, when I run the script I select an order number that I know is in the main table. There are 8 different items associated with this order. When I have the debugger on, I see all the information I need from the select statement and it seems stored in the variable assigned , but when it is finishes the script only the last item associated the order number is displayed. Maybe, I'm still not understanding what everyone is saying, but my logic seems correct.

Your query after the order number is always going to find all 8 items. It will aways do the display statements 8 times. What happens is on the multi-occurance form it will always be on the first line. So the display 8 times will result in only the last item to be displayed. What I picture you having is session that looks like this:

ORDER: tdsls401.orno

ITEM DESCRIPTION
print.item print.dsca
print.item print.dsca

Where item and description are the multi-occurance fields. So the fields(lower case) are fields first by your orno. This triggers the after.display which finds all 8 parts and only displays the last. If your form and fields looked like this(I am not familiar with these tables):

ORDER: tdsls401.orno

PONO ITEM DESCRIPTION
tdsls401.pono print.item print.dsca
tdsls401.pono print.item print.dsca

Then with your query you could do this


field.tdsls401.pono:
before.display
select tdsls401.ofbp,
tdsls401.orno,
tdsls401.item:print.item,
tdsls401.oamt:print.total,
tdsls401.damt:print.discnt,
tcibd001.item,
tcibd001.dsca:print.dsca
from tdsls401,
tcibd001
where tdsls401.orno = :tdsls401.orno
and tdsls401.pono = :tdsls401.pono
and tdsls401.sqnb = 0
as set with 1 rows
selectdo
display("print.dsca")
display("print.total")
endselect


Then as the session displays the positions it can find each of the items. Hope I explained it better.

Mark

O.k., below is my code. If i run it as is it doesn't give me anything, I'm assuming this is because, once I select the order number (print.orno) which is imported. I don't refer to that order number anywhere in the script. I'm somewhat a beginner at this so I appreciate everyone's cooperation. I see where having a main table is important, maybe I'm confused about this

What I'm doing is importing only an order number from another session, table tdsls420 (print.orno). With this order number I then want to get all my data from table tdsls401. So, I assuming that tdsls401 would be consider my main and only table, with the exception of tcibd001, which is used to get the description only. I'm not sure what field.xxx.xxx: does and if some of my syntax is correct/needed, ie.

zoom.from.all:
on.entry:
import("tdsls420.orno",print.orno)
ignore.first.event = true
execute(find.data)

If anyone can tell me where I'm going wrong by looking at my code I would greatly appreciate it!



declaration:

table ttdsls401 | Sales Order Lines
table ttcibd001 | General Item Data

extern domain tcamnt print.total
extern domain tcamnt print.discnt
extern domain tcorno print.orno
extern domain tcpono print.pono
extern domain tcitem print.item
extern domain tcdsca print.dsca


|****************** PROGRAM SECTION*********************
before.program:
fattr.total.line = true
|****************** ZOOM FROM SECTION ********************
zoom.from.all:
on.entry:
import("tdsls420.orno",print.orno)
ignore.first.event = true
execute(find.data)

|******************* FORM SECTION *******************
|******************* CHOICE SECTION *********************
|******************* FIELD SECTION **********************

field.tdsls401.pono:
before.display:

select tdsls401.ofbp,
tdsls401.orno,
tdsls401.item:print.item,
tdsls401.oamt:print.total,
tdsls401.damt:print.discnt,
tcibd001.item,
tcibd001.dsca:print.dsca

from tdsls401,
tcibd001

where tdsls401.orno = :tdsls401.orno
and tdsls401.pono = :tdsls401.pono
and tcibd001.item = :print.item
and tdsls401.sqnb = 0
as set with 1 rows

selectdo
display("print.dsca")
display("print.total")
endselect
|*********************** MAIN TABLE SECTION ****************
|*********************** FUNCTION SECTION ****************

Okay - Let see if we can guide you through this process. What main table do you have on the session? Next what is the start option on the session? What fields do you have on the form? What type of form is it? We do not have these tables on our system. So what fields are on tdsls401

I expect the main table to be tdsls401 for this session. SO lets ignore the tcidb001 table for know. Lets get the session working and doing a find on tdsls401 to start with. Since your main table is tdsls401 we need to first get tdsls401.orno. In Baan IV we have used this method(note for this part of the code I am assuming Baan V has these fields on this table and it works like Baan IV):


zoom.from.all:
on.entry:
tdsls401.orno = tdsls420.orno
execute(find.data)


Then on the form only have something like this:

ORDER: tdsls401.orno

Pono Amount
tdsls401.pono tdsls401.oamt

Lets get this working first before we start throwing in other tables first. Then once you get the concept down then you can modify the forms to what you really want. When compiling use debug until you get comfortable with each step.

Mark

when i do that get no value for either tdsls420 nor tdsls401.

Is tdsls401.orno part of a startup table key (active index) with highest sort order.

Okay - you will need to import tdsls420.orno into tdsls401.orno.

Mark

I would just like to thank everyone for their help, with some additional assistance from Lisa, I'm on the right track now.

Lisa is a good one to learn from. If you run into other problems come on back and launch a new thread.

Good Luck!

Mark