Incremental Search and SELECT with display in a GRID being a problem

[FoxEgg]

Sorry, back again...

I use a TexTBox and InteractiveChange Event with

Actual Last Name  is calculated to lcLastName
Actual First Name is lcFirstName

SELECT LAST_NAME, FIRST_NAME, FILE_NUM, INSURANCE FROM 1;
	WHERE First_name = UPPER(ThisForm.lcFirstName);
	AND   Last_name  = UPPER(ThisForm.lcLastName);
	INTO  CURSOR Temp1 READWRITE

and all I want to do is populate a GRID with the Temp1.. and update as I type in more of the name

I learned (and that took me an age to discover) that I had to execute a " ZAP in temp1 " for each new search
to get rid of previous temp file

But even with that, and with every permutation under the sun, I still cant seem to get the grid to display...

I use recordsource = temp1 <-- SURELY THAT IS CORRECT

each TextBox is controlled with the LAST_NAME FIRST_NAME etc etc

But I have tried recordsource type as Table SQL ALias but I cannot get it to display




In desperation, I used Ramamni's FAQ code on incremental search and I am still trying to get that to work ((GridBase errors or something))


OK SO IN THE MEANTIME !!!

May I ask .. What are the CORRECT SETTINGS on the Grid so that it will show the wretched incremental search using that SELECT code outlined in this post.

Thanks again


JF

PS I replaced the 'This form dot' variables with others /// then executed the select in the Command Window and Browsed the temp file... No problems....




Sydney, Australia

 

[FoxEgg]

Weird.. I added another basic (bare) grid alias with temp1 as record source
Then I run the form this time... the correct info is shown
BUT if I type a single letter into the interactive text box ... it goes blank

If I go to command Window and do a BROWSE there is a correct display


Grrrrrrrrrrrr It is clearly ME ~

J

Sydney, Australia

 

[Dan Freeman]

Your grid is deconstructing.

Every time you close a record source (such as with ZAP), the grid and all of the controls it contains are destroyed.

This is YET ANOTHER good use for a parameterized local view.

Your query (in the view) becomes:

SELECT LAST_NAME, FIRST_NAME, FILE_NUM, INSURANCE FROM 1;
	WHERE First_name = ?lcFirstName;
	AND   ?lcLastName

In your interactive change:

lcFirstname = (whatever) + "%"
lcLastName  = (whatever) + "%"
Requery("the view")
Grid.Refresh()

Note the addition of the SQL wildcard characters. That makes the query work as you type.

 

[vgulielmus]

I guess you are facing the grid reconstruction issue.
Instead of SELEC ... INTO ... better try INSERT INTO ... SELECT ... (perhaps followed by a grid refresh)
You can use RecordSourceType = 4 and as RecordSource your SELECT phrase

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Dan Freeman]

Yes, it's the INTO clause that's causing the deconstruction. VFP must close the original alias to recreate it.

I horked that explanation.

You can use Andy Kramek/Marcia Akins' "safe zap" technique where you SELECT into some OTHER cursor, Zap the original and append from the new cursor.

Either way you go, this is going to be performance dog using interactive change and updating with each character typed. I'll generally let the user type what they want and press enter to see results.

 

[FoxEgg]

I am getting there... but it (I) am slow !

OK so thanks to all for the Grid Deconstructing ... Learnt my lesson to not go there again.



So Dan.. like you said... back to a parameterized local view

So I created View2 in the dbc

SELECT _1.last_name, _1.first_name, _1.file_num, _1.insurance;
 FROM ;
     foxegg2015!1 _1;
 WHERE  _1.last_name = ( ?thisform.lcLastName );
   AND  _1.first_name = ( ?thisform.lcFirstName )

thisform.lcLastName and thisform.lcFirstname are properties that I created for the form

I calculate them from a TXTSearchName box.. This next code box is just the bit of code for the Last Name

   ThisForm.lcLastName = ALLTRIM(ThisForm.txtSearchName.Value)
   ThisForm.lcFirstName = ''

I know that works...

I then do when I type in (say) F the interactive code proceeds to

USE view2
Requery()
**messagebox("  REQUERY()  = "+STR(REQUERY(),10,2))
thisform.Grid1.Refresh()

and requery returns a 1 = successful

BUT Grid1 does not refresh ! and It does not change from the first F occurrence.

So I check in COMMAND WINDOW

USE view2
BROWSE

and I have to enter lcLastName and lcFirstName manually... BUT IT WORKS ! The Browse shows appropriate data...

SO WHY WONT THE GRID refresh in the form ?

Whats more... if I run the form a second time... it remembers THE PREVIOUS search

eg If I type in FR.. then close and re open.... and enter Q.. It shows FR last names in Grid1
if I close and re-open and enter RO. It shows Q last names in Grid1


I am pretty sure that's not right
Something isn't refreshing

J

and it is as hot as here and A/c is off.. definitely not refreshing here







Sydney, Australia

 

[Olaf Doschke]

View parameters should not be form properties. I don't think this will work.

Besides, this will be as slow as querying into cursor, a view requery is a query, this is not making a fast filter.
If you want a fast filter on data the best thing you can do is either SET FILTER or SET KEY with an appropriate index.

Bye, Olaf.

 

[FoxEgg]

Thanks Olaf

I have been all day on this and I removed the form properties..

I am officially giving in on this and using a SET FILTER

Thanks to all .. especially Dan who set me right on many things..

I will now try and make Ramani's incremental FAQ work for me

John Fox

Sydney, Australia

 

[Olaf Doschke]

Whats more... if I run the form a second time... it remembers THE PREVIOUS search 

eg If I type in FR.. then close and re open.... and enter Q.. It shows FR last names in Grid1
if I close and re-open and enter RO. It shows Q last names in Grid1


I am pretty sure that's not right

That sounds like you're using any third party code automatically saving forms in their current state. One simple explanation is you don't init the view but use it as is. It's not bound to the form, if the form works in the current workarea and not in a private workarea.

All these things like private/general datasessions, scope of parameters/variables/memory object sin general play together here. You will have the same behavior when using SET FILTER. The init must query the view without any restriction, eg empty firtname and lastname make the view query all data. And that's also what takes much longer than USE of a table.

A typically solution to not do the queries taking too long is to only start requery with at least two or three characters typed.

Bye, Olaf.

 

[Dan Freeman]

One thing:

[b]USE view2[/b]
Requery()
**messagebox("  REQUERY()  = "+STR(REQUERY(),10,2))
thisform.Grid1.Refresh()

DON'T KEEP REOPENING THE VIEW! Every time you USE the view, it gets closed and then opened again. That causes your grid deconstruction problem. Open it once, in the DataEnvironment for example, and LEAVE IT ALONE. If you use the DE, be sure to set NODATAONLOAD=.t. in the property sheet.

Set it and forget it.

As Olaf says, don't use form properties as view parameters. Among other things, you'll never be able to open that view unless there's a form running with those properties. It can be useful to open it in code or from the command window, particularly while you're trying to figure out how it works. That's why in my first example, above, I created variables before the requery().

 

[Dan Freeman]

Oh, and just a comment on naming conventions. You appear to be using a version of Reverse Hungarian where the first two bytes of a memory variable's name are used for scope and data type. (i.e. lcFirstName = local, character, FirstName.)

Form properties ARE NOT memory variables. They are scoped to the form. So generally, that first byte is lopped off. It's meaningless (and misleading). Whenever I see something like:

?thisform.lcFirstName

I always pause to mentally translate "wait, this isn't a variable". This will cause confusion going forward, particularly once you get into using WITH/ENDWITH and drop the explicit Thisform reference:

WITH Thisform
   .lcFirstName = "John"
   .lcLastName = "Smith"
ENDWITH

At a quick glance, what are those? Variables or properties? Will it still be obvious as the code gets more complex?

Diverging from the convention actually creates long-lasting headaches. I work regularly with a huge invoicing application that uses a variable named lcIMG. That's character, right? NOPE! It's a boolean. I'd change the name but it's used in untold numbers of report objects as a PRINT WHEN condition, among other uses. There's way too much room for error to make that one small fix. Someone 15 years ago wasn't PAYING ATTENTION and I pay the price every time I stumble over it.