record number in grid not table

[ameedoo3000]

hi all
how can i get the record number or row number in grid not table. recno() only get The record number is given in the table and not in the actual grid , especially after filtering.

 

[Olaf Doschke]

You have grid.activerow, but it is limited in only being available when the grid has focus and the active row is in the visible rows.
Forget about that aspect, you can't show the current row in index order considering a current filter setting.

The best thing is recno() of a cursor you select into with WHERE and ORDER BY, then you really have records copied in that order and filtering and recno() is in dislay order.

There are obvious cons, if you have lots of records and the filter is not removing many, such creation of a grid cursor takes much longer than SET ORDER TO And SET FILTER ..., but that's the way it is.

Bye, Olaf.

 

[Mike Lewis]

The short answer is that you cannot get the row number of a grid. For reasons that are too complicated to explain, rows do not exist as separate objects.

But you can get the record number within the underlying cursor or table. If the table has no filter, no controlling index, and no hidden deleted records, then the row number and the record number are the same.

Perhaps you could tell us why you need the row number. We might be able to suggest an alternative approach.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

In fact I'd stress out the best approach, even though of its con of eventually copying lots of data, is to query data into a separate cursor. Just becuase of one simple reason: A filtered table often causes ugly scrollbar looks and behaviour, especially if your filtered rows are few of very many records. Size of the bar will be tinier, the more records you have, even if filtered rows are far less, it considers the reccount(). and the position also is rather positioning for the overall position in the unfiltered data.

All that is due to the nature of a filter: it is not a query. As Mike says, too complicated to explain, but lets say you can't have major positive features of eg the grid or browse windows to immediately show records even of very large dbfs, without the cons of not knowing an absolute numbering of the filtered workarea. One reason is the filter is not just applied once throughout all records, but only starting at the top or current row and skipping through records only so far, as the grid/browse is filled. So the "performance" secret is not inexplicitly turbo of network fetch, but only fetching top 10 or so rows,until you scroll. And thus you can't have the positive sides of this without the negative sides.

Bye, Olaf.

 

[ameedoo3000]

For easy control and navigation in the grid as in the Access tables. Also go to a particular row by typing the number of this row even after the filter. Exactly as in the Access tables. However, it seems that this feature is not available in the Fox Pro.

I wish I could find a ready-made navigation bar that works in the grid of the Fox Pro like Access. I hope so.

 

[Olaf Doschke]

Well, Foxpro is not Access. I almost expected such a comparison to happen.

It's not really a big and useful feature of a grid to show "n of N". If you compare Access and VFP on that level, Access wins.

Bye, Olaf.

 

[Olaf Doschke]

If you insist, there is a general solution in scanning through all data once and creating a secondary table or cursor storing two values per row: the rownumber in display order and the recno(). Then for display lookup recno() and display the corresponding row number. It contradicts the advantage the VFP grid has to not need to scan all data to display the first few rows fitting the filter. And if you do so in a large table this takes long, maybe even longer than querying the data itself into a new table or cursor and be able to work with its recno() as row number.

In fact the best practice to store data in 4th normal form most of the time means data stored gets partitioned and listed in ways the end user doesn't profit, but data processing and quality (avoiding redundance for example) profits. Adn with that best practice in mind the best practice and only viable way to do the frontend of a database is to query data in the way you want it displayed anyway. Queries are not your enemy, but your friend in providing a translation from the best practice data storage to a user friendly display. When you store an address with a city and have put city names in one table and refer to them via cityid in an address table, the user wants to see the city name and not an id he has to lookup. SQLs job is to do that lookup and provide human readable data. Whenever you don't need SQL but have data in your DBFs ready for display but need to SET ORDER and SET FILTER, it's very likely you broke rules and best practices of data storage anyway.

So overall querying the data instead of making use of SET ORDER and SET FILTER has many good reasons for it. And solving the problem with some meta data about recno()/rownumber has quite another big impact in being necessary again and again, if you switch order or change filter, everytime you do something like that or delete a row, the meta data has to be managed to reflect the new situation. An ex boss of me discredited VFP for "not even being capable to do what's simple in Access". Well, you always have such details in which an overall inferior system is simpler. We ended up not having that feature, it's really dispensible, even if you now don't think so.

A similar problem is having alternating row colors in such situations the recno() is not in order because of index order and/or filter. The simple solution of a dynamicbackcolor depending on recno()%2 does only work out as long as recno() corresponds with row number and when that's not the case the effort to get there doesn't pay the benefit.

The moment you judge the vlaue of a software on features like this or centering of previews or any simple seeming visual outset you're lost and in terms of the pareto principle (80-20-rule), which may even have a worse ratio of 90:10 or 95: 5 have to put most effort in solving such nonsense problems. You can get stuck on this.

In the end you don't help somone working down a list of 100 items best by having a display what n of 100 he is working at, but by making the processing of each item as simple and efficient as possible.

Bye, Olaf.

 

[ameedoo3000]

The goal is not to compare dear Olaf. It is for larger, easier and faster control of the grid, as in the case of Access. For example, if you want to go to the middle of the records in grid , I know what the total number of records is and go to the any record number as well. This is the purpose of this feature. It is not the comparison. Whether the subject is a comparative topic, there are many features in Access that are not present in Fox Pro and vice versa. There are also many features in the Delphi not found in the Fox Pro and vice versa.
My goal is to achieve the highest features within the environment of the Fox Pro.I am 100 per cent confident of reaching my goal with your help and support.

 

[Olaf Doschke]

While you answered I edited my answer, so you should read it again. Anyway, I'm not eager to help aou with this nonsense, I have better things to do.

Navigate to the middle of the grid: Well, pull the bar into the middle of the scrollbar. Besides that to go to a certain row number is GO TOP and SKIP N-1. To have a total count COUNT TO var, that will only count all rows matching the filter, so that part of the feature can be done even without some meta data, but not having that display.

If I measure the simplicity of letting the grid display data via just setting the recordsource with the need to scan through data and maintain a meta data cursor, that ration is already shown in practice. It relativizes with the need to set single column controlsources, headers, etc. but in my application never was necessary and never was asked of any user of 1000s users in 15 years. So it is nonsense.

Bye, Olaf.

 

[ameedoo3000]

Thank you dear Olaf. I found my answer.

Navigate to the middle of the grid: Well, pull the bar into the middle of the scrollbar. Besides that to go to a certain row number is GO TOP and SKIP N-1. To have a total count COUNT TO var, that will only count all rows matching the filter, so that part of the feature can be done even without some meta data, but not having that display.

My questions may start or even below the level but I swear to you that I do this in order to make the working environment in the Fox Pro more compatible with the Access because there are many users who use this programs for them want to be the Fox in appearance (ostensibly) as MS Access So I try everything I can to give all the advantages of the Access in the Fox Pro to make it easy for users.This is at the request of users.My role in this phase is to scale them from ِAccess to Fox Pro.
As you notice, most of my posts and questions are related to what is in ِAccess and not found in the Fox Pro.For example, such Thread "insert checkbox in shortcut or menu "

 

[ameedoo3000]

Is it possible to make a field for automatic sequence 1, 2, 3, 4, 5 and ..... with which filter?

 

[ameedoo3000]

i found it

If, on the other hand, the table has an index in force, and you want to show a consecutive row number (rather than the underlying record number), then setting the ControlSource to RECNO() won't give you that. Instead, you should add a field to the table, and populate it with consecutive numbers; then show that field as usual in the grid.

To populate the field with consecutive numbers, write a loop; each time round the loop, replace the field with an integer; start the integer at zero, and increment it by one each time round the loop. Do that immediately after setting the index

 

[Olaf Doschke]

The way I put it was creating meta data in another table or cursor. Adding a field to the table bind to the grid will cause problems in multiple user environments, or even before thinking of that, changing a database table and adding fields for technical purposes only and not to persist data is a poor choice. I would always have a secondary local table or cursor for that. You can easily relate it to the original table and link it by RECNO(). That way your original tables and even original code can be kept untouched.

It still lacks the performance Access has with this, however its done behind the scenes, especially with larger data sets.

In regard of your screen shot of Excel, just because something pops up it isn't necessarily a shortcut menu or contet menu. This likely is a separate form, it has several sections a menu hasn't and surely does not remind me of a simple context menu. If you only thinik in such narrow terms, you get stuck in trying to implement something with a base class not at all intended for it.

Finally you seriously want to scale people from Access to FoxPro? You are aware, that Foxpro is discontinued? If I would not plan to go for providing services in a variety of databases and web development in PHP and maybe Python, which I began learning and liking, I could imagine the opposite move. Access still is continued and has gained many aspects over the years, that make it a good VFP replacement for several applications, if not also a better choice in some conditions. It has the same aspects of providing frontend and backend and be easier for beginners than .NET, even when you would choose to concentrate on Winform desktop applications in .NET.

Bye, Olaf.

 

[ameedoo3000]

I understand from this that there is no way to achieve my goal of creating another independent table?
Is this your last opinion?
To expand the knowledge of databases other than Fox Pro like Python or PHP. So far I will not be able to fully control the Fox Pro so I can look to other programs. If possible, this would be at the expense of leaving something at the expense of something else. Like a popular saying "the owner of the two interests is a liar."
I love to focus on one thing and to do it and do what I want from it.
By the way I'm liking Delphi such as you are liking Python perfectly.

 

[Olaf Doschke]

>I understand from this that there is no way to achieve my goal of creating another independent table?
No, that's not what I said, I said - partly also to Mike - that in contrast the total opposite is true, that an independent and local table, even simpler a cursor to hold recno/row number is better to not have multi user problems, changed database tables etc.

The problem is though, that syncing this data with whatever change otherwise very rapidly happening, like SET ORDER to another index, will always take much time, too much time, to be fluently working. So you better stop trying because of that aspect.

The other simple seeming example of alternate row coloring also shows you have to put much effort into to to arrive at a wirking, yet not satisfactorial solution and you don't find a solution that pays and is as rapid and fluent as the base behaviour of SET ORDER or FILTER is, so finally the user pays for this when his data grows, something, that also will not show up immediately to you, when you try things with low data volumes.

One very simple reason to not do something so compllicated, if it fails with higher data volumes is, that such a solution is not better than using SQL and so SQL already is the best solution to do instead of SET ORDER and SET FILTER and is simple to apply at the same time.

Bye, Olaf.

 

[ameedoo3000]

ok dear Olaf.Can you tell me how to implement the steps mentioned by Mike in detail.

 

[Olaf Doschke]

Really?

i=0
scan
   i=i+1
   replace something with i
endscan

Merely on the subject of understanding and implementing instructions, you fail to do the simplest things. If you can't follow a recipe how do think you cope in inventing recipes?

Doing it step by step in slow motion:

1. To populate the field with consecutive numbers, write a loop;

[highlight #FCE94F]scan
endscan[/highlight]

2. each time round the loop, replace the field with an integer;

scan
   [highlight #FCE94F]replace thefield with aninteger[/highlight]
endscan

3. start the integer at zero, ...

[highlight #FCE94F]aninteger=0[/highlight]
scan
   replace thefield with aninteger
endscan

4. ...and increment it by one each time round the loop.

aninteger=0
scan
   [highlight #FCE94F]aninteger=aninteger+1[/highlight]
   replace thefield with aninteger
endscan

5. Do that immediately after setting the index

[highlight #FCE94F]set order to index[/highlight]
aninteger=0
scan
   aninteger=aninteger+1
   replace thefield with aninteger
endscan

If you can't even do that, you disqualify to get to your goals. And don't expect now that this code runs as is, it has some prerequisites for working, mainly a table with a field named "thefield" capable to store an integer value.

Since Mike recommends thefield to be part of the table, you have a problem if two users watch the data in different order and each overwrites the others order. That problem can be solved by having thefield in a separate local table or cursor, but I won't go into the details.

What's so bad about this is that the SET ORDER TO really happens immediately, but the scan loop will take its time. If you have a few records, this will still be fine and seemingly fast. But if you have a complex filter condition, probably even not optimizable, then this will slow things down. When you SET FILTER TO, you don't do a query and VFP does not check all rows and marks them inside or outside of the filter condition, that condition is set means it will be checked every time you skip a row, every time you go forward or change current record in any way on that single record you are now at. And alone that aspect also means, if a row is edited in a way not matching the filter anymore, the row will disappear and so you will have a row number gap.

If you take this forward it means at least every time you leave a record you need to check, whether your numbering still is valid, not just at SET ORDER and SET FILTER changes. It doesn't work out, because your main task and the main thing users will need to wait for is for your loop to have renumbered the rows.

Bye, Olaf.

 

[ameedoo3000]

Unfortunately, the fox Pro seems more complex and difficult to compare with other programs. These steps explained may seem simple and few in other programs and even more streamlined and easy. Anyway thank you very much dear Olaf.

 

[ameedoo3000]

I found a solution faster and easier and without inserting a new field into the main table.
I inserted a field into the Grid in the form (new column)"column5".and In the click event on the button I wrote these simple lines.

SET FILTER TO
SET FILTER TO (any_filter)
set Order to (any index)
count to a
thisform.grid.SetFocus
GO TOP
FOR i = 1 TO a
thisform.grid.column5.text1.Value = transform(i)
SKIP
ENDFOR
GO TOP

many thanks for my dear Olaf and my dear mike Because of encouragement and motivation me.

 

[Olaf Doschke]

This won't work once you scroll. The grid does not consist of a column5.text1 textbox for every row, but one textbox that is drawn repeatedly. So you don't have N boxes to hold N values and once you scroll in the grid you'll see every row shows the last number only.

It's not the major problem anyway, when a row is deleted or its editing doesn't setisfy the current FILTER or changes its order, even if there would be a text1 per row you'd need to renumber that, too.

Bye, Olaf.