xdialog date range on embedded browse

[netgeorger]

I am trying to arrange a search on an embedded browse on a form. I want the browse to show the records that fall between two dates on the browse
I have a button that initiates an xdialog where the dates can be written or selected in the calendar on the field style.
I then try to use the variables to do a query on the table that the browse is based on. I suppose that this will then be reflected in the browse but
I haven't gotten that far yet.
I am trying to use the code I see in the help files on Between_Date() The code there seems to be pretty clear but when I run their code it gets stuck
on the line that creates the query. I always get an error message that says no such field exists so I guess I haven't declared the variables adequately?
can anyone show me how I can properly get this to work. I need to be able to locate a file on a browse that falls between two dates.
any insight would be greatly appreciated as I have never really figured out how I can use xdialog to do anything outside of the dialog. I can't really see how to get things in and out of a x-dialog. I have looked at the alpha Sports example but it deals with reports and what I am interested in is a browse in a form.
Thanks
below is the code I am trying to use what I get when I run this code is error message.png

'Date Created: 06-Mar-2014 05:35:57 PM
'Last Updated: 13-Mar-2014 07:27:21 AM
'Created By  : Assistant
'Updated By  : Assistant
'Create an XDialog dialog box to prompt for parameters.

DIM Global VFirst_Date as D
DIM Global VLast_Date as D
DIM Global varC_result as C
Dim t as P
Dim i as P
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Search  Records For Dates in a Range",<<%dlg%
{region}
First Date in Range:| [%DATE;P=popup.calendar(dtoc(V_First_date));I=popup.calendar%.40V_First_date!V_First_date_*];
Last Date in Range:| [%DATE;P=popup.calendar(dtoc(VLast_Date));I=popup.calendar%.40VLast_Date!VLast_Date_*];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%,<<%code%
if left(a_dlg_button,13) = "VFirst_Date_" then 
	if a_dlg_button = "VFirst_Date_killfocus" then 
		V_First_date = ctod(dtoc(VFirst_Date))
	end if 
	a_dlg_button = ""
end if 

if left(a_dlg_button,11) = "VLast_Date_" then 
	if a_dlg_button = "VLast_Date_killfocus" then 
		VLast_Date = ctod(dtoc(VLast_Date))
	end if 
	a_dlg_button = ""
end if
%code%)
t=table.open("discovery")
query.filter=between_date("date",VFirst_Date,VLast_Date)
i=t.query_create()

[Stan Mathews]

Re: xdialog date range on embedded browse

query.filter=between_date("date",VFirst_Date,VLast_Date)

The name of the field in the discovery table which you want to query for being between VFirst_Date and VLast_Date is date?

[netgeorger]

Re: xdialog date range on embedded browse

Stan
Wow! Talk about a quick response you caught that.
This is an oversight it should read query.filter=between_date("Document_Date",VFirst_Date,VLast_Date)
"Document_Date is a date field. Correcting this I now get an error message telling me that i=t.query_create() is an incorrect data type.
I am a bit baffeled there must be some other error I have made.
Thanks for your help. Mind you I am taking this more or less right out of the help file which is why I made that oversight.

[Tom Cone Jr]

Re: xdialog date range on embedded browse

First Date in Range:| [%DATE;P=popup.calendar(dtoc(V_First_date));I=popup.calendar%.40V_First_date!V_First_date_*];

VFirst_date

or

V_First_date

???

[netgeorger]

Re: xdialog date range on embedded browse

Tom
Thanks so much! That's obviously sloppy I guess I need code glasses. I re did the dialogue and attached the code to a button Now when I run the code I don't get any error messages but nothing happens to my browse either or my table. I am not really doing anything to order the table. It needs more work. My code doesn't go anywhere that I can tell. I need some other way to get at the browse on my form with this dialog.

'Date Created: 14-Mar-2014 10:49:11 AM
'Last Updated: 14-Mar-2014 10:49:11 AM
'Created By : Assistant
'Updated By : Assistant
''Create an XDialog dialog box to prompt for parameters.
DIM SHARED VFirst_Date as D
DIM SHARED VLast_Date as D
DIM SHARED varC_result as C
Dim SHARED t as P
Dim SHARED i as P
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Show records between a range of dates.",<<%dlg%
{region}
Enter Beginning Date:| [%DATE;P=popup.calendar(dtoc(VFirst_Date));I=popup.calendar%.40VFirst_Date!VFirst_Date_*];
Enter ending Date:| [%DATE;P=popup.calendar(dtoc(VLast_Date));I=popup.calendar%.40VLast_Date!VLast_Date_*];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%,<<%code%
if left(a_dlg_button,12) = "VFirst_Date_" then
if a_dlg_button = "VFirst_Date_killfocus" then
VFirst_Date = ctod(dtoc(VFirst_Date))
end if
a_dlg_button = ""
end if

if left(a_dlg_button,11) = "VLast_Date_" then
if a_dlg_button = "VLast_Date_killfocus" then
VLast_Date = ctod(dtoc(VLast_Date))
end if
a_dlg_button = ""
end if
%code%)
t=table.open("discovery")
query.filter=between_date("Document_Date",VFirst_Date,VLast_Date)
i=t.query_create()

[Stan Mathews]

Re: xdialog date range on embedded browse

t=table.open("discovery")
query.filter=between_date("Document_Date",VFirst_Date,VLast_Date)
i=t.query_create() 
t.close()
if I > 0
browse.viewQueried("asavedbrowsename",query.filter)
'or
'a5_open_default_Browse("buyer",query.filter,"","Temporary Query","",enable_show_all)
end if

[Tom Cone Jr]

Re: xdialog date range on embedded browse

George,

tell us about your form.

based on a set of tables, or just one table?

what's the name of the table supporting the embedded browse you mention? Is it "discovery"?

if your form is set based describe the members of the set and tell us how they're linked.

In its present form, your script, (and stan's) will open a new instance of the discovery table, without changing the range of records displayed in your embedded browse.

[netgeorger]

Re: xdialog date range on embedded browse

Tom
I have just one table called "discovery" the form name is also called "discovery" and the embedded browse based on the table
has an object name of "CONTROL_BROWSE1"
on the table I have a variety of fields for each record one of which is "Document_Date"
so I wanted to get a view of certain records between certain dates.
This is really a common search criteria and I wanted to see how to do this
I started by looking at all kinds of seach functions and dialogs and it seemed reasonable
to try and get it to work from a x-dialog. But I have never worked much with x other than
to modify alfpha's progressive lookup for many other kinds of fields but I didn't seem to get it to work for dates.
I have tried to apply Stans script
I tried both of the methods but I get errors in the code. He gives me two approaches which I have tried
first I tried

t=table.open("discovery")
query.filter=between_date("Document_Date",VFirst_Date,VLast_Date)
i=t.query_create()
t.close()
if I > 0
browse.viewQueried("Documents_Browse",query.filter)
'or
'a5_open_default_Browse("buyer",query.filter,"","Temporary Query","",enable_show_all)
end if

this didn't work because of the "if I>0" clause. the error said something about the variable was of the wrong type so I didn't know where that came from anyway so I got rid of the if clause
I suppose it is some kind of error code.
When I did this and ran the code, a new window popped up with the range of dates in the new browse "Documents_Browse". So great!
at least I am getting something of the answer.
I would have preferred the embedded browse on the form to show the changes but I don't see a function or method to do this.
my form object under properties is named "CONTROL_BROWSE1" the form is also called "Discovery"
anyway at least this gives a browse view of what I am looking for so that is great!
I really appreciate this, thanks so much to you and Stan. I still don't know how to make it reveal this in the embedded form.
However, I think this will work for me if I can't see how to do it.
Here is the code that seems to work.

'Date Created: 14-Mar-2014 10:49:11 AM
'Last Updated: 14-Mar-2014 03:08:08 PM
'Created By : Assistant
'Updated By : Assistant
''Create an XDialog dialog box to prompt for parameters.
DIM SHARED VFirst_Date as D
DIM SHARED VLast_Date as D
DIM SHARED varC_result as C
Dim SHARED t as P
Dim SHARED i as P
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Show records between a range of dates.",<<%dlg%
{region}
Enter Beginning Date:| [%DATE;P=popup.calendar(dtoc(VFirst_Date));I=popup.calendar%.40VFirst_Date!VFirst_Date_*];
Enter ending Date:| [%DATE;P=popup.calendar(dtoc(VLast_Date));I=popup.calendar%.40VLast_Date!VLast_Date_*];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%,<<%code%
if left(a_dlg_button,12) = "VFirst_Date_" then
if a_dlg_button = "VFirst_Date_killfocus" then
VFirst_Date = ctod(dtoc(VFirst_Date))
end if
a_dlg_button = ""
end if

if left(a_dlg_button,11) = "VLast_Date_" then
if a_dlg_button = "VLast_Date_killfocus" then
VLast_Date = ctod(dtoc(VLast_Date))
end if
a_dlg_button = ""
end if
%code%)
t=table.open("discovery")
query.filter=between_date("Document_Date",VFirst_Date,VLast_Date)
i=t.query_create()
t.close()
browse.viewQueried("Documents_Browse",query.filter)
'or
'a5_open_default_Browse("discovery",query.filter,"","Temporary Query","",enable_show_all)

I tried the other method of code but I had a problem with the "enable_show_all"
seems the computer sees this as some kind of variable not found.

[Stan Mathews]

Re: xdialog date range on embedded browse

Sorry, I intended to post

t=table.open("discovery")
query.filter=between_date("Document_Date",VFirst_Date,VLast_Date)
i=t.query_create()
recs = i.records_get()
t.close()
if recs > 0 'only proceed if there are records that fit the query
browse.viewQueried("Documents_Browse",query.filter)
'or
'a5_open_default_Browse("buyer",query.filter,"","Temporary Query","",enable_show_all)
end if


And the enable_show_all is from the documentation but they pass that variable to the function so in this usage it should be

a5_open_default_Browse("buyer",query.filter,"","Temporary Query","",.T.)

or .F. to your preference.

If you want to filter the form you have open, based on the discovery table, you could.

t=table.current() 'don't need to open an open table
query.filter=between_date("Document_Date",VFirst_Date,VLast_Date)
i=t.query_create()
recs = i.records_get()
t.close()
if recs > 0
parentform.queryrun(query.filter)
end if

[netgeorger]

Re: xdialog date range on embedded browse

Stan Wow!
Thanks So Much!
This is working on the embedded browse in my form.

t=table.current() 'don't need to open an open table
query.filter=between_date("Document_Date",VFirst_Date,VLast_Date)
i=t.query_create()
recs = i.records_get()
t.close()
if recs > 0
parentform.queryrun(query.filter)
end if

I had to take out the "t.close" however because it told me it couldn't close the main table for the session or something
now it works exactly the way I imagined thanks so much you made my day! This is a big big help. GREAT!