| Subject | Re: oodml coversion |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Tue, 24 May 2016 16:23:36 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | test_email.wfm |
On 23/05/2016 21:54, Charlie wrote: > Hi Mervyn.. > > Thanks very much! I'll start working on this. I miss coding and this will give me something to learn! > Umm, don't spend too much time on that example as it doesn't work. :-( The multiple JOINs aren't doing what I thought they would and I haven't been able to work out what is needed to fix it. A slightly different approach using only 1 JOIN does work. As you have everything in one table that makes it even simpler. (You really must consider normalising your data. But that's a discussion for another day.) Have a look at the attached example. I've tried to write it so that it will use you nazdat table. It is, however, not tested as I don't have a copy of your table. The calculated column shows the number of orders in the selected period. If you don't want this showing in the grid you can use the columns property of the grid to exclude it. To run the form you will first need to create a database alias NAZDAT_DATA for the folder where you keep nazdat.dbf. To do this you will need to run the BDE admin program as Administrator. Shout if you need help. Mervyn. | |
** END HEADER -- do not remove this line // // Generated on 2016/05/24 // parameter bModal local f f = new test_emailForm() if (bModal) f.mdi = false // ensure not MDI f.readModal() else f.open() endif class test_emailForm of FORM with (this) onOpen = class::FORM_ONOPEN height = 24.3182 left = 5.0 top = 0.5 width = 170.7143 text = "" endwith this.NAZDAZ_DATA1 = new DATABASE(this) with (this.NAZDAZ_DATA1) left = 60.0 databaseName = "NAZDAZ_DATA" active = true endwith this.NAZDAZ1 = new QUERY(this) with (this.NAZDAZ1) left = 75.0 database = form.nazdaz_data1 sql = "Select l_name,f_name,email,count(sdate) as c1 " sql +="From nazdaz " sql +="where sdate between :d1 and :d2 and not (email is null or email = ' ') " sql +="group by l_name,f_name,email order by l_name,f_name" params["d1"] = new date(1900,0,1) params["d2"] = new date(2200,0,1) active = true endwith this.RADIOBUTTON1 = new RADIOBUTTON(this) with (this.RADIOBUTTON1) onChange = class::RADIOBUTTON_ONCHANGE height = 1.0909 left = 4.0 top = 3.3182 width = 43.0 text = "Customers with orders in last 7 days" group = true endwith this.RADIOBUTTON2 = new RADIOBUTTON(this) with (this.RADIOBUTTON2) onChange = class::RADIOBUTTON_ONCHANGE height = 1.0909 left = 4.0 top = 5.6364 width = 43.0 text = "Customers with orders in last 30 days" endwith this.RADIOBUTTON3 = new RADIOBUTTON(this) with (this.RADIOBUTTON3) onChange = class::RADIOBUTTON_ONCHANGE height = 1.0909 left = 4.0 top = 7.9545 width = 43.0 text = "Customers with orders in last 90 days" endwith this.RADIOBUTTON4 = new RADIOBUTTON(this) with (this.RADIOBUTTON4) onChange = class::RADIOBUTTON_ONCHANGE height = 1.0909 left = 4.0 top = 10.2727 width = 43.0 text = "Customers with orders in past year" endwith this.RADIOBUTTON5 = new RADIOBUTTON(this) with (this.RADIOBUTTON5) onChange = class::RADIOBUTTON_ONCHANGE height = 1.0909 left = 4.0 top = 12.5909 width = 43.0 text = "Customers with orders in past 2 years" endwith this.RADIOBUTTON6 = new RADIOBUTTON(this) with (this.RADIOBUTTON6) onChange = class::RADIOBUTTON_ONCHANGE height = 1.0909 left = 4.0 top = 14.9091 width = 43.0 text = "Loyal customers (more than 10 orders in 2 years)" endwith this.RADIOBUTTON7 = new RADIOBUTTON(this) with (this.RADIOBUTTON7) onChange = class::RADIOBUTTON_ONCHANGE height = 1.0909 left = 4.0 top = 17.2273 width = 44.0 text = "All customers" value = true endwith this.ENTRYFIELD1 = new ENTRYFIELD(this) with (this.ENTRYFIELD1) height = 1.0 left = 67.0 top = 22.0 width = 8.0 value = 0 endwith this.TEXTLABEL1 = new TEXTLABEL(this) with (this.TEXTLABEL1) height = 1.0 left = 77.0 top = 22.0 width = 17.0 text = "Records selected" endwith this.GRID1 = new GRID(this) with (this.GRID1) dataLink = form.nazdaz1.rowset allowEditing = false height = 16.0 left = 53.0 top = 4.0 width = 110.0 endwith this.rowset = this.nazdaz1.rowset function RADIOBUTTON_onChange() if form.radiobutton1.value =true form.nazdaz1.params["d1"] = form.endDate - 7 form.nazdaz1.params["d2"] = form.endDate form.nazdaz1.rowset.filter = 'c1 > 0' elseif form.radiobutton2.value = true form.nazdaz1.params["d1"] = form.endDate - 30 form.nazdaz1.params["d2"] = form.endDate form.nazdaz1.rowset.filter = 'c1 > 0' elseif form.radiobutton3.value = true form.nazdaz1.params["d1"] = form.endDate - 90 form.nazdaz1.params["d2"] = form.endDate form.nazdaz1.rowset.filter = 'c1 > 0' elseif form.radiobutton4.value = true form.nazdaz1.params["d1"] = form.endDate - 365 form.nazdaz1.params["d2"] = form.endDate form.nazdaz1.rowset.filter = 'c1 > 0' elseif form.radiobutton5.value = true form.nazdaz1.params["d1"] = form.endDate - 730 form.nazdaz1.params["d2"] = form.endDate form.nazdaz1.rowset.filter = 'c1 > 0' elseif form.radiobutton6.value = true form.nazdaz1.params["d1"] = form.endDate - 730 form.nazdaz1.params["d2"] = form.endDate form.nazdaz1.rowset.filter = 'c1 > 10' elseif form.radiobutton7.value = true form.nazdaz1.params["d1"] = form.endDate - 100000 form.nazdaz1.params["d2"] = form.endDate form.nazdaz1.rowset.filter = 'c1 > 0' endif form.nazdaz1.requery() form.entryfield1.value = form.nazdaz1.rowset.count() return function form_onOpen() form.endDate = new date() form.nazdaz1.params["d1"] = form.enddate - 100000 form.nazdaz1.params["d2"] = form.endDate form.nazdaz1.requery() form.entryfield1.value = form.nazdaz1.rowset.count() return endclass /* If the form is opened in the designer the sQL statement will be output as a long line. This cop is for easy reference. sql = "Select l_name,f_name,email,count(sdate) as c1 " sql +="From nazdaz " sql +="where sdate between :d1 and :d2 and not (email is null or email = ' ') " sql +="group by l_name,f_name,email order by l_name,f_name" params["d1"] = new date(1900,0,1) params["d2"] = new date(2200,0,1) */ | |