Hello,

I need urgent help with following:

I'm trying to build a display session which displays main table containing total turnover figures per BP, parent-BP, area, branche and company.

The table is structured as follows:
index fields:
- record type -> where rctp 1 = BP, 2 = parent, 3 = area etc
- bp
- parent
- area
- branche
- company
- year
- period
- cost -> flag for cost items or others
- free -> flag for sales with 100% discount

where for the first record type all the fields are filled, and for all other record types (>1) only the relevant fields are filled and the rest is empty [for rctp 2 parent, year, period, cost, free are filled. for rctp 3 area, year, period, cost, free etc].

In the data:
- the turnover/discount/costs amounts.

The display session has to function as follows:

- 5 forms, showing the data per record type. So Form 1 has view field per bp, form 2 per parent, form 3 per area etc.
- by default the data is shown CUMULATED PER YEAR, excluding cost and excluding free. So, per year there has to be ONE RECORD with the total figures for (all periods of) that year. The user has to be able to select a range of periods (for instance 1->3), and then the records will be per year the total of the selected periods (1->3). But I need ONE RECORD PER YEAR.
- The user should also be able to select including cost and/or including free.

This is the first tme I am creating a display session, and I noticed that what I'm trying is rather unconventional within Baan (didn't find any standard Baan session that works similar). I am encountering major dificulties with several issues in this regard.

My main issues right now are:
1) how do I manage to display only one record per year (although the period are indexed field), but with a total of all periods within that year (I.e. the program has to read 12 records, but display only one with the total figures).

2) I would like to get the user range selection fields in my "find" window -> through clicking the spy-glass. So when user clicks the spy-glass he should get to select
- the field corresponding with the current form (bp, parent area etc) from -> to
- period from -> to
- including cost -> yes/no
- including free -> yes/no
How do I arrange that?

3) The highest level is the totals per BP. So I want when tabbing from the first form [per BP] to the other forms, I want after fetching the relevant records for that form (the corresponding rec. type) the pointer on the current record in the first form. In other words, when before leaving the first form the current view is BP 12345 who's parent is 66666 and area is 900, then when tabbing to the second form, I want the session to start with parent 66666 and when tabbing to the third form pointer should be at area 900. Also when then tabbing back to first form, the pointer has te be again at 12345. How do I do this???

FYI: The fetching of my relevant records per form -> record type, I did by using query.extend.where() in the before.form sections, every time selecting the relevant record type.


I know this is rather much, but I have been puzzling on this for quite a few days already now (!!!) and I don't get it right. Any help advice would be HIGHLY APPRECIATED!!


Looking forward to your responses,

En.

P.s. I have posted a thread yesterday also regarding part of this issue, but decided to re-post this here with better and more detailed information...

En -
I see nobody is biting. This type of thing is difficult to do. The standard program which runs the sessions in Baan is based on a main table. You can accomplish some of this with the query extensions that were on the other threads. But to get summaries only, that is not so easy.

You can calculate summaries fairly easily in the script, and display them as a field, but you can't use the summary records to 'drive' the session. You need at least 1 index on the main table that has the 1 to 1 view of the records you want.

Usually people opt for a report in this case.

Another option that fits with what you want is to get the user input, then build a temporary table based on that input and let them run around in that. Since you are in Baan V, you can see this is how they do it in 'Time Phase Inventory Planning' (cprrp0520m000). What they do there is get the range selection from the user, then fill the 'dummy table' cprmp010 to run the view you see in the session. The dummy table holds only 1 record per period, giving the 1 to 1 view they need to drive this session.

You will still need the query extensions to get the forms to filter how you want. From your last post, not sure what went wrong on when you came to look up again. Try find.data instead:

before query rebuild stuff:
save.orno = tdsls041.orno
save.pono = tdsls041.pono

then, after the query rebuild stuff,
tdsls041.orno = save.orno
tdsls041.pono = save.pono
execute(find.data)

Let us know :)

After lots of puzzling, I found a way to do this! I now get one record per year with the total turnover and discout amounts for all periods of that year! I did it in the main.table.io section, using select on main table alias.

This is what my script looks like:

main.table.io:
read.view:
save.year = tdrom991.year.c
first.period.brom = tdrom991.brom.c
first.period.disc = tdrom991.disc.c

after.read:
if tdrom991.year.c <> save.year then
first.period.brom = tdrom991.brom.c
first.period.disc = tdrom991.disc.c
save.year = tdrom991.year.c
endif

select alias991.rctp.c, alias991.ofbp.c, alias991.ncmp.c, alias991.itbp.c,
alias991.creg.c, alias991.cbrn.c, alias991.cofc.c, alias991.year.c,
alias991.perd.c, alias991.free.c, alias991.cost.c, alias991.brom.c,
alias991.disc.c
from tdrom991 alias991
where alias991._index1 = {:tdrom991.rctp.c, :tdrom991.ofbp.c, :tdrom991.ncmp.c,
:tdrom991.itbp.c, :tdrom991.creg.c, :tdrom991.cbrn.c,
:tdrom991.cofc.c, :tdrom991.year.c}
and alias991.perd.c > :tdrom991.perd.c
and alias991.cost.c = :tdrom991.cost.c
and alias991.free.c = :tdrom991.free.c
as set with 1 rows
selectdo
year.brom = year.brom + tdrom991.brom.c
year.disc = year.disc + tdrom991.disc.c
skip.io("")
selectempty
tdrom991.brom.c = year.brom + first.period.brom
tdrom991.disc.c = year.disc + first.period.disc
display.curr.occ()
year.brom = 0
year.disc = 0
first.period.brom = 0
first.period.disc = 0
endselect

For the other two points I still do not have the solutions, so any suggestions or pieced of advice would be very welcome and appreciated!!

Hope this is useful for some of you, since I heard from whoever I asked so far that it wouldn't be possible to work out something like I did. Well, aparently it IS possible... :D

En.

Well, aparently it IS possible... Everything is possible if you are determined enough!

Good luck on the other issues. I would seriously consider a temporary table... it will give you better control, and easier calculations, since you can fill the table with cumulated data.

I would seriously consider a temporary table...


Would you prefer it even on the way I solved it now? I have no previous experience with these types of display sessions, so please explain: what according to your opinion could go wrong or cause problems in the way I currently programmed it??

It won't necessarily cause problems or go wrong, but why make your life difficult when there are easier options? Just consider the time you spent finding this first solution! Like Lisa mentioned:Usually people opt for a report in this case. If you started with this, you would probably already know the calculation logic to achieve the cumulated figures. It would then have been easy to fill a temporary table, and displaying would be much easier...

So yes, I would prefer the use of a temporary table over the way you programmed it now. But that's my personal opinion, and many others might advise differently.

En,

I saw your query in the after.read of main.table.io, I have few things to say....

It is generally not advisable to write a query on a table that fetches records from the same underlying main table..
However you have used an Alias...(in this way it may not be problem), but in future, you can avoid using such queries in any of the main.table.io sub-sections. It might cause some undesired results...

(Gurus...do you agree with me ? or do you have any better suggestions ?!)

I agree with NvanBeest, you can either use a temporary table or an internal dynamic memory array to accumulate the values and later display the total values.

All the Best on your efforts..!!

Warans

I know that on report level this is all easy, but what can I do, this functionality had to be present for my display session...

I personally prefer this on writing into a temp record. I would use a temp record only if I had no other choice. I do however appreciate and validate your opinion.

Have you (or anyone else out there) got any idea how to help me out with the other two issues? Namely:
1) How and where do I enable the user selection range on the form. The user needs to be able to select
- per record type a range of that type (form 1: bp, form 2: parent, form 3: area etc)
- period from -> to
- cost included yes/no
- free included yes/no
Is it possible to make these fields available in the find dialog when clicking the spy-glass??

2) When tabbing from the first form to other forms, I want - after fetching the relevant records for the form - the pointer to point at the record corresponding with the record that was last pointed in the first form. I.e. on the first form my current view was BP 12345, its parent is 66666 and its area is 900, I want when tabbing to the second form (per parent bp) the pointer the first view to be parent 66666, and ont he third form area 900 etc. Also when tabbing back to the first form the pointer should be again at that same last view.

So I assume I save the current occurence in the after.form of form 1, but then how do I get the pointer to display those records first???


Anyone, please help!!


Thanks a mill!

En.

Have you (or anyone else out there) got any idea how to help me out with the other two issues? Namely:
1) How and where do I enable the user selection range on the form. The user needs to be able to select
- per record type a range of that type (form 1: bp, form 2: parent, form 3: area etc)
- period from -> to
- cost included yes/no
- free included yes/no
Is it possible to make these fields available in the find dialog when clicking the spy-glass??

2) When tabbing from the first form to other forms, I want - after fetching the relevant records for the form - the pointer to point at the record corresponding with the record that was last pointed in the first form. I.e. on the first form my current view was BP 12345, its parent is 66666 and its area is 900, I want when tabbing to the second form (per parent bp) the pointer the first view to be parent 66666, and ont he third form area 900 etc. Also when tabbing back to the first form the pointer should be again at that same last view.

So I assume I save the current occurence in the after.form of form 1, but then how do I get the pointer to display those records first???


Anyone, please help!!


Thanks a mill!

En.

For the first question (and using BaanIV) this is what I would do:

Declare the necessary range variables as external
Add a "Filter" button to all the forms
Let this button zoom to a new subsession with the required input fields, with zoom.to$("subsession", Z.SESSION, str$(form.curr), "", 0)
In the zoom session, you would need zoom.from.<form> sections for setting the valid ranges for the record type
Have an "OK" button in the subsession, activating a "cont.process"
In this section, export the selection fields to the external variables in the parent session, and execute an "end.program"
Back in the main session, rebuild the query extension with the variables, and refresh the records


For the second question, have you tried the suggestion posted by Lisa? before query rebuild stuff:
save.orno = tdsls041.orno
save.pono = tdsls041.pono

then, after the query rebuild stuff,
tdsls041.orno = save.orno
tdsls041.pono = save.pono
execute(find.data)

as far as i understand you want to search through different fields via spy glass (standard command) depending on what form your position is.

if you put some more indices (including the fields you want to search for) to your main table then you should be able to change the index via change.order when tabbing through the forms.
After that the spy glass should start the search window on the correct index.

Never tried this but it should work.

Greetings
Andreas

Thanks Nico for your response!!

Let me try to understand something: how does the def.find of Baan works. When I hit the spy-glass or ^f to search for a record, based on what exactly are fields yes or not appearing for selection in the find-dialog??

I noticed that for the table (index??) fields it is simply as follows: if the field is set as input, it will appear in the find dialog; as display not. But what with my form fields? [period.f - period.t, including free, incl. cost] How do I get them in there? ?

Thanks Andreas for your reply too; only saw it after I posted my previous post...

I want to offer a RANGE selection of index fields. In other words:
Period form -> to , where period is an index field of the main table.
Including Free, where free (yes/no) is an index field
Including cost, where cost (yes/no) is an index field.

Is there a way to do that directly through the spyglass or at least on the same form, without having to use a seperate session for it??

def.find only works for the main table's index fields.

So, to specify the ranges, either use the option I outlined above, or add a new first form where the user can specify the ranges, and then switch to one of the other forms. The only problem with this second approach is that you will not (easily) be able to set the valid ranges for record type based on the form where you come from. It is possible, by saving the form number in the after.form, and in the before.form of the selection form use this saved number. But, you cannot control to which form the user jumps after filling the ranges. With the zoom option you always return to the same form (provided that the zoom session is declared as a modal child)

So are there no examples whatsoever where ranges of index-fields are being selected in the find dialog???

Sorry for bugging about it; just this is a surprise for me... :eek:

If really this is the case (please re-confirm), then I will indeed try to do it via a zooming session, which will only be a selection-form...

Have never been able to specify ranges in the Find dialog. You could try to use the Query-by-form option, with a "between" choice on the field you want to filter.

Maybe in BaanERP ranges in the Find dialog are possible, although I doubt it, but in BaanIV and lower definitely not!

sorry for my misunderstanding of your needs.

I agree with Nico that there is no possibility to get a 'range find'
with the Standard comand which is only intended to get the pointer to one specific record.

Good Luck
Andreas

Ok Nico and others, here I am again. I decided to opt for your suggestion regarding the range selection in new subsession via botton.

Can you please help me a bit further? I now need to pass on the right values to the subsession for the range selection. Depending which form I come from, I want - if this is possible - the first field in the subsession to be different, to different zooming, and different label. In other words, when coming from form 1 in the main session, I want to see in the subsession:

from to
BP
period
free
cost
where when zooming from field BP.f/BP.t I will zoom to the BP's session (tccom4500m000)

If coming from third form in main session, i want the subsession to look like this:
from to
Area
period
free
cost
Zooming from field Area.f/Area.t will take me to the Areas session (tcmcs0145m000). Etc for all forms.

Is this possible? Can anyone please explain to me - with script examples - how to do this and in which sections etc?

Thanks so much in advance!

En.

Hi En,
thought a little bit about your problem.
First, yes it is possible to code what you wan't.
Labelproblem: there is a function in Baan called change.field.label(...) but this is only valid for dynamic Forms and only for three different Labels so i don't think this should be your choice.
I assume to use a field on the form which you can fill depending on the form (give a value or so) of the zooming session 'which can be used as a label'.
Second problem with the different fields:
You can define 5 (i think there were 5 different forms ) or 10 (from/to) different fields which you can make invisible or visible depending where you zoom from. Functions are inputfield.visible(...) and inputfield.invisible(...).
Since you are on 5b there should be another possibility.
You can define a new segmented domain which involves your 5 different fields. then you have access to the different (in your case 5)segments which can be manipulated independently.(with enable.fields(..), disable.fields(...).
In the definition of the segmented domain you can define what session to zoom to and which field to be returned for every segment.

Hope this helps
Andreas

Andreas, thanks so much for your response!!

The problem of the different labels I have solved indeed the way you described. Looks like this:

zoom.from.1:
on.entry:
label = "Sales-to BP"
zoom.from.2:
on.entry:
label = "Invoice-to BP"


Where the zoom.from numbers are the form numbers zoomed from in the main sessions.

Now for the form field: I started playing around with it as well (experimenting, because I don't know what the "official" way is to solve it). This is what I did so far:

- rel.field.f is the field which has to get the relevant from/to per record type.

field.rel.field.f:
before.field:
if from.form = 1 then
attr.type = DB.STRING
attr.zoomcode = Z.SESSION
endif

before.zoom:
if from.form = 1 then
attr.zoomreturn$ = "tccom110.ofbp"
attr.zoomsession$ = "tccom4500m000"
endif

after.zoom:
if from.form = 1 then
import("tccom110.ofbp", rel.field.f)
endif


So the field indeed zooms to the correct session. But the field doesn't get filled with the BP when returning from zoom. It stays empty. When debugging, I saw that the field tccom110.ofbp stays empty, and doesn't get a value (the BP I selected in tccom4500m000). What am I missing??

First there's a problem in your definition:
you have to set attr.zoomreturn$ to tccom100.bpid because tccom4500m000 depends to Business Partner Table tccom 100.
Then you don't have to import tccom100.bpid because after.zoom the variable is not in memory anymore and you overwrite your returnfield with ""
so try the following

field.rel.field.f:
before.field:
if from.form = 1 then
attr.type = DB.STRING
attr.zoomcode = Z.SESSION
endif

before.zoom:
if from.form = 1 then
attr.zoomreturn$ = "tccom100.bpid"
attr.zoomsession$ = "tccom4500m000"
endif

--------------------------------------------------------------------------------

Oops!! You're absolutely right. By mistake I wrote the returnfield tccom110.ofbp instead of tccom100.bpid. The import I added only after I saw it wasn't working in an attempt to get it working...

Thanks so much!! Till the next thing I get stuck with.... ;)
Have a wonderful day!!

En.