Hi All,

Im writing a query data that need to join table tfacr200 and tfgld106 to get the tfgld106.dcdt.
I have use the key to join the table but got a few data that duplicate in tfgld106.So it will effect the grand total. I want to print only 1 line of data for each transaction.

I also write in the Report Script for 'as set with 1rows' but seem it doesnt work.
Need urgent help in this matter.
Please refer to attachment for the code. please help. thank you :confused:

Hi the easiest way will be to use suppress identical values in the report layout ..this you will find in the conditions tab

Use Distinct Keyword in Select Query to avoid duplicate data

I tried few times for 'select distinct' keyword in report script. but still it come out as duplicate data.

Hi,

You need to study the data in tfacr200 and tfgld106 to get the accurate report. There is no one to one relation between the two tables. For one transaction in tfacr200, there could be number of transactions in tfgld106 depending on your mapping schemes, way of transactions, type of receipts etc. Also, there are multiple debits and credit entries. So, I will prefer to study the two tables first, involve a functional guy into this and then start with your report development logic.

And if you have the logic, then please do some changes to your query.

Remove the selection of tfgld106 table from your query. In your report script just write the selection for tfgld106 table.

No need to use tfacr200 selection as you are sending the values from your query.

select tfgld106.*
from tfgld106
where tfgld106._index1 = {:tfacr200.ninv,:tfacr200.ttyp,:tfacr200.olin}
and tfgld106.dcdt <> 0
selectdo
duedate = tfgld106.dcdt
selectempty
duedate = 0
endselect

Hi Bhusan,

Thanks for your reply,
I've tried using your code. but report script doesn't recognize value from tfacr200.ninv, tfacr200.ttyp and tfacr200.olin.

Does it carry from query data? or i need to declare again in the report script?
thanks.

Hi,

Add the in Report Input Fields.


Reports-> Specific->Report Input Fields

Thank you Bhusan,
It's work for that matter. but once compile. it come out this error. how should i handle this error?

Error SQL: SQLState QP010: Parameter 'tfacr200.ninv' should be of type STRING instead of type INTEGER
Error SQL: SQLState QP010: Parameter 'tfacr200.ttyp' should be of type REAL instead of type STRING

select tfgld106.*
from tfgld106
where tfgld106._index1 = {:tfacr200.ttyp,:tfacr200.ninv,:tfacr200.olin}
and tfgld106.dcdt <> 0
selectdo
duedate = tfgld106.dcdt
selectempty
duedate = 0
endselect

Misplaced the fields. Try this.

Hi Bushan,

Thank you so much. my report work properly. may God bless you. :)

Hi Bushan,

Sorry for keep asking. User claims that a few record does not display the duedate. but most of it is display. may i know how can i change the code?
Still new in developing script

Hi,

If just write :-

lattr.print = false in selectempty.

detail.1:
before.layout:
select tfgld106.*
from tfgld106
where tfgld106._index1 = {:tfacr200.ttyp,:tfacr200.ninv,:tfacr200.olin}
and tfgld106.dcdt <> 0
selectdo
duedate = tfgld106.dcdt
lattr.print = true
selectempty
duedate = 0
lattr.print = false
endselect

Hi Bushan,

Thanks for your reply.
When i use lattr.print = false in selectempty, it will not print all the data in a row.
So the total calculation will be wrong. You may refer to attachment for my result. :confused:

Hi,

If you print the transactions only with the due dates you will get wrong amount. So, let the first solution be like that. The transactions without due date are correct. Let your users figure out why those dont have due dates.