Dynamic Back Color

[Scott24x7]

Hi all,
I have a grid that I'm using (several actually) which show records available in different tables, and I put in the DynamicBackColor of the Column this:

IIF(MOD(RECNO(),2)=1,RGB(255,255,255),RGB(197,228,254))

All was well until records were populated out of order, and I put a Filter condition ion it. (COMPANY.COMPANYID = CONTACT.COMPANYID) or similar.

Then I realized that the filter condition is showing the dynamic color based on the MOD of Record number, but if they are "out of order" in the table, their RECNO doesn't represent where they actually are.

So the question is, has anyone a better source than using the RECNO() for determining which items to highlight. What this essentially does is highlight every other record (I don't use grid lines, as I don't like how they look).


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

Scott, the way I do this is to add an extra field to the record. This is an integer field, and contains a simple sequence number to indicate the position of the reocrd in the filtered or indexed cursor. Then use that new field, rather than RECNO(), in the MOD() function.

It's not a particularly elegant solution if you are using a physical table to populate the grid. But it's perfectly straightforward if you are creating a cursor to populate the grid, which is what I often do.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

Mike,
Actually that is exactly what I do for Code Tables.... except I just use a logical field, and set it to .T. or .F. after it's order has been set to what I want it. There is no filtering here though. (I have logic that sorts it all out and re-writes the values as they get entered, which is fine for a small table of say 100 records or less) but on a big table, that doesn't work... And I'm expecting some of these to reach 10,000's or 100,000s records...
Is there something at the "GRID" level while it's being filtered that can be used? Is there a way to get the number of row elements in an active grid? Then I could MOD(2) on that...


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

The problem is that there is no RowNumber property, or anything similar. The grid is essentially a collection of columns, not a collection of rows. You cannot get an object reference to a row. So your DaynamicBackColor property has to be based on something in the cursor, not in the grid.

You can use a logical field to determine the background colour (as you do), or you can use a sequence number (as I do). But ultimately you have to add something to the cursor to get this to work.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

Well, that explains why I built it the way I did 15 years ago...
I do recall now finding that was very annoying.
With the way you are doing it though, how do you determine which is to be highlighted?
Incremented values say 1 to 10 before the filter might show, say 2,4,6,7,9. If you MOD(2) that you still get the first 3 being on or off...


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

There is nothing simple for that matter. Mike has layed out a good way. You will need to repopulate the extra numeric or bool column every time you change order (index) or filter a cursor, simply scan..endscan
Even if you display a table or view you can't add a field to, you can always relate a secondary cursor with that extra field. You don't even need an index for that matter or a copied ID, you can relate the grid and the secondary cursor by recno(). The secondary cursor will be "scrambled" and filtered the same way, so the only condition on it is you give it the same RECCOUNT() number of records. You then need to scan through all records of the main grid cursor and set the fields, there is no shortcut.

The use case for dynamicbackcolor is not alternate coloring but coloring items needing highlight, eg paid vs unpaid bills, etc. and that's how it is designed, to define coloring in dependence of current record data only. RECNO() and even DELETED() also belongs to a current record, but RECNO() is physical position in the DBF and that does not change neither by index nor filter, as you know, so this only works for unsorted and unfiltered data, eg what you also get with a query INTO CURSOR, so anything you do with SQL instead of SET ORDER and SET FILTER has a new RECNO usable with the simple MOD expression, but has other disadvantages, eg quick sorting by header click is best done with SET ORDER instead of a query with ORDER BY, as SET ORDER only applies the travesing of the Index to visit records in index order. Everything has it's pros and cons in regard of ease of development and performance of use. In this case either the coloring via full scan of the data can take a long time or the query in the new order and where condition can take a long time, there is no instant solution in neither case for the coloring and so I skip doing that with anything that can get very lengthy. But if your displayed data always is short: It takes split seconds to scan even through tenthousands of records, also creating a secondary cursor with the same number of records. It can become detectable to the user, eg switching order by header clicks will not be instant. But requerying data even takes more time and has the problem of grid reconstruction, if you don't know how to do it safely.

It's much easier to alternately color lines of a report. Simply create a report variable as counter and set the printwhen condition of a grey background bar to MOD(counter,2)=1, but you can't define a grid variable. Grids are not populated top down like a reoprt is. In the initial case only the first visible reords are fetched. If you scroll the grid to some place in the middle this does not make VFP fetch all data between current row and the next portion to display, but VFP only fetches those rows. That's what makes a browse (which is a grid) so responsive and fast, even if your dbf has millions of records.

Bye, Olaf.

 

[Mike Lewis]

Scott, Olaf has answered that last question. In summary: you populate the count field after you set the filter or set the index order or whatever else you do to determine what you want the grid to show. It's very simple really.

Also, I should have mentioned the option of relating the existing table to a new table that exists specifically for this purpose. This is quite easy to do, and has the advantage that you don't need to mess around with the structure of the original table.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

Mike,
I don't quite follow you on the:

I should have mentioned the option of relating the existing table to a new table that exists specifically for this purpose.

Remember, I know just enough to be dangerous/annoying.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Well, as you said you already have such a construct

> that is exactly what I do for Code Tables.... except I just use a logical field, and set it to .T. or .F. after it's order has been set to what I want it. There is no filtering here though

So why ask?

If that extra logical fiels is part of the grid data you simply scan endscan and thereby go through all records in current ordering and filtering. Set the logical field alternatively and let it control the dynamicbackcolor.

If that extra field is part of an extra table you simply relate grid and this extra table.

Bye, Olaf.

 

[Scott24x7]

Because, as I mentioned, this works fine on code tables because they are small. I essentially re-write all the logicial fields every time a record is added, and there is no filter, only one index on the file.
That is completely unrealistic on a table that has a million records in it every time you just want to show a filtered grid...
So I was looking for another method. I'm irritated that the GRID gives no ability to know what it's contents are. I remember being irritated by this 15 years ago too...

And just a few days ago you say "If you don't know something ask". Now you say "Why do you ask???"
These kinds of comments are time wasting, and not helpful.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

For millions of record displaying a few filtered records of it is no good idea to start with. Let alone the scroll bar is useless in this scenario, at least not reflecting the real position and size of the data portion in view. I can't give you a milder irritation, but the grid just has one row of real controls, everything else is just drawn.

The problem is not milions of records, though, if they contain a field to make use, the problem is muti user. So you'd have that extra field on cursors only or an additional cursor.

There is some other way to have that additional cursor only have as much records as needed, if you relate by recno and store the recno of the grid table into the side cursor you get one record per record you really show and that should be less than millions.

Bye, Olaf.