How to write this ORDA Query (using Formula)?

I want to write an orda query: to query a date and a time, compared to a parameter:

I have a method that converts date/time to “yyyy-mm-dd hh:mm:ss”:
it’s BB_ISODate(date;time) ->Text

I want to do this using a Formula:

My table has a field called “Date” and a field called “Time”

I’ve tried this
$Fn1:=Formula(BB_ISODate )
$Es1:=ds.MyTable.query(“eval(:1, This.Date, This.Time) < :2”;$Fn1;BB_ISODate (!2019-10-01!;?18:00:00?))

I think I’m way off.
Can someone help me with this?

What I don’t understand, why doing it so complicated?
You could search directly:
$sel:=ds.Table_1.query(“date=:1 and time=:2”;!2019-10-15!;?12:00:00?)

What is the benefit of a formula, what do you want to solve by doing that?

Hi Tony,

For a better understanding:

The query member method can accept a Formula object instead of the queryString.

This Formula object can receive a parameter thanks to the querySettings object.
Those parameters must be put in a args object nested in the querySettings object.
The Formula receives this args object as $1.

The Formula must return a boolean result. It iterates on the entities and can use them as This

Your code could be like this one:

C_OBJECT($es;$settings;$formula)

//querySettings object
$settings:=New object()
$settings.args:=New object(“dateAndTimeAsText”;BB_ISODate(!2019-10-01!;?18:00:00?))

// This Formula object receives args as $1
$formula:=Formula(checkDateTime($1.dateAndTimeAsText))

//The query member method can be given a Formula object instead of a queryString
//The querySettings are given ($settings)
$es:=ds.MyTable.query($formula;$settings)

//checkDateTime method
C_TEXT($1;$dateTime)
C_BOOLEAN($0)

$dateTime:=$1

$0:= (BB_ISODate(This.Date; This.Time) < $dateTime)

Thomas,
I’m looking to do a query of greater than, or less than a date+time, where the date and time are stored in separate fields. So, I need to combine them together for a query.

Marie-Sophie,
What you wrote is helpful.

But I’m still curious: would it be possible to write this query without creating the extra method?
Is there a way to write the formula to call a traditional 4D method, and pass traditional parameters, rather than “This”?

sorry, I totally misunderstood.
Answer already given by Marie-Sophie.

Reminder: your query will run without index and depending of the size of the table might need a huge amount of unneeded text calculations…
If you must have the data stored this way, a query upfront to limit the amount of records by date could have huge speed impact. (given that date has an index).

While I often stored date/time as separated fields before, later I used longints with a calculated stamp (similar to Unix time), now just use 4D’s timestamp command (=string). This makes it very easy (and fast) to query for ranges.

What I think you would need to solve your issue is a kind of virtual field, a calculated field on the fly, with a way to “overwrite” query and order by, to be able to use index, right?
Something to discuss at the 4D Summit Party with a beer or vine in Chicago…

Hi Tony,

You can avoid coding the method checkDateTime by building the Formula object as this:

$formula:=Formula(BB_ISODate(This.Date; This.Time) < $1.dateAndTimeAsText)

About the This object, I’m not sure to understand your question.

You can’t avoid using This in the Formula because it represents the entity you are evaluating to decide wether or not it will be selected by the query.

In the documentation --> https://doc.4d.com/4Dv17R6/4D/17-R6/dataClassquery.305-4311816.en.html

it is said:

formula: a valid formula (see Formulas) passed as Text or Object.
The formula will be evaluated for each processed entity(*) and must return a boolean value.
Within the formula, the entity is available through the This object.

Thanks Thomas.

Reminder: your (function) query will run without index
yes: I will actually be doing a query in selection: so the index won’t be important.

While I often stored date/time as separated fields before, later I used longints with a calculated stamp (similar to Unix time)
Yes: I do similar in many cases.