I know most of the BaaN gurus will think this is a very simple request. I have a report whereby I want to split the date into three columns, Year, Month, Day. I need this information to split the table field tdsls040.odat into these three columns.

My current report script is such:
declaration:
extern domain tcmcs.long year
extern domain tcmcs.long month
extern domain tcmcs.long date

detail.1:
before.layout:
date = date.to.num(year, month, date)
month = date.to.num(year, month, date)
year = date.to.num(year, month, date)

The table field tdsls040.odat is being passed by the program already, just not sure if I am suppose to have a select statement inside of the report script in order to use it or how would the above variables be able to identify that they need to obtain the information from the table field?

If anyone has a report script to share which reflects how to do this, this would be greatly appreciated, otherwise if anyone can give me the simple instructions I need to accomplish this task, then that would be great as well.

Thanks in advance.

Hi,

detail.1:
before.layout:
num.to.date(tdsls040.odat, year, month, date)


No need for another select if tdsls040.odat is being passed by the program already.

Regards,

RedBatz

correction:

num.to.date(tdsls040.odat, year, month, day)

RedBatz

Thank you RedBatz,

This code worked perfectly.

Hi,

detail.1:
before.layout:
num.to.date(tdsls040.odat, year, month, date)


No need for another select if tdsls040.odat is being passed by the program already.

Regards,

RedBatz

Ok, so now I have the following script, but the result duplicates the total if there are more than 1 sales order lines, how does one tell BaaN to only reflect one result as opposed to two or more:

declaration:
table ttdsls045
extern domain tcmcs.long year
extern domain tcmcs.long month
extern domain tcmcs.long date
extern domain tcamnt invoice.amnt

detail.1:
before.layout:
num.to.date(tdsls040.odat, year, month, date)

invoice.amnt = 0
select tdsls045.*
from tdsls045
where tdsls045._index1 = {:tdsls040.orno}
and tdsls045.invd > 0
selectdo
invoice.amnt = invoice.amnt + tdsls045.amnt
endselect

I have tried the the sqlcommand "as set with 1 rows" but this then causes the invoice amount to total the last order line and so resulting in an incorrect invoice amount.

Hope someone is able to assist me please?

See if there is a seqn for each line - something like seqn 0 and seqn>0 being something like receipts or invoices. I know for the purchasing receipt table you have to skip a sequence 0. We don't use the sls module, but it might be something like that.

Yes Mark,

There is seqn for Sales Orders as well. Chris you can try what Mark said and add an additional condition in where class to check if seqn <> 0

In LN we have a different table structure so I am not sure which fields exists in your table.

Thanks guys, going to have a look.

Ok, had a look, I cannot find anything of an seqn in either table tdsls040 or tdsls045. I have attached a copy of the two tables, including both records from tdsls045 for one specific order which I am noticing is beiing repeated.You guys might be able to spot the difference that I am missing.

Thank you guys in advance.

Hi Chris,

I guess srnb is the field. Try with srnb > 0 or srnb <> 0.

Actually, when the order lines are created, the sequences are also created for delivery lines which are the part of Sequence 0.

For e.g.

If Order Position(pono) is 1 and it has quantity 50 , there might be chances that user split the line into 2 parts to create 2 sequences i.e.

Sequence 1 :- 25
Sequence 2 :- 25

Hence, sequence 0 will have total quantity i.e. 50 and other two sequences will have 25 respectively.

The problem might be that, you are selecting all the sequences including sequence 0 and hence its repeating.

So, try excluding sequence 0 by adding srnb >0 or srnb <> 0.

Hi Bhushan,

that's not totally correct for BaanIV.

If you will coding tdsls045.srnb <> 0 than based on the attachment by chris nothing will be selected because both delivered and invoiced lines has tdsls045.srnb = 0. How the sequence number will be created depends how the user is handle the order, so it's possible to split-up one oder line into two partial shipments with sequence = 0 (first shipment) and sequence = 1 (second shipment).
Chris is also using tdsls045.invd > 0, so only the real delivered and invoiced lines will be selected.

Chris: What to you want to receive by the select statement, the totals per order or per line?

Regards,
Juergen

Juergen,

Yes. Thanks for correcting me. Again, as I said, in LN we have completely different structure and now I can assume we have a different business process too. So, Chris you can go with the suggestions given by 4c4 experts.

Hi Juergen,

I wanting to accomplish totals per order please.

Thanking you in advance.

Hi Juergen,

I wanting to accomplish totals per order please.

Thanking you in advance.

Hello,

table tdsls045 index 1 is orno,pono,srnb

So use something like :


invoice.amnt = 0
select tdsls045.*
from tdsls045
where tdsls045._index1 inrange {:tdsls040.orno, 0, 0}
and {:tdsls040.orno, 9999, 99}
and tdsls045.invd > 0
selectdo
invoice.amnt = invoice.amnt + tdsls045.amnt
endselect


Regards

invoice.amnt = 0
select tdsls045.*
from tdsls045
where tdsls045._index1 inrange {:tdsls040.orno, 0, 0}
and {:tdsls040.orno, 9999, 99}
and tdsls045.invd > 0
selectdo
invoice.amnt = invoice.amnt + tdsls045.amnt
endselect


Regards

But Bernd that is exactly what was posted in the first thread.

invoice.amnt = 0
select tdsls045.*
from tdsls045
where tdsls045._index1 = {:tdsls040.orno}
and tdsls045.invd > 0
selectdo
invoice.amnt = invoice.amnt + tdsls045.amnt
endselect

The only difference is you made it a range and opened the position and delivery(what it is called on our 4c4 system). The original poster says this was not giving him the right total. I do not know if this table is populated the same as the receipt table tdpur045, but we always use the srnb variable greater than 0. See below.


| This query gives all receipts for the selected order number
| and date range.
select tdpur045.orno, tdpur045.pono, tdpur045.srnb, tdpur045.quap,
tdpur045.dqua, tdpur045.date, tccom020.nama, tdpur041.oqua
from tdpur045, tccom020, tdpur041
where tdpur045.orno inrange :orno.f and :orno.t
and tdpur045.date inrange :date.f and :date.t
and tdpur045.dqua > 0 and tdpur045.srnb > 0
and tdpur045.suno refers to tccom020
and tdpur041._index1 = {tdpur045.orno, tdpur045.pono}


I was wondering if the delivery (tdsls045.srnb) works the same way. The original poster should be able to look at the table in ttaad4500 and see if that is the case.

Hello Mark,

the poster say's:
------------------------------------------------------------------------------------
I have tried the the sqlcommand "as set with 1 rows" but this then causes the invoice amount to total the last order line and so resulting in an incorrect invoice amount.
------------------------------------------------------------------------------------
This could'nt give the right amount for the sales order, because only the first recordset is taken.

I think it's better to summarize in an if condition like:


select ...
from ....
where ...
selectdo
if tdsls045.invd > 0 then
invoice.amnt = invoice.amnt + tdsls045.amnt
endif


Regards