applyLocate - create array...

[unknown]

On Sat, 1 Nov 2008 15:40:13 -0400 Ivan Benttini
Sender: "Ivan Benttini" <***@nc.rr.com>
wrote the following in:
Newsgroup: dbase.programming

To anyone willing and with the time to help me.
My goal with the code below is to LOAD in the array(aNames) all the cities
from the table in use that has the same name.(Not all rowsets as it do now,
regard of the city name as you can see with my code).

Do you mean that the listbox should display all the cities with the same name as the one typed into
Entryfield1? If yes, try the code below my signature; if no, maybe you can use some of the code
anyway :-)


Ivar B. Jessen

//-----
** END HEADER -- do not remove this line
//
// Generated on 30-10-2008
//
parameter bModal
local f
f = new IvanForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif

class IvanForm of FORM
with (this)
onOpen = class::FORM_ONOPEN
height = 8.3182
left = 45.4286
top = 7.0455
width = 23.0
text = ""
endwith

this.IVANAB1 = new QUERY()
this.IVANAB1.parent = this
with (this.IVANAB1)
left = 3.0
top = 0.5
sql = 'select City || "-->" || State CityState from "IVANAB.DBF" where upper(City) =
upper(:cName)'
params["cName"] = ""
active = true
endwith

this.LISTBOX1 = new LISTBOX(this)
with (this.LISTBOX1)
height = 5.0
left = 1.0
top = 0.5
width = 21.0
id = 101
sorted = true
endwith

this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
onChange = class::ENTRYFIELD1_ONCHANGE
height = 1.0
left = 1.0
top = 7.0
width = 19.0
value = ""
endwith

this.rowset = this.ivanab1.rowset

function ENTRYFIELD1_onChange
class::form_onOpen()
return

function form_onOpen
form.IVANAB1.params["cName"] = iif(empty(form.entryfield1.value), "", form.entryfield1.value)
form.IVANAB1.requery()
aNames = new array()
r = form.IVANAB1.rowset
r.first()
do while not r.endofset
aNames.add(r.fields["CityState"].value)
r.next()
enddo
form.Listbox1.datasource = "Array aNames"
return
endclass
//-----

[unknown]

On Sat, 1 Nov 2008 19:34:03 -0400 Ivan Benttini
Sender: "Ivan Benttini" <***@nc.rr.com>
wrote the following in:
Newsgroup: dbase.programming

Thanks so very much Ivar.
((> Do you mean that the listbox should display all the cities with the same name as ((> the one typed into
((> Entryfield1? If yes, try the code below my signature; if no, maybe you can use
((> some of the code anyway :-)
YES, YES.
Now I am going to try to separate CITY and STATE names to put it in EF2 and EF3, that I add to the form.
I hope I can do it.

Try the code below my signature, it illuatrates one way of doing it. As I don't know in which way
you are going to use the separated values, the code may not be optimal, I did not look at the grid,
as you did not explain its purpose.


Ivar B. Jessen

//-----
** END HEADER -- do not remove this line
//
// Generated on 31-10-2008
//
parameter bModal
local f
f = new IvanRevAForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif

class IvanRevAForm of FORM
with (this)
onOpen = class::FORM_ONOPEN
height = 15.8636
left = 26.0
top = 3.7727
width = 70.1429
text = ""
endwith

this.IVANAB1 = new QUERY()
this.IVANAB1.parent = this
with (this.IVANAB1)
left = 23.0
top = 0.5
sql = 'select City || "-->" || State CityState from"IVANAB.DBF" where upper(City) =
upper(:cName)'
params["cName"] = ""
active = true
endwith

this.LISTBOX1 = new LISTBOX(this)
with (this.LISTBOX1)
onSelChange = class::LISTBOX1_ONSELCHANGE
height = 5.5
left = 2.0
top = 8.0
width = 28.0
id = 101
sorted = true
endwith

this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
onChange = class::ENTRYFIELD1_ONCHANGE
height = 1.0
left = 2.0
top = 2.5
width = 28.0
value = ""
endwith

this.TEXT1 = new TEXT(this)
with (this.TEXT1)
height = 1.0
left = 2.0
top = 1.5
width = 19.0
colorNormal = "blue/BtnFace"
fontName = "Times New Roman"
fontBold = true
fontItalic = true
text = "Enter city name below:"
endwith

this.TEXT2 = new TEXT(this)
with (this.TEXT2)
height = 1.0
left = 2.0
top = 7.0
width = 24.0
colorNormal = "blue/BtnFace"
fontName = "Times New Roman"
fontBold = true
fontItalic = true
text = "List of cities with same name:"
endwith

this.PUSHBUTTON1 = new PUSHBUTTON(this)
with (this.PUSHBUTTON1)
onClick = class::PUSHBUTTON1_ONCLICK
height = 1.5
left = 2.0
top = 13.5
width = 28.0
text = "Press here to select new city"
fontName = "Times New Roman"
fontBold = true
fontItalic = true
borderStyle = 8 // Modal
colorNormal = "blue/BtnFace"
endwith

this.GRID1 = new GRID(this)
with (this.GRID1)
fontName = "Times New Roman"
fontSize = 9.0
fontBold = true
fontItalic = true
headingFontName = "Times New Roman"
headingFontItalic = true
dataLink = form.ivanab1.rowset
height = 8.0
left = 32.0
top = 2.0
width = 30.0
endwith

this.TEXT3 = new TEXT(this)
with (this.TEXT3)
height = 1.0
left = 32.0
top = 1.0
width = 20.0
colorNormal = "blue/BtnFace"
fontName = "Times New Roman"
fontBold = true
fontItalic = true
text = "Grid with selected table:"
endwith

this.TEXT4 = new TEXT(this)
with (this.TEXT4)
height = 1.0
left = 2.0
top = 4.0
width = 4.0
colorNormal = "blue/BtnFace"
fontName = "Times New Roman"
fontBold = true
fontItalic = true
text = "City"
endwith

this.TEXT5 = new TEXT(this)
with (this.TEXT5)
height = 1.0
left = 24.0
top = 4.0
width = 5.0
colorNormal = "blue/BtnFace"
fontName = "Times New Roman"
fontBold = true
fontItalic = true
text = "State"
endwith

this.ENTRYFIELD2 = new ENTRYFIELD(this)
with (this.ENTRYFIELD2)
height = 1.0
left = 2.0
top = 5.0
width = 20.0
value = ""
endwith

this.ENTRYFIELD3 = new ENTRYFIELD(this)
with (this.ENTRYFIELD3)
height = 1.0
left = 24.0
top = 5.0
width = 5.0
value = ""
endwith

this.rowset = this.ivanab1.rowset

function ENTRYFIELD1_onChange
class::form_onOpen()
return

function LISTBOX1_onSelChange
cStr = iif("ARRAY" $ form.Listbox1.selected(), "", form.Listbox1.selected())
form.entryfield2.value = substr(cStr, 1, at("-", cStr) - 1)
form.entryfield3.value = substr(cStr, rat(">", cStr) + 1)
form.entryfield1.setFocus()
return

function PUSHBUTTON1_onClick
form.entryfield1.value = ""
class::form_onOpen()
return

function form_onOpen
form.IVANAB1.params["cName"] = form.entryfield1.value
form.IVANAB1.requery()
form.aNames = new array()
r = form.IVANAB1.rowset
r.first()
do while not r.endofset
form.aNames.add(r.fields["CityState"].value)
r.next()
enddo
form.Listbox1.datasource = "Array form.aNames"
form.listbox1.curSel = 1
class::listbox1_onSelChange()
return
endclass
//-----

[unknown]

On Sun, 2 Nov 2008 09:33:19 -0500 Ivan Benttini
Sender: "Ivan Benttini" <***@nc.rr.com>
wrote the following in:
Newsgroup: dbase.programming

Hi Ivar,
Do you mind if I send you some questions about some the code you send sent
me?
everything is working, but since I never delt with some of your statement,
like your statement on the
SQL = 'select City || "-->" || State CityState from "IvanAB.dbf"
where upper(City) = upper(:cName)' params["cName"] = ""
I never use anything like that.

Hmm, you used it in the code in your original message :-)

q=new Query()
q.sql="Select * from Ivanab"
q.active=true
r=q.rowset

In the your SELECT statement the asterisk means that all fields in the table should be included in
the rowset. But in this case we are only interested in the two fields 'City' and 'State' and if the
value of the fields are respectively 'Tampa' and 'FL' we want to concatenate the values as the
string 'Tampa-->FL'.

This can all be done in one SELECT statement using LOCAL SQL. In you BDE directory is a helpfile
named localsql.hlp which has many examples illustrating the various statements and functions in
local sql.

I suggest you read the fillowing topics:

SELECT statement
Concatenation function
UPPER function
WHERE clause
Parameter substitutions in DML statements ( See also the params example in the dBase OLH )

You will find that the SQL I used reads almost like dBase code:

SQL = Select the field 'City', add its value to the string "-->" and add the result to the value of
field 'State' from table IvanAB; name the result 'CityState'. Until now you have in effect created a
rowset with the single field 'CityState'. It is similat to a calulated field in dBase.

In the last part of the SQL you restrict the number of table rows to the ones where upper(City) is
equal to the upper value of a parameter which contains a specific state name. This is almost pure
dBase coding ;-)

The following is a simpel example illustrating the concepts.

Your table IvanAB contains the fields FNAME, MNAME, LNAME and FULLNAME. The last field is
superfluous. It is not good practice to include it, because data has to be typed twice and you run
into problems if there are typo's or omissions in the full name which does not exist in one of the
other three fields.

If you need the full names, for example for printing a list, you can easily create it with a select
statement as shown below. ( Try it from the command pane ).

select fname || " " || mname || " " || lname as fname, fullname from IvanAb
list fname, fullname
use

The result is,

Record# fname fullname
1 Peter B DaCosta Peter DaCosta
2 Steve W Baylis Steve W Baylis
3 Ivan B Benttini Ivan B Benttini
4 Abe Schening Abe Schening
5 Charles M Acock Charles M Acock
6 Christian Paez Cristian Paez
7 Duane Bays Duane Bays
8 Aparecida T Toledo Aparecida Toledo

As you will notice, your fullname column contains two typo's or omissions, which could, and should,
have been avoided <smile>

I hope the explanation has been useful.


Ivar B. Jessen