Update data in a Cursor 2

[SitesMasstec]

Hello colleagues!

I have this form, with data in a Grid, with this command:

SELECT LOCALIZA, EMISSAO, CODAGENTE, NOMAGENTE, NOMECIA, NOMENAV, DATASAIDA, DOCUMENTO FROM RESERVAS;
   WHERE (EMISSAO>=XQualPDEmissao AND EMISSAO<=XQualUDEmissao) INTO CURSOR curReservas

So, the data in the Grid bellow is from the Cursor curReservas:



Is it possible to filter the data in the Grid (from Cursor curReservas) without having to select data from the original table again?

That is, I want just to display the data in the cursor according to a filter.

 

[Mike Lewis]

Well, you can set a filter on the cursor in the normal way:

SET FILTER TO <your filter condition here> IN curReservas

and then refresh the grid.

But it might be more efficient to regenerate the cursor. In other words, do the SELECT again, with the relevant data in the WHERE clause. And then refresh the grid.

Mike

 

[SitesMasstec]

Thanks, Mike, I got what I needed, with your help:

SET FILTER TO (EMISSAO>=XQualPDEmissao AND EMISSAO<=XQualUDEmissao) IN curReservas

I have to insert more options in the filter and I think there will be no problem.
Why is it more efficient to regenerate the cursor? (SELECT... FROM table ... INTO Cursor)

 

[SitesMasstec]

Well, sometimes it does not work...
Maybe I will have to regenerate the Cursor again, from table... as Mike had advised.

 

[Doug Lindauer]

Well, you can set a filter on the cursor in the normal way:

SET FILTER TO <your filter condition here> IN curReservas

and then refresh the grid.

But it might be more efficient to regenerate the cursor. In other words, do the SELECT again, with the relevant data in the WHERE clause. And then refresh the grid.

Mike

Mike, won't regenerating the cursor cause a problem with RecordSource of the grid? You've created a new object even if it has the same name. I've had problems when I've used the builder to create a grid and then change something even like a ControlSource for a column. It seems to blow up the whole grid so I will generally use filters and keep the same record source. (But I have to admit that I don't clearly understand the record source options other than alias and table.)

 

[SitesMasstec]

Mike is correct,
SET FILTER TO <your filter condition here> IN curReservas
indeed woks fine!

I had said in my above post "... sometimes it does not work" just because, after applying the filter, some lines in the Grid became invisible! If I scroll up the grid all (correctly filtered) rows appeared!

So, a new problem has arisen... how to make the first row (and some others after it) not to be (sometimes) invisible, without the user having to scroll up in the grid?

 

[SitesMasstec]

Well, it worked this way:

RowAtiva=thisform.Grid1.ActiveRow        && Record position in the Grid
RowRelativa=thisform.Grid1.RelativeRow   && Showed visible position in the Grid
IF (RowAtiva-RowRelativa)>0
    FOR AVANCAR=1 TO (RowAtiva-RowRelativa)
        thisform.Grid1.DoScroll(0)
    NEXT AVANCAR
ENDIF

 

[Pieter Koemans]

When working with large datasets, using a SELECT SQL statement is generally much faster than relying on SET FILTER. This is because SQL queries allow you to fetch only the necessary data, while SET FILTER still scans the entire dataset, which can be slow.

To prevent altering or "blowing up" the grid's layout (especially if it's designed with GridBuilder or manually), you can safely update the data by following these steps:

1. Select the new data into a different cursor:Instead of directly modifying the existing cursor that the grid is bound to, use a SELECT SQL statement to create a temporary cursor holding the filtered or new data:
2. SELECT * FROM yourTable WHERE yourCondition INTO CURSOR tempCursor
3. SELECT originalCursor
4. zap
5. APPEND FROM DBF('tempCursor')

 

[Mike Lewis]

won't regenerating the cursor cause a problem with RecordSource of the grid?

You're quite right, Doug. When you SELECT ... INTO CURSOR <cursor name>, the existing cursor is destroyed before the new one is created. When that happens, the grid's "knowledge" of its columns is also destroyed, which means the grid can't display the new data.

The solution is to set the RecordSource to an empty string before you do the SELECT, and then to set it back to the cursor name afterwards.

Why is it more efficient to regenerate the cursor?

In this case, it probably won't make much difference. But,in general, if the cursor is particularly large (tens of thousands of rows?), populating a grid from a filtered cursor can be slow. I'm not sure why.

Mike

 

[mjcmkrsr]

Did you consider using a parametrized/updatable view? Demo code attached.

MarK

 

[mjcmkrsr]

Please do also have a look at the BETWEEN() function

instead of

(EMISSAO>=XQualPDEmissao AND EMISSAO<=XQualUDEmissao)

try

BETWEEN(Emissao, xqualpdemissao, xqualudemissao)

MarK

 

[EinTerraner]

use in curReservas
...grid.RecordSource = ""

You need to assign the recordsource before closing the table

    .SrcAlias = "RESERVAS"
    .DisAlias = "CurRESERVAS"

    IF USED(.RecordSource)
        Lc_Sort = ORDER(.RecordSource)
    ENDIF

    .RecordSource = SPACE(10)
* either --> Clear Table, keep IndexTags
    SET SAFETY OFF
    ZAP IN (.DisAlias)
    SET SAFETY ON
    INSERT INTO (.DisAlias) ;
        SELECT * FROM (.SrcAlias) ;
        WHERE BETWEEN(EMISSAO, XQualPDEmissao, XQualUDEmissao)

* or  -->
    SELECT * FROM (.SrcAlias) ;
        INTO CURSOR (.DisAlias) READWRITE ;
        WHERE BETWEEN(EMISSAO, XQualPDEmissao, XQualUDEmissao)

    .RecordSource = .DisAlias
    SET ORDER TO Lc_Sort IN (.RecordSource)

If not, all your grid-settings like ColumnX.Controlsource will be disrupted

Long time ago WOODY mentioned to set the RecordSource to a cpl Spaces instead an 0-Byte-String to avoid unpleasant surprises.

 

[SitesMasstec]

Following Pieter suggestion, I tested this:

SELECT LOCALIZA, EMISSAO, CODAGENTE, NOMAGENTE, NOMECIA, NOMENAV, DATASAIDA, DOCUMENTO FROM RESERVAS WHERE ;
 (EMISSAO>=XQualPDEmissao AND EMISSAO<=XQualUDEmissao) AND ;
 (XQualCia="" OR XQualCia=ALLTRIM(NOMECIA)) AND ;
 (XQualNav="" OR XQualNav=ALLTRIM(NOMENAV)) ;
 INTO CURSOR tempCursor 
 
 SELECT curReservas
 ZAP
 APPEND FROM DBF('tempCursor')

It only selects the records between the dates (first: XQualPDEmissao and last:XQualUDEmissao). The records for the dates (Data Emissão) are correct.
If I choose something for XQualCia (trimmed) it does not obey, all records only within the dates are displayed.


The result should be just the 2 rows marked above ("NORWEGIAN").

 

[mjcmkrsr]

Hi

The result should be just the 2 rows marked above ("NORWEGIAN").

NO - since the two filter conditions

(XQualCia="" OR XQualCia=ALLTRIM(NOMECIA))

(XQualNav="" OR XQualNav=ALLTRIM(NOMENAV))

are always TRUE - think about it : you set xqualcia and xqualnav either empty or NOT empty which doesn't make sense

MarK

 

[igorsandler]

Thanks, Mike, I got what I needed, with your help:

SET FILTER TO (EMISSAO>=XQualPDEmissao AND EMISSAO<=XQualUDEmissao) IN curReservas

I have to insert more options in the filter and I think there will be no problem.
Why is it more efficient to regenerate the cursor? (SELECT... FROM table ... INTO Cursor)

Hello,
I agree with Mike that it's much better to recreate the cursor based on the different selections. We have the same forms with grids driven by the view and we requery the view is the user selects different prompts. SET FILTER hides the rest of the records and you can't see those records in case you need to debug it. Recreating the cursor gives you exactly what you need and unless you have hundreds of thousands of records, recreating the cursor takes a second. Like I said, we prefer the view, using Rushmore optimization. We also using Mortals Framework, works like a champ

 

[SitesMasstec]

Hello, Mark.

NOMECIA and NOMENAV are fields in the Cursor curReservas.
XQualCia and XQualNav are variables that keeps options.

For example, if I left a combobox without choosing a company, XQualCia will be equal to "".
I think that if choose "NORWEGIAN", only records which has NORWEGIAN in the field NOMECIA should be shown. But I am wrong...

Maybe I will have to have some lines of code instead of just one SELECT command.

 

[mjcmkrsr]

Hi

Maybe I will have to have some lines of code instead of just one SELECT command.

No, you need correctly coded filter conditions - see below

SELECT LOCALIZA, EMISSAO, CODAGENTE, NOMAGENTE, NOMECIA, NOMENAV, DATASAIDA, DOCUMENTO ;
FROM RESERVAS WHERE ;
BETWEEN(EMISSAO, XQualPDEmissao, XQualUDEmissao) AND ;
IIF(EMPTY(XQualCia), .T. , NOMECIA =ALLTRIM(XQualCia) ) AND ;
IIF(EMPTY(XQualNav), .T. , NOMENAV =ALLTRIM(XQualNav) ) ;
INTO CURSOR tempCursor

hth

MarK

p.s. some day you may want to have a closer look at the VFP functions

 

[SitesMasstec]

Great, Mark! It works now!
I have never used the command IIF. I will dive into it now, to understand how to used it, and when to used it.
I have already seen examples using it (IIF) but have never paid attention to it. It is a powerful command.
Many thanks!

 

[EinTerraner]

I have never used the command IIF. I

Next level of IIF is ICASE... Check it out. Sometimes very helpful.
like here:

    .G_DELETED.DynamicCurrentControl = [ICASE(] + ;
                [!EMPTY(BEDPOSI) .and. BITTEST(BEDFLAG, FG_DelGrup), "LCK",] + ;
                [!EMPTY(BEDSUBP) .and. BITTEST(BEDFLAG, FG_DelPosi), "LCK",] + ;
                [BITTEST(BEDFLAG, FG_Deleted), "INS",] + ;
                ["DEL")]
    .SetAll("DynamicForeColor", [ICASE(BEDPOSI==Bed_ZwsPosi, darkgreen, BEDGRUP==Bed_SumGrup, darkblue, black)])

 

[SitesMasstec]

Ein: thank you. I will try to study the commands in your example in this life...
Indeed I have used ICASE / OTHERWISE in much simpler routines.