get single field into global variable

[Marc Reicher]

Hi,

i would like to query my db and get a single field into a global variable. the field is called slip_start_date and the global var is called search_start_date

the general idea is that i have an embedded browse on a form and the user can enter in a date range and then query the db. when the window opens i want to have the earliest and latest available dates already filled into the search fields.

I want to query the db and get the earliest date record by using two fields combined as the key. The fields are slip_start_date and slip_24_hour_time.

These fields are currently not indexed but could easliy be if needed.

thanks in advance for your assistance

marc

[Finian Lennon]

Indexing the field (using the key of date+time) in question would certainly speed things up if the table is large. Also, you should convert the date and time to a string .. something like
cdate(datefield)+padl(ltrim(str(toseconds(Datefield))),6,"0")
which will give you a fixed length index string. Then you can do LQO queries using the "between" function.

Assuming the fields are in the parent of a set or the form is based on the table ... In the on init event for the form you could do something like (inserting your field names) in the code, and adding variables sdate and edate to your form

'===========
t = table.current(1)

qorder = "cdate(datefield) + padl(ltrim(str(toseconds(timefield))),6,"0")" 'matches the index definition

idx = t.order(qorder)
if idx.records_get() = 0 then
ui_msg_box("","There are no records like this.")
end
end if
sdate = t.fetch_first()
edate = t.fetch_last()

topparent:sdate.refresh()
topparent:edate.refresh()
'===========

Finian

[Marc Reicher]

Finian,

thank you for the response to both my posts. i was able to get a calculated field to work with my browse, thank you

in regards to fethcing the first and last date i am still having problems. I modified you code as follows:

----------------

t = table.current()

qorder = "cdate(slip_date) + Slip_24_Hour_Time"

idx = t.order(qorder)
if idx.records_get() = 0 then
ui_msg_box("","There are no records like this.")
end
end if

search_start_date = t.fetch_first()
search_end_date = t.fetch_last()

topparent:search_start_date.refresh()
topparent:search_end_date.refresh()

----------------

slip_24_hour_time is a field in the table; character, length 5 and holds the time like 09:34 or 16:22 etc.

slip_date is a field in the table and is defined as D for date.
search_start_date and search_end_date are global vars DIMed as D.

when i run this script i get an error on the

"search_start_date = t.fetch_first() "

line saying 'variable mismatch'. can you see what my problem is?

Also it is not clear to me how the db will know which field from the first record i want into my variable.

thanks for you continued help

marc

[Al Buchholz]

perhaps this will help..

t.fetch_first()
search_start_date = t.slip_date
t.fetch_last()
search_end_date = t.slip_date

[Marc Reicher]

Thanks Al, that worked nicely!!