I've created an easy SQL query that has a form with from/to required inputs. Does anybody know how I can retain these from/to inputs so that they can be printed on the header of the report?

This is my first posting, so I hope everything come out alright!

Hi,

I don't think you can, we have tried here but have never got it to work. Seems once the report starts the input fields are lost.

We have re-written all our queries as sessions to do this.


Steve

All you have to do is put the form field names in the "Input Fields" of the report. You then have access to them in the report layouts. Since they are on your form, they are already declared in your script as external variables, so you don't even need access to the code if you don't have it.

Hi,

If you get steveauckly's advice to work could you let me know, our programmer tried that and it did not work for him, would be nice to know if it was a problem with his coding or not.

Steve

Hi,

When i tried adding the input fields in the reports for my form fields, i got an error message "Field Not Defined".

On just declaring the field e.g tcfin100.trdt.f as extern in my report script, i did not get any values which i had enterd at the time of running the query.

Steve would appreciate if you could send us the exact syntax

Regards

Saurabh

my sample SQL:
select
tcfin100.fitr, | Financial Transaction
tcfin100.fprd, | Period
tcfin100.fyer, | Year
tcfin100.item, | Item
tcfin100.nuni, | Number of Units
tcfin100.orno, | Order Number
tcfin100.pono, | Position
tcfin100.sern, | Sequence No.
tcfin100.trdt, | Transaction Date
tcfin100.tror | Transaction Origin
from
tcfin100 | WIP and Inventory Transactions (PCS/SFC)
where
tcfin100.trdt >= tcfin100.trdt.f and
tcfin100.trdt <= tcfin100.trdt.t and
tcfin100.fitr = tcfitr.receipt and
tcfin100.tror = tctror.prd
:confused:

Hi Saurabh,

Have you defined both fields (i.e. tcfin100.trdt.f and tcfin100.trdt.t) as input fields at your report?

Check your defined Input fields...

Hi Frans

When i try to define the field "tcfin100.trdt.f" as an Input field for the report, it comes back with an error "Field Not Found". This i think is because its trying to
find a field called trdt.f
in the table tcfin100


I have already defined the field tcfinn100.trdt as a report field

regards

Saurabh

Hi,

After checking a B50b system I see your point. Apparently the tools check on table fields. Because tcfin100.trdt.f looks like a table field it is not accepted by the tools. That's why you get the blocking error message.
If you rename your form fields to trans.date.f and trans.date.t then after modifying your query and adding them to the report you'll succeed.
I don't know why the tools fire this blocking message instead of just a warning to prevent typo's.

Hi,

You are not allowed to change the fields to any other name aprt from the <field_name>.f and <field_name>.t, as Easy SQL does not recognise it.

If i change the field name i get an SQL execution error "field not found". I guess this is becuase when you use EASY SQL, query by field, and define it to be on the form, it put the two fields on its own.

e;g field tcfin100.trdt -- In Form YES
then the SQL puts

tcfin100.trdt >= tcfin100.trdt.f and
tcfin100.trdt <= tcfin100.trdt.t


Regards

Saurabh

Easy SQL generates form fields given as ranges(ex."tcfin100.trdt.f")
and calculated fields. Just pick up this field name and place it in your report.
Do not attempt to define this generated field anywhere because it
could be internally defined when the form gets generated while running
the query.

I know this is ok because in SQL query one of my fields was
to be by sum(). The output was directed to generated form
variable "float0". I placed "float0" in the report and its working
perfectly. So, just place the generated names in the report
and compile. This must work.

Can I understand from the above that there is no a sloution to get the form fields printed.

Please advice

I have not tried this myself, but here is an idea at a conceptual level that you can try.

Because of the way EasySQL works, it does not pass any form information to the report. So, maybe, the only way to get what you want is in the AFTER.REPORT layout.

- First, make sure the sort sequence in the report is according to what you require for the from and to fields
- Next, in the report, create a script to declare the from and to variables that you want to print.
- Also declare a record count.
- Increment the record count in the detail layout. In the detail layout, if the record count is 1, save the 'From' values from the table fields. This should give you all the 'lowest' values - the 'From' field values on the form.
- in the after.report, save the 'To' values in the before.layout - at this point you are at the last record being printed, so this should represent the 'To' values.
- Assuming you have created the after.report layout and put all the 'From' and 'To' fields in, you should get the output you need.

Note again that the output values of the 'From' and 'To' can change if you change the sort order of the report (I do not think the sort order of the select statement will matter).

Can you please give me the code.

You know, I cant define the form fileds as they defined in the SQL query.

Please advice

Hi Guys,

Newbie for Baan here so bear with me. Im trying to do a EasySql Query and Im tyring to show the specific vaule for a field withint a table. Example

Select

tfcmg100.bank, Bank Relation
tfcmg100.chst Check Status

From

tfcmg100 Check master

Where

tfcmg100.chst = 'PAID'


This is not correct when running the query. Whats the correct syntax for this??

Thanks

GabrielVA

Hello,

Please try this:


Select

tfcmg100.bank, |Bank Relation
tfcmg100.chst |Check Status

From

tfcmg100 |Check master

Where

tfcmg100.chst = tfcmg.chst.paid

select tfcmg100.bank, chst
from tfcmg100
where tfcmg100.chst='paid'

Also, wat is the 'output type' you are choosing in query data session.

Regards,

Yes lorry.lu,

That seemed to work pretty well. If I wanted to see a date past a certian range would it be the following?

tfcmg100.drec.(< 08-01-2010)

??


Thanks

Yes lorry.lu,

That seemed to work pretty well. If I wanted to see a date past a certian range would it be the following?

tfcmg100.drec.(< 08-01-2010)

??


Thanks

Hello,

Select
tfcmg100.bank, |Bank Relation
tfcmg100.chst |Check Status
From
tfcmg100 |Check master
Where
tfcmg100.chst = tfcmg.chst.paid
and
tfcmg100.drec >= tfcmg100.drec.f
and
tfcmg100.drec <= tfcmg100.drec.t

this will open preformatted inputfields where you're able to define
the date range for selection.

Regards

Hello,

In the Easy SQL, add the date range:

tfcmg100.drec >= tfcmg100.drec.f and
tfcmg100.drec <= tfcmg100.drec.t

When you run the SQL, in the selection form, select the date range

or maybe you can date.to.num(yearno, monthno, dayno) in your report scripts:


detail.1:
before.layout:
if tfcmg100.drec < date.to.num(2010,1,8) then
lattr.print = true
else
lattr.print = false
endif

:confused:
hello all...
i have a report script ,in that i declare one table ,i want to print print some records from that table,while printing that i am getting only last record whyy?
and i don't know how to filter out the record using select query in report ,normally i do without select query.please give your answer...........

declaration:
table ttccom907
table ttccom901
extern domain tcccp.wrng id
extern domain tcccp.wrng kk
extern domain tcname p
extern domain tcname k
extern domain tcname m
extern domain tcname n
extern domain tcbod.ccin name
extern domain tcbod.ccin name1
extern long i
extern domain tcbasi posit
extern domain tcbasi posit1
extern domain tcmcs.str50 barco

before.program:
m = "aaaa"
n = "bbb"
p = "ccc"
detail.2:
before.layout:
select * from tccom901
where tccom901.s.no > 1
selectdo
message("%s %s ",tccom901.s.no,tccom901.name)
lattr.print= true
endselect


this was my report script
below my program script
||******************************************************************************
|* tccom9407 0 VRC 107C 0 cust
|* report tool
|* Karthik
|* 2020-10-12
|******************************************************************************
|* Main table tccom907 act2, Form Type 4
|******************************************************************************

|****************************** declaration section ***************************
declaration:

table ttccom907 |* act2

extern domain tcccp.wrng id.f
extern domain tcccp.wrng id.t
extern domain tcccp.wrng id
extern domain tcccp.wrng id1
extern domain tcccp.wrng i
extern domain tcbod.ccin name
extern domain tcbasi posit
extern domain tcbod.ccin name1
extern domain tcbasi posit1
group.1:
init.group:
get.screen.defaults()
|****************************** choice section ********************************
choice.cont.process:
on.choice:
execute(print.data)
choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif
|****************************** field section *********************************
field.id.f:
when.field.changes:
id.t = id.f
|****************************** function section ******************************
functions:
function read.main.table()
{
select tccom907.*
from tccom907
where tccom907._index1 inrange {:id.f}
and {:id.t}
order by tccom907.pos
selectdo
message("%s %s posit1 %s ",i,posit1,tccom907.pos)
if tccom907.pos <> posit1 and i > 1 then
id = 1
export("id",id)
i=2
else
id = 0
export("id",id)
i=2
endif
rprt_send()
posit1 = tccom907.pos
endselect
}





thanks all......................................i attach my code here.

Why are you exporting the ID? I would also check the report - make sure you have a header and a detail layout. Also make sure you have all the fields you plan to print as input fields to the report.

hello all,
(export)that not having any value.......,i insert all the fields which i used in report layout to input fields......i am using select query in report but i am not getting all record,i got only last record.........:confused:

Use layout.again as I have mentioned - here is one thread with http://www.baanboard.com/baanboard/showthread.php?t=60131&highlight=layout.again an example. I am sure there are many more.

rprt_send()
posit1 = tccom907.pos

Just try an exchange the position of these lines and check i.e.

posit1 = tccom907.pos
rprt_send()

hello all,
my problem was solved,in report script ,i put one select query for that query's where clause i compare the value,the value is not coming from report script we need to compare in that way after doing this ,the problem was solved,simply i import the value from report script just compare with that value in select query.........................

thank you allll..................
somebody says ,for this issue ,i may use layout.again() can any one please tell what is the use of layout.again()

http://www.baanboard.com/programmers_manual_baanerp_help_report_scripts_functions_in_report_scripts are report script information attached to baanboard.

layout.again is used to repeat printing a layout. So in our case for example if we had a seeded baan report session that printed items and we wanted to print all the sfc orders for those items. So we could not change the seeded baan script, but we could go into a report, add a new detail layout, add the sfc order number. Then use the report script and could count how many orders for that item in the layout that printed the item numbers(would probably be detail.1 layout). We add detail.2 layout. In the before.layout event we find the first order, check the total counter, if have not printed all the orders use layout.again. This prints detail.2 again - in the before layout we now get the second order, print it. So on and so fourth until we have printed all the orders. Follow the link above that I posted and look at it, the very last post is a solution that worked for them.

Thank you mark_h
such a wonderful explanation,i got it.:)