Find the location of the column in the VFP table 2

[bharons]

Dear Expert..
I have a classic question. and I don't understand how to solve it, namely looking for a column where the contents of that column are exactly the same as the VFP textbox. The following is the coding that I use.

select crsResult3
lcVar = thisform.text1.value

for lnColumn = 1 to fCount()
   if Evaluate(Fields(lnColumn)) = lcVar 
     lnColumn = lnColumn + 1
   endif
endfor
ThisForm.text2.value = lnColumn

where I use textbox2 to see the column positions in the table.
If I use it with the Field() function, I can determine if the contents are in that column.
and the follow-up question is if the contents of the textbox are found in various tables, what coding do I apply to find the column position?

 

[Mike Lewis]

It's not clear exactly what your question is, or what exactly you are trying to achieve. In particular, a column can't contain a value that matches the contents of a textbox, simply because a column has multiple values, one for each record in the table.

Looking at your code, I assume you are looking for a matching value in the current record. If that's so, this line is incorrect: [tt]lnColumn = lnColumn + 1[/tt]. The FOR/ENDFOR loop itself increments lnColumn, so you don't want to do that implicitly. What's more, even when you have found the matching value, you are contuing the loop, which means that the loop will always end with lnColumn being equal to FCOUNT().

On that basis, the following code might be more appropriate (not tested):

select crsResult3
lcVar = ALLTRIM(thisform.text1.value)

lnMatchCol = 0
for lnColumn = 1 to fCount()
   if alltrim(Evaluate(Fields(lnColumn))) = lcVar 
     lnMatchCol = lnColumn 
     EXIT
   endif
endfor
if lnMatchCol > 0
   ThisForm.text2.value = EVALUATE(FIELDS(lnMatchCol))
endif

Note the use of EXIT to exit the loop. At that point, lnMatchCol will point to the required column.

Note also the use of ALLTRIM(). This is because the contents of the column and/or those of the textbox might be padded with spaces. Similarly, you might want to apply UPPER() to those two values to deal with case sensitivity.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

Why do you speak of columns? A table/cursor has fields - a grid has columns.

Assuming that you want to scan a table/cursor for lcVar your code could look like below (not tested)

*-* from Mike - adapted

create cursor crsResults (iRecord I, iField I)

select crsResult3

lcVar = ALLTRIM(thisform.text1.value)

scan 
   for lnI = 1 to fCount()
      if alltrim(Evaluate(Field(lnI))) = lcVar 
        insert into csrResults values (RecNo("crsResult3"), lni) 
      endif
   endfor
endscan

Select csrResults
browse

hth

MarK

 

[Mike Lewis]

Why do you speak of columns? A table/cursor has fields - a grid has columns.

I assumed he was using the term "column" to mean a field. That's the normal usage in most DBMSs.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Mike,

Maybe that in most DBMSs column is the word to use but here we are talking VFP or?



MarK

 

[Chris Miller]

It's true that the table designer tab is captioned Fields, but the term column is common to SQL and also baked into VFP SQL, see ALTER TABLE. It can have an ADD or ALTER COLUMN clause, not FIELD. In the context of looking through the columns with the FIELDS function it may be more appropriate to also talk of fields, but the terms are interchangably usable also for VFP DBFs.

But that's not what I came for.

If you browse a table you can use the usual CTRL+F Find dialog (which you usually use to find code lines) and find instances of search terms in table columns (or fields), it searches all data and stops at found values, but continues through the whole workarea, if you repeatedly click "Find Next".

But what would be better than going through data record by record and field by field is looking field by field first, with queries that each show all results for some field, that's making use of indexes on fields.

The only thing the find dialog is better at is finding substrings and if, for example, you search 7, it will not only look into numeric fields, it will also find, for example all postal codes and phone numbers that contain a 7. So if you'd like to emulate what the find dialog can find, you have to do a lot more than the reommended solutions.

It's unusual to not know in which column/field you want to search something, though. It points out you have an array of fields that should not exist in a better table design.

Chriss

 

[Chris Miller]

There's also a simple find all solution when you export all data into a CSV/delimited text file and search in that, though such a search lacks index rushmore performance optimizations, but full text searches would not be able to use rushmore, anyway.

Chriss

 

[bharons]

If I browse crsresult3 several columns and their contents will appear.
What I'm asking is in what column the data I'm looking for is in the column.
below is an image of the crsResult3 table

[img

https://res.cloudinary.com/engineering-com/image/upload/v1723560942/tips/Capture1_virntg.jpg

]

if I search for the name of something with the locate() function and know the column names, as an example

select crsResult3 
lcVar = ALLTRIM(UPPER(thisform.text1.value))  
locate for [COLOR=#CC0000]AO[/color] = lcVar 
if found() 
	thisform.text2.value = Recno()
endif

maybe by using the Field(x) function after I open/browse the table, I can determine that the data is in column (x).
From the image above there is a lot of the same data and it happens to be in 2 columns.
and from the code suggested above, I have problems namely,
[highlight #FCE94F]function argument value, type, or count is invalid[/highlight]...
If there is the same data content in the next table, this is what makes me confused.....
Note: I use the VFP 8 application

 

[Mike Lewis]

this is what makes me confused

You're not the only one that's confused.

In your original code, you appeared to be looking for matching data in the current record. Now you appear to be searching the entire table. And then you talk about "the same data content in the next table". Where did this next table come from?

Your latest code (with the LOCATE) should find you the record number of the first record containing the matching data. But only if you know which column it is in (A0 in this example). Also, it only finds the first one. Assuming you want the record numbers of all the records containing the matching data, you could do something like this:

lcVar = ALLTRIM(UPPER(thisform.text1.value))
SELECT RECNO() AS nRec FROM csrResult 3 ;
  WHERE lcVar = ALLTRIM(UPPER(A0)) ;
  INTO CURSOR csrRecNos
BROWSE csrRecNos

That will give you a cursor containing the matching record numbers. But, again, it assumes that you know in advance the column name.

Alternatively, you could do something like this:

lcVar = ALLTRIM(UPPER(thisform.text1.value))
SELECT RECNO() AS nRec FROM csrResult 3 ;
  WHERE lcVar = ALLTRIM(UPPER(A0)) OR lcVar =  ALLTRIM(UPPER(Surveyor)) 
     OR lcVar =  ALLTRIM(UPPER(Analis));
  INTO CURSOR csrRecNos
BROWSE csrRecNos

You will still need to know the column names, but at least this will search all the possible columns.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

In your code

if alltrim(Evaluate(Field(lnI))) = lcVar

The alltrim can easily fail for fields that are not of a string type.

You can only search char/varchar/memo columns for a string search term, that's also why I demonstrated what the find dialog is capable of and that you'll need to do more complex code to emulate that, if it's required or een only something comparable.

Bharons, you showed more detail and I understand instead of only locating the record you want to know in which column the data was found from the LOCATE? That's always AO, in your sample case. It's not that, is it? You'd like to use FIELD(x) instead of AO in the locate and then find out x. Well, LOCATE won't search all fields and then turn x to be the column number that has that value, so you still have to go through all fields in a loop and record by record. There's neither a LOCATE variation nor SQL that would look into all fields at once and then as result aftermath tell you a field number.

Create Cursor results (iRow I, iColumn I)
Local lcSearchTerm, lnRow, lnCol
lnRow = 0
lcSearchTerm = ALLTRIM(Transform(thisform.text1.value))
Select crsResult3 
Scan
   lnRow = lnRow +1
   For lnCol = 1 to FCOUNT()
     If Transform(Evaluate(Field(lnCol))) = lcSearchTerm
        Insert Into results (lnRow, lnCol)
     Endif
   Endfor
Endscan

This would look through all rows and columns of data and will scale proportional with number of records and fields, obviously.
And the end result is a list of row/column number pairs, coordinates of found matches.

If you want to make use of Rushmore optimization and SQL or Locate, then you better know in advance which field or fields to search to not need that generalization of searching everywhere.

LOCATE and its FOR clause, or SQL Select and its WHERE clause always only look for whether the FOR condition(s) or WHERE condition(s) overall are .T. and if so LOCATE will stop at that row, but not position to a column (even with FOR conditions like Ao=lcVar or Oto_by=lcVar) you'll only know that this condition is true, not whether it's true because Ao matches, Oto_by matches or both match, that'll need to be determined as aftermath and with specifcally asking the separate questions which it is.

So there is no such simple language construct, you'll always need to program complexer code to get there, you're looking for something that doesn't exist as an atomic, single simple function or command.

Chriss

 

[Chris Miller]

I don't understand the mening of your fgield names (Ao, etc.) so let me come up with a simple example where there's:
a) a valid reason to have multiple fields of same nature
and
b) the feature to find out not only whether a value is found in one of them but also in which one of them would be helpful.

Let's say you have fields in each record that tell
1. Who created the record
2. Who last modified it
3. Who is responsible for the record
4. ...

You could define more such fields all for User IDs. Let's say your company uses two letters for any employee like CM for Chriss Miller and ML for Mike Lewis, etc.
It would still be a strange search to me to look for a user in all user related fields, because a question you have should be more specific, i.e. do you care for who inserted or last modified a record, if you're interested in finding records a user/employee is responsible for? You'd specifcally only search the 3rd field, not the others.

But a question that could look into two fields of these would be about data entry, where you would not care whether the user inserted or last modified the record, but both options. Inserting is also a last modification, if it is the first and only up to now.

Fine, then your LOCATE could be

LOCATE FOR Inserted='CM' OR Modified='CM'

Now the question is whether it actually matters where CM is found, if it is found the whole record is data entry or modification by me. 'But of course you can look into it. Once such a record is found (FOUND() is .T.) you can look IF Inserted='CM', then you know it's that field, if not, 'CM' must be in Modified, or it would not be in any field and the LOCATE would not have stopped in that row.

Chriss

 

[Chris Miller]

Continmuing the example of employee initials, if you want easier searchable data you don't reinvent the wheel of defining new language constructs, you just organise your data better.

Instead of storing the initals in two fields Inserted and Modified you'd define say roles (recordcreator, recordeditor) and define a separate table:
Employeeroles defining such relationships with the data by records of this type: Just a suggestion, not necessarily needed in all its details:

Table, Record, EmployeeInitials, Role

Then your inital LOCATE becomes a LOCATE FOR EmployeeInitials = 'CM' and the information you need (which record of which table and in which role, inserting or modifying) comes from the rest of these records.

Such things are called meta data, sometimes. And if you have such questions on your data, it's a sign you need such meta data and reorganize the table structures.

Chriss

 

[Chris Miller]

To come back to the idea to do locate for AO = lcVar with all other field names. That's of course doable in a loop, again, but each LOCATE will only care for one specific field

For lnCol = 1 To FCount()
   lcField = Fields(lnCol)
   LOCATE FOR &lcField = lcVar
   If Found()
      ...
   Endif
EndFor lnCol

I only sketch this with no concrete Code in the Found() case, becasue when you'd do this you'd encounter data type problems again, you can only compare char like fields with a searxch string, for one reason, and if you go through the data this way, while that has the advantage to make use of indexes on the fields for each single locate, it has the overhead of compilation of a line containing macro substitution, which is that bad. What's worst is this way you just get the last found place, so that's a hint the ... part of coode could not just be thisform.text2.value = Recno(), you want to stop at the first row and the leftmost column in that row. Then what you need with this series of locate is find the minimum Recno() and when you have the same minimum Recno() twice, the minimum lnCol within that same minimum Recno.

And if you find a rather low Recno() with one locate, you still can't be sure a later LOCATE in another column wouldn't find a result in a lower Recno(), so in the end you'd search through most data anyway, even in columns where you could be sure to not find the search term.

So implementing the First_Field_Locate the most perfomant way is not a simple task at all.

Chriss

 

[mjcmkrsr]

Hi Chriss,

The alltrim can easily fail for fields that don't evaluate to a string type, i.e. for all field that are not of string type.

Sure, it certainly will - but please don't kill the messenger. I didn't write this line of code (please check the beginning of this thread)



MarK

 

[Chris Miller]

MarK, noted and corrected.

Chriss

 

[EinTerraner]

Dear Expert..
I have a classic question. and I don't understand how to solve it, namely looking for a column where the contents of that column are exactly the same as the VFP textbox. The following is the coding that I use.

just try this code

AFIELDS(TmpArray,[AliasName])
lnColumn= ASCAN(TmpArray, ALLTRIM(thisform.text1.value), 1 ,0, 1, 9)

 

[bharons]

just try this code

AFIELDS(TmpArray,[AliasName])
lnColumn= ASCAN(TmpArray, ALLTRIM(thisform.text1.value), 1 ,0, 1, 9)

I put the lnColumn result in textbox3 with the final result 0, what does the value 0 mean

 

[EinTerraner]

with the final result 0, what does the value 0 mean

it means the fieldname was not found in this table. just SET STEP ON right after the AFIELDS(... and see the content of the array

 

[bharons]

In your code

if alltrim(Evaluate(Field(lnI))) = lcVar

The alltrim can easily fail for fields that are not of a string type.

You can only search char/varchar/memo columns for a string search term, that's also why I demonstrated what the find dialog is capable of and that you'll need to do more complex code to emulate that, if it's required or een only something comparable.

Bharons, you showed more detail and I understand instead of only locating the record you want to know in which column the data was found from the LOCATE? That's always AO, in your sample case. It's not that, is it? You'd like to use FIELD(x) instead of AO in the locate and then find out x. Well, LOCATE won't search all fields and then turn x to be the column number that has that value, so you still have to go through all fields in a loop and record by record. There's neither a LOCATE variation nor SQL that would look into all fields at once and then as result aftermath tell you a field number.

Create Cursor results (iRow I, iColumn I)
Local lcSearchTerm, lnRow, lnCol
lnRow = 0
lcSearchTerm = ALLTRIM(Transform(thisform.text1.value))
Select crsResult3
Scan
   lnRow = lnRow +1
   For lnCol = 1 to FCOUNT()
     If Transform(Evaluate(Field(lnCol))) = lcSearchTerm
        Insert Into results (lnRow, lnCol)
     Endif
   Endfor
Endscan

This would look through all rows and columns of data and will scale proportional with number of records and fields, obviously.
And the end result is a list of row/column number pairs, coordinates of found matches.

If you want to make use of Rushmore optimization and SQL or Locate, then you better know in advance which field or fields to search to not need that generalization of searching everywhere.

LOCATE and its FOR clause, or SQL Select and its WHERE clause always only look for whether the FOR condition(s) or WHERE condition(s) overall are .T. and if so LOCATE will stop at that row, but not position to a column (even with FOR conditions like Ao=lcVar or Oto_by=lcVar) you'll only know that this condition is true, not whether it's true because Ao matches, Oto_by matches or both match, that'll need to be determined as aftermath and with specifcally asking the separate questions which it is.

So there is no such simple language construct, you'll always need to program complexer code to get there, you're looking for something that doesn't exist as an atomic, single simple function or command.

Chriss

thanks, teacher... i mean Expert.. it work on my form , i gain in 2 column, then i use field() function to determine name column is

 

[bharons]

SET STEP ON right after the AFIELDS(

my crsResult3 is cursor.