Hello, i'm new to ERP LN.
Just learn it for about 1 month in scripting.

Right now i have 2 problem.

First i want to convert number to string with zeroinfront of it.
eg. number = 12 String = 0000000012
number = 125 String = 0000000125
All string have same length.

second, i want to do something like select cisli305.* from cisli305 where "year from date field" = 2013 (year)

how can i achieve what i want in LN ?
sory about this. i know i'm noob in LN. Please help me. i'm already search this forum, yet i still didn't find the solution.

Thanks before

In Baan 4/5 you can use sprintf fundtion for example sprintf$("%010d", 125), not sure about LN though.

You can use the same function to extract year from the date and then do the comparison but not directly in the where clause rather inside the select loop using "IF".

First i want to convert number to string with zeroinfront of it.
eg. number = 12 String = 0000000012
number = 125 String = 0000000125
All string have same length.

Do this : -

define a variable
long xyz

xyz=sprintf$("%010d", field.name)
i.e. xyz=sprintf$("%010d", tdsls401.qoor)
OR xyz=sprintf$("%010d", tdsls401.qoor)
OR xyz=sprintf$("%010d", 25)

Then do this:-

define a variable
tcmcs.str10 abc
abc=str$ (field.name)
i.e. abc=str$(xyz)

You got your field in xyz

i want to do something like select cisli305.* from cisli305 where "year from date field" = 2013 (year)

Refer this code:-
Define these variables

long yy,mm,dd,hh,mn,ss, yy1
string temp.yy(4)
utc.to.date(field.name, yy,mm,dd,hh,mn,ss)

e.g. utc.to.date(tdsls400.odat,yy,mm,dd,hh,mn,ss)
OR utc.to.date(utc.num(),yy,mm,dd,hh,mn,ss) | for current date

Now, yy has the year number. i.e. 2012 or 2013 etc.

Regards.

Do this : -

define a variable
long xyz

xyz=sprintf$("%010d", field.name)
i.e. xyz=sprintf$("%010d", tdsls401.qoor)
OR xyz=sprintf$("%010d", tdsls401.qoor)
OR xyz=sprintf$("%010d", 25)

Then do this:-

define a variable
tcmcs.str10 abc
abc=str$ (field.name)
i.e. abc=str$(xyz)

You got your field in xyz



Refer this code:-
Define these variables

long yy,mm,dd,hh,mn,ss, yy1
string temp.yy(4)
utc.to.date(field.name, yy,mm,dd,hh,mn,ss)

e.g. utc.to.date(tdsls400.odat,yy,mm,dd,hh,mn,ss)
OR utc.to.date(utc.num(),yy,mm,dd,hh,mn,ss) | for current date

Now, yy has the year number. i.e. 2012 or 2013 etc.

Regards.

thanks for your reply,
i think i can't use yy in where clause
is there anyway around so i can change my "just year" into two date varibale that have date 1st january and 31th december so i can put it in where clause using inrange.

I'm afraid if i put IF in selectdo, it would impact the performance because a lot of row selected.

Sorry if i'm wrong about this.

thanks before,
JoeleZ

Yes you can't use the yy in your where clause as you will be converting the date field in selectdo itself. As far as I know, using it in your if wont affect your performance much as I use the same in my reports and it is quite simpler to use as well.

If Guru's can give a better solution than this, it will be helpful for us.

For the time being, you can go ahead with this logic.

Regards.

answer to 2nd part...

domain tcdate l.from.date
domain tcdate l.to.date

l.from.date = date.to.utc( 2013, 01, 01, 00, 00, 00 )
l.to.date = date.to.utc( 2013, 12, 31, 23, 59, 59 )

select cisli305.*
from cisli305
where cisli305.date inrange {:l.from.date} and {:l.to.date}

this should solve your problem and more effective than writing if condition in selectdo

please refer to programmer's guide for function usage.

Good answer. That is basically how I do it in 4c4, but without the time piece of it for the utc date. I also let them input the month and year. So I have a little function that returns the starting date and ending date. Well a little more complicated than that, but basically the same routine.

answer to 2nd part...

domain tcdate l.from.date
domain tcdate l.to.date

l.from.date = date.to.utc( 2013, 01, 01, 00, 00, 00 )
l.to.date = date.to.utc( 2013, 12, 31, 23, 59, 59 )

select cisli305.*
from cisli305
where cisli305.date inrange {:l.from.date} and {:l.to.date}

this should solve your problem and more effective than writing if condition in selectdo

please refer to programmer's guide for function usage.


Thanks a lot man......
you help me a lot.......

Best regards,
JoeleZ