Please help how to deal with SCAN command... 8

[Mandy_crw]

Good day everyone... I have this code,

IF this.Parent.text7.value = 1
identifier = ALLTRIM(UPPER(tsulat.special))
ELSE
identifier = ALLTRIM(UPPER(tsulat.status))
ENDIF


IF FLOCK()

SCAN NOOPTIMIZE FOR ALLTRIM(this.Parent.CboGrdLvl.value) = ALLTRIM(tsulat.grade) AND identifier = indicator

this.Parent.text4.value = TRIM(tsulat.idnum)
this.parent.text1.value = TRIM(tsulat.sname) + ", " + TRIM(tsulat.fname)
this.Parent.text2.value = TRIM(tsulat.mobile)

​

endif​

i want criteria in my scan differs depending whether text7.value is 1 or not... but i dont seem to get the right code... please help thanks...

 

[GriffMG]

Well, your code is missing an ENDSCAN and would benefit from an m.dot tweak to remove possible confusion:

SELECT TSULAT

IF THIS.PARENT.TEXT7.VALUE = 1
	m.IDENTIFIER = ALLTRIM(UPPER(TSULAT.SPECIAL))
ELSE
	m.IDENTIFIER = ALLTRIM(UPPER(TSULAT.STATUS))
ENDIF


IF FLOCK()

	SCAN NOOPTIMIZE FOR ALLTRIM(THIS.PARENT.CBOGRDLVL.VALUE) = ALLTRIM(TSULAT.GRADE) .AND. m.IDENTIFIER = TSULAT.INDICATOR

		THIS.PARENT.TEXT4.VALUE = TRIM(TSULAT.IDNUM)
		THIS.PARENT.TEXT1.VALUE = TRIM(TSULAT.SNAME) + ", " + TRIM(TSULAT.FNAME)
		THIS.PARENT.TEXT2.VALUE = TRIM(TSULAT.MOBILE)

	ENDSCAN


ENDIF

But, that is really not so helpful - because as it sits the code will just flash through all the records in TSULAT changing the
screen values of text values TEXT1, 2 and 4 - which is maybe interesting, but in no way useful.

Did you mean to find the FIRST value that matches the criteria, code below, or perhaps you want the LAST value (which your code would yield)...

SELECT TSULAT

IF THIS.PARENT.TEXT7.VALUE = 1
	m.IDENTIFIER = ALLTRIM(UPPER(TSULAT.SPECIAL))
ELSE
	m.IDENTIFIER = ALLTRIM(UPPER(TSULAT.STATUS))
ENDIF


IF FLOCK()

	LOCATE FOR ALLTRIM(THIS.PARENT.CBOGRDLVL.VALUE) = ALLTRIM(TSULAT.GRADE) .AND. m.IDENTIFIER = TSULAT.INDICATOR

	THIS.PARENT.TEXT4.VALUE = TRIM(TSULAT.IDNUM)
	THIS.PARENT.TEXT1.VALUE = TRIM(TSULAT.SNAME) + ", " + TRIM(TSULAT.FNAME)
	THIS.PARENT.TEXT2.VALUE = TRIM(TSULAT.MOBILE)


ENDIF

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mandy_crw]

hi griff thanks for your swift answer...but what i mean is...

IF THIS.PARENT.TEXT7.VALUE = 1
SCAN NOOPTIMIZE FOR ALLTRIM(THIS.PARENT.CBOGRDLVL.VALUE) = ALLTRIM(TSULAT.GRADE) and ALLTRIM(UPPER(TSULAT.SPECIAL))
ELSE
SCAN NOOPTIMIZE FOR ALLTRIM(THIS.PARENT.CBOGRDLVL.VALUE) = ALLTRIM(TSULAT.GRADE) AND ALLTRIM(UPPER(TSULAT.STATUS))
ENDIF

so that if text7.value is equal to 1 it will look for cbogradlvl = alltrim(tsulat.grade) and alltrim(upper(tsulat.special)) = indicator and if not it will look for cbogradlvl = alltrim(tsulat.grade) and alltrim(upper(tsulat.status)) = indicator.

Thanks in advance...

 

[GriffMG]

Not quite following you but this would do that...

SELECT TSULAT
IF FLOCK()
	IF THIS.PARENT.TEXT7.VALUE = 1
		SCAN NOOPTIMIZE FOR ALLTRIM(THIS.PARENT.CBOGRDLVL.VALUE) = ALLTRIM(TSULAT.GRADE) .AND. ALLTRIM(UPPER(TSULAT.SPECIAL)) = ALLTRIM(UPPER(TSULAT.INDICATOR))

			THIS.PARENT.TEXT4.VALUE = TRIM(TSULAT.IDNUM)
			THIS.PARENT.TEXT1.VALUE = TRIM(TSULAT.SNAME) + ", " + TRIM(TSULAT.FNAME)
			THIS.PARENT.TEXT2.VALUE = TRIM(TSULAT.MOBILE)

		ENDSCAN
	ELSE
		SCAN NOOPTIMIZE FOR ALLTRIM(THIS.PARENT.CBOGRDLVL.VALUE) = ALLTRIM(TSULAT.GRADE) .AND. .AND. ALLTRIM(UPPER(TSULAT.STATUS)) = ALLTRIM(UPPER(TSULAT.INDICATOR))

			THIS.PARENT.TEXT4.VALUE = TRIM(TSULAT.IDNUM)
			THIS.PARENT.TEXT1.VALUE = TRIM(TSULAT.SNAME) + ", " + TRIM(TSULAT.FNAME)
			THIS.PARENT.TEXT2.VALUE = TRIM(TSULAT.MOBILE)

		ENDSCAN
	ENDIF
ENDIF

Or (may or not be more efficient)

SELECT TSULAT

IF THIS.PARENT.TEXT7.VALUE = 1
	MYCONDITION= "ALLTRIM(THIS.PARENT.CBOGRDLVL.VALUE) = ALLTRIM(TSULAT.GRADE) .AND. ALLTRIM(UPPER(TSULAT.SPECIAL)) = ALLTRIM(UPPER(TSULAT.INDICATOR))"
ELSE
	MYCONDITION= "ALLTRIM(THIS.PARENT.CBOGRDLVL.VALUE) = ALLTRIM(TSULAT.GRADE) .AND. ALLTRIM(UPPER(TSULAT.STATUS)) = ALLTRIM(UPPER(TSULAT.INDICATOR))"
ENDIF

IF FLOCK()

	SCAN NOOPTIMIZE FOR &MYCONDITION

		THIS.PARENT.TEXT4.VALUE = TRIM(TSULAT.IDNUM)
		THIS.PARENT.TEXT1.VALUE = TRIM(TSULAT.SNAME) + ", " + TRIM(TSULAT.FNAME)
		THIS.PARENT.TEXT2.VALUE = TRIM(TSULAT.MOBILE)

	ENDSCAN


ENDIF

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mandy_crw]

Hi griff... thank you so much it works....God bless!

 

[Mike Lewis]

Mandy (and Griff),

I don't understand why you are using a SCAN loop here. A SCAN loop would be appropriate if you wanted to change the value in every record in a table. But, here, you only changing the values of textboxes; you do no need to process every record in the table in order to do that. (Unless I have completely misunderstood the problem, which is possible.)

Also, I think the test for FLOCK() is unnecessary. You are not updating the table, so there is no need to lock it.

Also, I disagree with Griff's statement:

your code ... would benefit from an m.dot tweak to remove possible confusion:

In this case, the m.dot here:

m.IDENTIFIER = ALLTRIM(UPPER(TSULAT.STATUS))

serves no purpose, as there can be no doubt that IDENTIFIER is a variable (but it would do no harm to leave the m.dot in place).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Hi Mike,

Is it not possible to have a TSULAT.IDENTIFIER that would be taken in preference to a variable by the same name?

I'm pretty confident there is at least one poster here who would argue that until the cows came home - so to speak B-)

I agree about the scan, unless the OP is doing something else not shown, or wants a slow way to get to the LAST value
that matches the criteria (as I suggested).

My suspicion is that there is an acre of code in the scan loop that we can't see at the moment...

FLock(), why not? That would stop another user updating while the scan is performed, necessary? probably not and the
OP dropped it in the second example so who knows B-)

I also suspect that the OPs second code example has a flaw trying to nest a SCAN within an IF...ELSE...ENDIF.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

Hi Mike Lewis,

In this case, the m.dot here:

m.IDENTIFIER = ALLTRIM(UPPER(TSULAT.STATUS))

serves no purpose, as there can be no doubt that IDENTIFIER is a variable (but it would do no harm to leave the m.dot in place).

Not so fast. In a line on it's own [tt]IDENTIFIER = ALLTRIM(UPPER(TSULAT.STATUS))[/tt] would be an assignment and IDENTIFIER therefore wouldn't be read and there is no benefit of m. here as VFP would immediately only look for variables of that name and not fields.

But this is in a FOR condition, where = means comparison and not assignment. In a comparison both sides - RHS or LHS - can be fields or expressions involving fields, too. That's the tricky part of a tool like Thor's adding mdots, by the way.

You can easily fall for this RHS/LHS way of writing a condition when you strictly program by the rule to always make field/variable comparisons in the order field=variable, field as LHS, variable as RHS. Similarly with fields order in a join condition. And that makes sense with the default non-EXACT matching the = operator means. It's not wrong to ask for variable = field, though you have to be careful variable is the length of the field or you get .F. since inexact comparisons are made to the length of RHS and automatically .f. when the LHS is shorter than the RHS.

Or to say it in simpler words, since string comparison in VFP is "starts with" comparison, a shorter string can never start with a longer string. The only case where some part of a string is ignored is not a whitespace case but the case of the right string being shorter than the left string and in that case the left string can continue with whitespace but also with anything else, which makes this comparison a bit questionable, if you're not used to it being a "starts with" comparison.

Mandy,

I see you have a solution so there's nothing to add for this case, just note I'd also vote for using a LOCATE. I think (but don't know it - it's just a strong assumption) the conditions you make are for finding a single record. Because it makes no sense to make multiple control value assignments, the user will finally only see the last assignment.

So in very short: Look for a single record?
Use SEEK if an index exists that is sufficient for the search condition, usually conditions of the kind field=value qualify for this, where field is indexed.
Use LOCATE if multiple indexes could be used for the single parts of a more complex condition, also works in any case even without indexes, just not optimized.
Use SCAN only to process all or at least a group of records. SCAN with an EXIT in the loop before ENDSCAN can act the same as a LOCATE, if you're sure you'll only find one record you can also do without EXIT, but in any case a LOCATE then is the simpler tool to use.

I think all this and your use of NOOPTIMIZE comes from a previous thread where you tried to learn from a discussion following the solution to your question. It's usually NOT a good idea to use NOOPTIMIZE. Only in very rare cases you know trying to find a rushmore optimization for the for condition is a waste of time. It usually isn't a waste, even for complex search conditions, as they do make use of indexes for the single parts of a condition that has several terms combined with boolean AND/OR/NOT/etc. operations.

For finding single records you could always also use SEEK, if you prepare it with an index of whatever complex condition you have, but that'll be a waste of time for that index only can be used for the special condition.

There are curious special cases where indexing itself with a FOR condition means you create a small index that only covers some records of a DBF, aka filtered index. The index creation itself, more precisely the FOR condition telling which records are put into the index and which not, are rushmore optimized if the FOR condition allows it. In very short that just means the FOR condition of any command having it is Rushmore optimized.

Why filtered indexes are usually avoided is that they hide the fact SET ORDER to such an index does not only set a sort order, at the same time it filters the dbf workarea. This filtering affects records seen by a BROWSE or SCAN/ENDSCAN even when they don't have a FOR condition, just like a SET FILTER does. This makes creating a filtered index an alternative to a SCAN FOR, when you know you'll scan this group of records repeatedly. You just always need to be aware using filtered indexes means filtering the DBF to records that make it into the index. An advantage is, that records which are not fulfilling the FOR condition after an UPDATE or new or modified records that do fulfill the FOR condition will get into the index and so such an index has a good reusability, too. The only bad thing is this filtering is hidden in the index definition and so that filtering effect can surprise someone who only sees the SET ORDER and doesn't know it's a SET FILTER at the same time because it's a filtered index.

If you don't understand that last bit about creating filtered indexes, never mind. It's not necessary to use that mechanism anyway. You could actually also file it under "does more harm than good, even when you know what you're doing".

But if you have questions about when to use SEEK, LOCATE, and SCAN, please ask for anything still unclear.

Chriss

 

[Mike Lewis]

Chriss,

I take your point about using m. in comparisons. No argument there. But that is not the case here. The line of code I was commenting on was a simple assignment statement. As you say, there is never any reason to use m. with an entity on the left-hand side of an assignment which is the case here.

Griff,

Is it not possible to have a TSULAT.IDENTIFIER that would be taken in preference to a variable by the same name?

No. At least, not on the left-hand side of an assignemtn statement, which is the point I was making.

I'm pretty confident there is at least one poster here who would argue that until the cows came home - so to speak happy shades

Yes, it's a subject that has come up several time in the forum, and which some people feel strongly about. (I don't.)

Regarding the SCAN loop, my guess is that Mandy wants to update the textboxes with values from the first matching record. If that's right, the simplest solution would be to LOCATE that record.

Regarding FLOCK(), the way to deal with multi-user conflicts while you are reading a table (as opposed to writing to it) is to issue SET LOCK ON. That would prevent another user changing a table while you are scanning (or otherwise traversing it). But I'm not sure that is what's needed here. It's more useful when you are, for example, printing a report, and you don't want the data to change between starting the report and finishing it.

Mandy,

Given that your code is working the way you want, you can probably ignore all of the above discussion. You might find it educational to follow the discussion, but it won't make a big difference to the solution to your problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

In summary, Mandy,

Learn when to use SEEK, LOCATE and SCAN.

Als notice there is SEEK() as a function, too, and there's one more function INDEXSEEK():

And here's some tmeplate code for each of them:

SEEK:

* prerequisites: A selected workarea with a set index
SEEK value
IF FOUND()
   * can use the found record
ENDIF

SEEK():

* prerequisites: an open alias to seek in
IF SEEK(value, alias, index tag)
   * can use the found record
ENDIF

The intellisense description of SEEK() is more complex, look into the documentation, what's importatn to notice is that SEEK() returns .T. or false and thus combines the SEEK command with a follow up FOUND into one call.

INDXSEEK():

* prerequisites: same as for SEEK().
IF INDEXSEEK(value, .F., alias, index tag)
   * you now know such an record exists, but you haven't moved to it
ENDIF

INDXSEEK has one more parameter than SEEK, the second one, called lMovepointer. If you set it .T. it measn it does exactly the same as SEEK(), so the only real usecase is with this set to .F. and in that case you only know the value is found in the index, but not in which record. The use case for this is very limited, but might play a role in record buffered workareas, where you want to make sure leaving the record doesn't cause a uniqueness violation of an index.

So in short: Forget about the existence of INDEXSEEK until you get very deep into special cases. With tbale buffering instead of record buffering the problems triggered by moving the record pointer off of a buffered record vanish and then this function becomes really very useless.

Maybe also notice that most of the time the SEEK command is used by developers instead of SEEK() including the FOUND(), because it's used in cases you're already sure you'll find a record. When you SEEK a value that came from a field, for example, for going back to exactly that record. Mostly when the index is on the primary key field aka identifier or ID.

LOCATE:

* prerequisite: an open and seleccted alias to locate in
LOCATE FOR condition
IF FOUND()
   * do something with the located record
ENDIF

Also see CONTINUE:

* prerequisite: same as for LOCATE.
LOCATE FOR condition
DO WHILE FOUND()
   * do something with the located record
   CONTINUE * search the next record also fulfilling the condition
ENDDO

I usually don't use CONTINUE as you can then also use SCAN FOR. What it does could be describes as LOCATE REST FOR same condition as in the previous LOCATE. Which is a LOCATE that starts from the current record and so only checks records following. Which is what SCAN FOR does, too. In a simpler to understand manner.

One advantage of LOCATE and CONTINUE is unlike a SCAN..ENDSCAN loop this can even be put into separate methods, but it's at the same time a disadvantage as it hides effectively doing a loop construct.

Therefore I'd recommend to forget about CONTINUE. It has no real benefits.

SCAN FOR:

* prerequisite: same as for LOCATE
SCAN FOR condition
* do something with the record (one of usually many) fulfilling the condition
ENDSCAN

The most common use case of SCAN is for all records. FOR already is specific, there are more possibilities, more scopes than SCAN FOR but in comparison with SEEK and LOCATE FOR, SCAN FOR is the SCAN option to care for. See scope operators (ALL, REST, NEXT, WHILE and some combinations of them) to understand more about record scopes.

But to get back to this case: To find one record SCAN is not the tool to use. Rather use SEEK or LOCATE.

Chriss

 

[Chris Miller]

The line of code I was commenting on was a simple assignment statement.

I see, my fault. The Thor tool does, though, doesn't it? Because it's much easier to just parse for LOCAL and PUBLIC declaration of variable names and then prefix all of these names in the code than to fiddle with finding out whether the code using the name in some place is assigning to the name or reading its value.

Chriss

 

[Mandy_crw]

As i am reading all your answers it taught valueable lessons on how to use VFP well.... Im just trying to view every record that is met by criteria in SCAN.... Thanks Chriss, Mike, Griff and Myearwood...
Noted on how to ask questions, thank you so much everyone... you guys rock!!! God bless,,,,

 

[Mandy_crw]

Hi myearwood... exactly what i want...i just want every record that meets the criteria of scan be shown... hence the last record showing in the form... i think i have confused you.. im really sorry.... thanks and Godbless....

 

[Chris Miller]

Many,

as you say you want every record meeting the criteria shown, you cannot do this with just one set of controls for one record.

hence the last record showing in the form

Is that useful? You only see the last record, not previous records. You even don't know how many results exist.

Nobody would assume you want to scan all result records as you only have a set of textboxes that show one record at a time.

To display multiple records you need to use a Grid or Listbox. You could add controls for every record, but I wouldn't recommend creating controls at runtime, this could go very bad if you have very many results, will always be much more work to cope with the place needed for controls the form has to grow in height, adding scrollbars to a form once it would becomes larger than the screen to have place for all controls needs to be done, etc. All that's much simpler as Listboxes and Grids are prepared to display any number of rows and have their own scrollbar.

Once you decide between Grid and Listbox, the SCAN will not become your best option, what you want is a cursor of the result records and therefore an SQL query into a cursor, not a SCAN loop.

Chriss.

 

[Steve Meyerson]

.i just want every record that meets the criteria of scan be shown... hence the last record showing in the form

Hi Mandy, What you say here is not clear to me. Unless I'm mistaken, you either want ALL those records shown OR only the LAST record meeting your criteria. OR BOTH (which I doubt). Perhaps you could you make clear WHICH of these you need? (That is, unless you've received your answer already).

HTH

Steve

 

[Mandy_crw]

Hi myearwood! Wow! Just wow!! i hope i can have that UI also... i am really a newbie in VFP programming... if i could borrow that app Myearwood! i really want to learn... Thanks and God bless!

 

[Mandy_crw]

Hi Myearwood, my company is not paying me for the application, they consider it old.... although i tried selling it... our company is using web based app... i was a dbase and foxpro programmer before and i am so fascinated with vfp9, so im trying to relearn and combine dbase, foxpro and vfp... its a hobby actually... i just like programming although this not being used anymore... i just love doing it...

 

[Mandy_crw]

Hi Myearwood… thats ok, my company is very small, and my app is also simple… newboe programmer here, there are alot of things in VFp 9 that i dnt understand… Could you share the code for me to based on or copy some features?

 

[Mike Lewis]

Mandy (and others),

Could I raise a cautionary word about frameworks (of which Visual MaxFrame is an example).

This is absolutely no criticism of MaxFrame or any other framework. On the contrary, MaxFrame is a substantial product, the work of several talented developers who have done a good job of keeping it up to date and relevant.

But there are two things to keep in mind about any framework:

- It will require a substantial amount of learning time; and

- It will be difficult to implement retrospectively.

To use a framework effectively, you need to embrace it fully. Up to a point, you can dip into a framework and pick out individual functions that are likely to be useful to you. But to get real value from it, you need to make it the basis of your entire application. You need to design application from the ground up with the framework in mind.

In your case, Mandy, knowing what I do about your circumstances, I would advise you not go down that route. Better to carry on with what you are already doing: asking questions about specific problems, and learning from the replies. By all means, look for examples of specific code, but focus more on learning the underlying techniques.

At least, that's opinion for what it's worth. Others might not agree.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mandy_crw]

Hi Mike and Myearwood! Thank you... and god bless....