Help on how to create a form, from which a user enters a date range and filters data from a table

[Luiz Eduh]

Hi, I have the following sql query. I would like to be able to create a form by which a user can select their date range maybe inputting the date in a textbox for startdate and another for enddate. Also give the option to select from b.category an option of 4 categories stored in the table. There are 4 categories that are present on the table; ot, at, ut, and it. I would like to have the option for the user to choose their selection and output the selected data in a report in vfp. Im not familiar with forms but i believe this is the only way that i can have the user run the sql query through a form. Would really like the help on how to accomplis this. The following sql query outputs into data to the cursor crsToExcel where descript = "c" and b.category ="ot"

The highlighted parts are what i want to user to specify as their desire.

Thank you

startdate = Date(2013,09,01)
enddate = Date(2013,11,21)
Select A.Oridate, A.anum, B.price, B.ordqty, B.shipqty, B.Descript, B.category ;
from A inner Join B On A.anum = B.anum ;
where A.Descript = "c" And B.category = "ot" AND ;
A.Oridate ;
between m.startdate And m.enddate ;
into cursor crsToExcel ;
nofilter

 

[Luiz Eduh]

I changed the below code of the sql query to
into table dotit ;
nofilter

to be able to save the table

startdate = Date(2013,09,01)
enddate = Date(2013,11,21)
Select A.Oridate, A.anum, B.price, B.ordqty, B.shipqty, B.Descript, B.category ;
from A inner Join B On A.anum = B.anum ;
where A.Descript = "c" And B.category = "ot" AND ;
A.Oridate ;
between m.startdate And m.enddate ;
into table dotit ;
nofilter

thanks

 

[Olaf Doschke]

Indeed the user can't simply run a query, as you cannot legally provide VFP9.exe to him, the runtime has no command window to simply run queries from.

Also you won't want to give full power to users doing anything. A runtime command window is not a big thing to do as a minimum interface, but users can do harm, they then also can DROP TABLE, do harmful UPDATEs or DELETEs, too. You may go for vRunFox9 from

http://leafe.com/dls/vfp

But in the end a user interface with a form asking for query parameters is a typical solution. It's not at all hard to do. Simply create a form, put textboxes on it, name them accordingly, then you find the user entered values in thisform.textboxname.value. Preset the value of date textboxes at designtime, by putting =DATE() for today and =GOMONTH(DATE(),-1) for last month and users may only enter other valid dates there.

Then in a button to cause the query and excel file generation you do:

LOCAL ldStartdate, ldEnddate
ldStartdate = thisform.startdatetextbox.value
ldEnddate = thisform.enddatetextbox.value
* verify values
If NOT vartype(ldStartdate)+Vartype(ldEnddate)=="DD"
   Messagebox("wrong date entry")
   Return
Endif 
If ldEnddate<ldStartdate
   Messagebox("wrong date range")
   Return
Endif
*... more
Select A.Oridate, A.anum, B.price, B.ordqty, B.shipqty, B.Descript, B.category ;
from A inner Join B On A.anum = B.anum ;
where A.Descript = "c" And B.category = "ot" AND ;
A.Oridate ;
between m.ldStartdate And m.ldEnddate ;
into cursor crsToExcel ;
nofilter

COPY TO (PUTFILE("Enter Orders Excel Sheet Name","orders.xls","XLS")) TYPE XLS

The categories ot, at, ut, and it suggest you have a not normalized table, but that's just a side note. You typically would seperate the categories into a table categories, which could have these short names as it's primary key, but also long names and meaning or other data about them. The short names in your B table would stay as they are and then become foreign keys, a insert/update trigger will only allow valid categories then, that would be the big advantage. More common are integers as keys, but the more important part is the integrity of the data.

That was a bit of database terminology. Database normalisation is the key word and is not just theoretical and optional, but really practically lived and important.

Bye, Olaf.

 

[Luiz Eduh]

Hi Thanks for the suggestions. I will try your suggestion but like i said im not familiar forms, I do want to learn how to use them.

Thanks

 

[Olaf Doschke]

For a starter: Create a Project, if you don't have already Menu: File->New->Project
Chang to the documents tab, Select Forms, click New... And choose New Form (not the Form wizard).
Click on The Toolbar Save or CTRL+S and save the empty form to a file.

To add controls, while you're in the form designer open Tools->Toolbox, open the "VFP Base Classes" and n drag&drop Textboxes from there onto your form. You might also use the Form Controls Toolbar.



Bye, Olaf

 

[Luiz Eduh]

okay it seems that is working now, by adding the textboxes for the dates, it is exporting those date range, not i need to create a combo box or a choice to have the user select the desire category from the category field. Like i mention it, I only have 4 categories in the entire table, ot, at, ut, and it. Could there be a combo box in the form that could export the date range the tran.trantype = "C" but also filter the catergory and export his to excel. How can i add this?

Thanks for your help

 

[Luiz Eduh]

Sorry for asking too many questions, I added a textbox for the category and is working now. Since Im using a table that has important data, is there a way to intead of using the main table use a temp table just in case something goes wrong? maybe create a temp table from the main table and use the temp table when running the form instead of actually using the main table. If so how can this be done?

 

[Luiz Eduh]

Im thinking that it might be better to add a combobox for the category selection intead of a textbox, what needs to be change in the code to have those for categories in the combobox?

Thanks again

 

[Olaf Doschke]

This is a forum, not a chatroom .

I'm on the go, I'll read your questions later, maybe someone else will answer meanwhile.

Bye, Olaf.

 

[Olaf Doschke]

Meanwhile one puzzle piece solved:

A comcbobox can easily display just the four categories by setting it to Rowseroucetype=1, which means simple comma delimited values, and setting Rowsource = "ot,at,ut,it"

I still have not much time. Deadlines.

Bye, Olaf.

 

[Olaf Doschke]

>is there a way to instead of using the main table use a temp table just in case something goes wrong? maybe create a temp table from the main table and use the temp table when running the form instead of actually using the main table. If so how can this be done?

This kind of security concern is unusual. You have backups for security, data is for working on it. It's not a bad idea in itself, but it gets less and less practical over time. If your table grows large, copying it, just to filter and work on some records and update them is not the way to go, but indeed you can query data from the main table by SQL-Select and then have a subset of interesting data to display and work on, and that is even a very usual way to work with large databases, you never pull all data.

You already go in that direction, as you have designed filter criteria for your data. What most people do is display all data and then apply filter criteria. That has advantages and disadvantages. I always give the example of google. Google just asks you for search criteria, even in a very simple way, just one textbox. It's both easier as a developer to have more concrete filter input controls specific to your data and to the user searching specific data. So that#s not the point, but it's obviously useless, if google would list websites, before it even knows what you search. In the past few years we all know, google has changed in how it reacts to search filter input directly. First it suggests autocompletion of your search term and then it also start displaying search results while you type. This is really fine, but a far off goal in your case.

Nevertheless you can easily start with an empty search result list and only fill it, after the user entered timespan and category, then only query and display that data.

You can't prevent table corruptions, even if you copy the whole table, though. You will have a point, where you need to merge your changes to the data copy into the main backend table. That's it's purpose, to store the data persistent, and it's changing a table, writing to it, what can cause data corruptions. Again you have backups, this is not a nice to have but a must have, and then you can do logging of changes, but it's nothing VFP does automatic, it's easier to use SQL Server, it's transaction logging and other features, eg CDC. Not going into detail, because that's far off your task for now. But if you don't trust DBFs then don't use them.

Bye, Olaf.

 

[JRB-Bldr]

Since Im using a table that has important data, is there a way to intead of using the main table use a temp table just in case something goes wrong?

In addition to what has been said above, you can always use a Copy of your 'important' table (SELECT *, .F. AS RecChanged FROM ImportantTable INTO CURSOR FormData READWRITE) in your Form and then write and changes/additions/etc. back to your 'important' table.

If you did this you could change the value of the new 'flag' field (in this case RecChanged) to indicate which records were Changed/Added/Etc. and write only those back to your 'Important' table.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

You don't need that field, you can use GETNEXTMODIFIED() and GETFLDSTATE() to see what changed, you just have to set the cursor buffered. It's not connected to the "important table", so TABLEUPDATE() won't work, but you can emulate a record by record table update with the two aforementioned functions.

The point, that no DBF table is invulnerable, just by working on it this way is still valid. Still users needing to change it need file access to it. In the end, even if your app does not work on the table in a more direct way, users can still work on it in Excel, vie ODBC driver or OLEDB provider, etc. You don't even need to buy VFP to harm a dbf.

Bye, Olaf.