Is this possible?? - Passing a varialble into : LOCATE FOR (my_var) 3

[networkthis]

I'm new to VFP and I'm trying to pull my table names into a loop and loop through them individually counting the matches per column based on user input..... Here is a simplified version of what I am trying to do... I think I have the basics set up for this, but I'm not quite sure that my variable is passing properly into my locate for ( )... if I simply type mydb.mytablename into: locate for (mydb.mytablename) it works great. However, if I try to pass my_var into it, it fails every time... :-/ Any ideas, or thoughts would be greatly appreciated!!!



LOCAL locate_name

**locate_name = table1.fname

STORE 0 TO gnCount

gnFieldcount = AFIELDS(gaMyArray)

CLEAR

FOR nCount = 1 TO gnFieldCount

?gaMyArray(nCount,1)

field_name = gaMyArray(nCount,1)

locate_name = ('table1.' + field_name)

?locate_name
LOCATE FOR (locate_name) = 'Ray'

DO WHILE FOUND( )

gnCount = gnCount +1

? " It matches: " + fname

CONTINUE

ENDDO

ENDFOR


? ' Total Matches: ' + ALLTRIM(STR(gnCount))

 

[bborissov]

When you use LOCATE you must be in the ALIAS where you want to search. This command unlike SEEK has no IN clause.
So:

....
SELECT Table1
LOCATE FOR &field_name = 'Ray'
 
DO WHILE FOUND( )
   gnCount = gnCount +1
     
     ? "   It matches: " + fname
     
     CONTINUE
     
     ENDDO
     
ENDFOR

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Olaf Doschke]

While simpple paranthesis around an expression like (expression) should evaluation the inner expression to a name, it does not do so in this case. Name expression don't work in any place, just in those place only a name makes.

For example, if you use some table and want to get the left char of some field via Left(("fieldname"),1), this is not evaluated as a name expression, but as a string expression. This yields f, not the first char within the field.

The same goes for the FOR condition, this can contain names for sure, but it more general expects any boolean expression composed of whatever, so FOR (variable) = value actually does test the variables value, not the field value.

Name expression are use in command that only expect names, eg COPY FILE (name) does evaluate the value of the name variable.

So in this case you rather need macro substitution:

LOCATE FOR &locate_name = 'Ray'

On the larger scale, your code looks could be part of a "fulltext" search, if you loop over all character fields and over all or some tables you can collect record numbers and finally list all finds.

If this is working fine for you, I don't want to discourage you, but this will get massively slower the larger the database grows, you better use a normal SQL approach.

Even though indexes on all the fields you locate can make those locates faster it's not recommendable to index all fields of a table just for that, that's making writes slower in the end, as each write may trigger some cdx index file reorganisation, if the new value doesn't fit into the index tree, I've seen inserts of rather small records need >1 sec at times, just because of too much indexes in a table, which had grown >1GB.

There are better ways to do fulltext search, eg rather use an sql server backend with it's built in fulltext functionality. Or create some side data mainly for fulltext search. That also adds the possibility to store nontext fields converted to text, eg dates in your locale date format, and users then can fulltext search including dates for example.

Bye, Olaf.

 

[JRB-Bldr]

A variation on your task might be to use SQL Queries to find how many records meet your search criteria - like Olaf mentions above with "you better use a normal SQL approach."

Something like:

DIMENSION gaMyArray(1)
gnCount = 0

SELECT Table1
gnFieldcount = AFIELDS(gaMyArray)

FOR nCount = 1 TO gnFieldCount
 field_name = gaMyArray(nCount,1)  && Determine Field Name
 field_type = gaMyArray(nCount,2) && Determine Field Type

 IF field_type = "C"
    * --- Only Check Char Fields For Char String ---

    * --- Build Search String ---
    cSrchName = "Ray"  && Or this could be passed into routine
    * --- Use UPPER() to make NOT Case Sensitive ---  
    cSrchStr = "UPPER(Table1." + field_Name + ") = UPPER('" + cSrchName + "')"
    
    * --- Get Temp Cursor Of Records Matching The Criteria ---
    SELECT *;
      FROM Table1;
      WHERE &cSrchStr;
      INTO CURSOR LocaResults READWRITE

    SELECT LocaResults
    IF RECCOUNT() > 0  && Check How many records 'found' for this field match
       nRecs = RECCOUNT()
       gnCount = gnCount + nRecs
       ?" Field: " + field_name + " - Matches Found: " + ALLTRIM(STR(nRecs))
    ENDIF  && IF RECCOUNT() > 0
    USE

 ENDIF  && IF field_type = "C"
ENDFOR

? '   Total Matches: ' + ALLTRIM(STR(gnCount))

I do have to wonder why you would be searching through ALL character fields in the table.
A character string match for a FirstName would not be very applicable in another field such as Address1,City,Vehicle,etc.
For example:
'Ray' in the FirstName field makes sense
But 'Ray' as part of 'StingRay' in the Vehicle field isn't as likely a valid match.
But its your work, so have fun.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Actually with:
cSrchStr = "UPPER(Table1." + field_Name + ") = UPPER('" + cSrchName + "')"
you would not get 'Stingray' since the field contents does not begin with 'Ray' (case in-sensitive).

But you might get "Ray Gun" in the Toys field.

If you wanted to find if 'Ray' exists anywhere within the field contents you would need to change your cSrchStr.

Good Luck,
JRB-Bldr

 

[networkthis]

Thanks everyone for the great info and fast advive! The & before the &field_name is really what I couldn't figure out!!! Or macro substitution as I now know it. It was driving me crazy!

Sorry if my example is a little misleading. I am acutally not using this to check each column for names... its numeric combos(codes assigned to each field). I just made a quick basic example of what I was trying to accomplish and put a name 'Ray' in there instead for simplicity sake.

This table is extremely small about 20 columns and has maybe 200 fileds in all. I just needed something to recursively go through all the records and say hey it's in this column or hey its not in this column without using inlist for each individual statement. This table will likely have more column names added soon and they may be changing the names as well.

Thanks for the extra added input about searhing with text too and the great example with the array!! Every little bit helps a ton!

 

[Olaf Doschke]

If you put your data in an array, you could do your kind of search via ASCAN(). It offers to scan a column only or all elements from a certain start element on, so you can program continually searching for a certain number in an array of numbers.

Bye, Olaf.

 

[networkthis]

JRB-Bldr,
Thanks for breaking everything down the way you did with your example. I just now had time to play with it. I'm really liking the thought of using SQL searches with this a lot better then my original idea!

Olaf,
I'm going to try to put something together today utilizing searching with the ASCAN() command. The more I looked at it's refrences in VFP Help the more I'm seeing that it can definitely be an invaluable tool! I'm just not sure yet how to get it to work with the nSearchColumn part yet :-/

Question for both of you: How did you all learn all this?! Do you have any advice or suggestions for refrence material to learn in depth knowledge about VFP?

 

[Olaf Doschke]

Okay, I think I never told much about my background, not in the internet, but why not:

I am 42 now, and I started in late 1999 with vfp6 and in the first year lerned by myself and from the help and already had a programming background of C/C++ and sql, so it was not my first language. I come from VC-20, basic and 6502 assembler, I did mostly do gaming on those years, though, of course. But I already had a strong background of programming from about mid 80ies onwards.

Others here often report to have started from the very first fox version, doing clipper and dbase beforehand, there is a long way back, where you could have picked up knowledge even before foxpro was there.

The next big step in advancing with my foxpro knowledge was in fact newsgroups and forums and I contually use them, even though I now seldom have a problem or question anymore. I't interesting enought to see what problems others have. As those are mostly real world problems you automatically build a knowledge base. Combined with own experiences that teaches you ideas and strategies you don't learn from books.

I was a Microsoft Foxpro MVP from 2007 until this year, which ended that MVP category. I assume, but don't know, in the end of the year there may not be an VFP MVP anymore, but I actually haven't followed how that develops. Just looked at

https://mvp.support.microsoft.com/communities/mvp.aspx?

and found only 7 MVPs left.

I attended conferences just two times, once in frankfurt nd once in prague and I attend a monthly meeting of foxpro, now more general ms/database developers. Also a good experience, actually I could have got more especially from the conference attendance, if doing this earlier, when there was much to learn. The knowledge I already had at my first conference attendance was merely making this enjoyable to see some pepole I just knew by name and to get some ideas and confirmation. Same goes for two MVP Summits I attended in 2007 and 2008.

Bye, Olaf.

 

[JRB-Bldr]

Without going into my full CV, I got a couple of Computer Science degrees (AS & BS) before there was dirt and I have been developing applications in a variety of languages (Assembly languages and numerous others) for a variety of industries as an on-site employee and later as a contractor/consultant since the early-80's.

By learning how to make things occur in one language, the methodology in another language is quite often pretty similar - just with a modification in syntax.
That having been said, the newer development approaches OOP, etc. have introduced changes to some aspects of methodologies, but even so, at the core, there are still sufficient commonalities to keep me sane.

Here are some free on-line VFP tutorial videos that you might want to look over:

http://www.garfieldhudson.com/FreeVideos.aspx

http://www.sweetpotatosoftware.com/spsblog/2006/07/02/VisualFoxProVideos.aspx

And a whole host of good VFP reference books:

http://www.hentzenwerke.com/catalog/zcatalogsubject.htm#visualfoxpro

Additionally once you have a basic foundation under your belt and then have specific questions we are always here to offer suggestions/advice (keep in mind - our advice is worth every penny you had to pay for it).

Good Luck,
JRB-Bldr

 

[TamarGranor]

Just to add to the resources the others have pointed out, there is still a magazine devoted to Visual FoxPro:

http://www.foxrockx.com

Tamar