How to implement this SQL statement with WHERE condition

gison

Dears,

I wanna filter out the a group of records which has "Created time < Modified time"
I tried following sentence but couldn't work...

g_recTable.setfilter("Created time",'<%1',"Modified time")

Could anyone give me some suggestion....
thank you....

gison

gison wrote:

Dears,

I wanna filter out the a group of records which has "Created time < Modified time"
I tried following sentence but couldn't work...

g_recTable.setfilter("Created time",'<%1',"Modified time")

Could anyone give me some suggestion....
thank you....

the problem is "Created time" and "Modified time" are in the same record
It seems against Navision's design

kriki

You need to read all records and check that record per record.

Maybe a little trick:

Create a flowfield:
"Created and Modified Time Are the same" of type boolean

Calcformula:Exist("Sales Line" WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(Document No.),Line No.=FIELD(Line No.),Create Time=FIELD(Modify time)))

So basically, the flowfield searches its own record and adds an extra filter : Create Time=FIELD(Modify time)
If the flowfield is TRUE, they are the same. If FALSE, they are different.

Now you can put a filter

SETRANGE("Created and Modified Time Are the same",FALSE);

I presume that the modified time (if different) is always later then the create time.

BTW : I don't know what is best for performance : reading all records and checking 1 by 1 or using this flowfield trick. Probably it depends on your data.

gison

hi kriki,

thanks a lot~~
your solution is great~~