record number in grid not table record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 06:32 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. RE: record number in grid not table Olaf Doschke (Programmer) 22 Apr 17 07:04 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. RE: record number in grid not table Mike Lewis (Programmer) 22 Apr 17 08:02 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 RE: record number in grid not table Olaf Doschke (Programmer) 22 Apr 17 09:34 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. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 10:08 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. RE: record number in grid not table Olaf Doschke (Programmer) 22 Apr 17 11:06 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. RE: record number in grid not table Olaf Doschke (Programmer) 22 Apr 17 11:41 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. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 11:46 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. RE: record number in grid not table Olaf Doschke (Programmer) 22 Apr 17 11:57 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. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 13:16 Thank you dear Olaf. I found my answer. Quote (Olaf)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 " RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 13:34 Is it possible to make a field for automatic sequence 1, 2, 3, 4, 5 and ..... with which filter? RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 13:52 i found it Quote (MikeLewis)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 RE: record number in grid not table Olaf Doschke (Programmer) 22 Apr 17 14:45 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. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 17:20 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. RE: record number in grid not table Olaf Doschke (Programmer) 22 Apr 17 17:26 >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. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 17:48 ok dear Olaf.Can you tell me how to implement the steps mentioned by Mike in detail. RE: record number in grid not table Olaf Doschke (Programmer) 22 Apr 17 17:56 Really? CODEi=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; CODEscan endscan 2. each time round the loop, replace the field with an integer; CODEscan replace thefield with aninteger endscan 3. start the integer at zero, ... CODEaninteger=0 scan replace thefield with aninteger endscan 4. ...and increment it by one each time round the loop. CODEaninteger=0 scan aninteger=aninteger+1 replace thefield with aninteger endscan 5. Do that immediately after setting the index CODEset order to index 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. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 22 Apr 17 18:41 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. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 23 Apr 17 01:36 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. RE: record number in grid not table Olaf Doschke (Programmer) 23 Apr 17 06:20 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. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 23 Apr 17 09:02 Yes, you must include an empty field in the main table. The code is then executed. You are right, Olaf. As for the work of the any filter at any time or any index at any time, this can be done by putting this code into a Method that is called at any time and with anything. Greetings Dear Olaf. RE: record number in grid not table Olaf Doschke (Programmer) 23 Apr 17 09:40 >As for the work of the any filter at any time or any index at any time, this can be done by putting this code into a Method that is called at any time and with anything. That only solves the problem of not needing to write this code multiple times. This is a very usual thing to do. It doen't solve the performance problem you'll only ever see, if your or your customers data grows. It shows earlier than you think. And you can have that field in an extra table or cursor, just think about RELATIONs. But also that won't solve the performance and refresh problem. You get something, that works nice with perhaps even up to 10000s of records, and might think that's good enough. But things get ugly, if it begins to slow donw things, this will happen more suddenly than you think. Bye, Olaf. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 23 Apr 17 10:12 Quote (Olaf)That only solves the problem of not needing to write this code multiple times. This is a very usual thing to do. It doen't solve the performance problem you'll only ever see, if your or your customers data grows. It shows earlier than you think. And you can have that field in an extra table or cursor, just think about RELATIONs. But also that won't solve the performance and refresh problem.Does this also apply to the code? I think it will be more complicated and slow. In any case, if I need a way to deal with more than 10,000 records, I will definitely fill you with no doubt. I'm still very young in Fox programming and I'm still learning from you. This is a great honor for me. Bye, Ahmed. RE: record number in grid not table Olaf Doschke (Programmer) 23 Apr 17 11:53 >Does this also apply to the code? What do you mean with this question? Bye, Olaf. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 23 Apr 17 12:36 I mean this question (is your code will cause the same problems and slow for over 10000s of records as you said about my code. or not ?) RE: record number in grid not table Olaf Doschke (Programmer) 23 Apr 17 15:38 putting code into a function or method to be able to call it instead of having to rewrite it everywhere only lowers the code necessary to write or copy and is a good move about maintenance, only one place needs to be changed, if ever. That's code recycling, that's one major part of both procedural and oop programming. But how on earth would it speed things up? Or slow things down (essentially)? You still execute the same code. Bye, Olaf. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 23 Apr 17 17:50 Yes, you are always right, dear Olaf. Things can not be overstretched. Yes. Thank you very much, Professor Olaf. Bye, Ahmed. RE: record number in grid not table Olaf Doschke (Programmer) 23 Apr 17 18:41 I'm not enforcing anything upon you, I just give advice. If you just expect encouragement, you're asking the wrong guy. I just want to spare you the pain of learning, but sometimes everyone might need to make his own experience with something to believe. I am surely more experienced, you know that, but I'm also not perfect, noone is. That means there's neither a need to try to outsmart nor to praise me or us. You can take your ideas and what you learned and try to get as far as it takes you, you may be satisfied and never hit barriers I hit years ago, hardware goes faster all the time, but alone from the maintenance point of view anything you need to do to accompany some very basic native behaviour you can do with a single command like SET ORDER, that needs a magnitude more lines, may not be worth it. You can always argue code once written and proved to work is done and causes no further work anymore, it simply works and is a single call of a function or instanciation of a class as an object. But developing generations of code/libraries using previous generation code then just leads to unmaintainable heaps of code layers. So that thought does not hold true over longer time periods. So whether I am right with my pessimistic view or you manage to reach your goals will only really turn out several years from now. If you manage to realize all features you think of within the next few days will not prove anything, this still is not where I would talk of a success. Things have to work over a longer period of time, or maintenance of your first code will keep you from advancing, learning and doing more. This can really become a burden you can't dislodge easily aka maintenance nightmare. You make the impression to want to fly, when you even can't walk, and you're victim of something I see in many non computer savvy project managers, wanting a seeminlgy easy idea to be done, they saw elsewhere, and as this exampe shows this hasn't to be from a sci fi movie lilke a holo deck or a computer you can simply ask for something. Then on the other hand you can do things that make their jaws drop. It often is hard to explain why easy seeming things are hard and hard seeming problems are really easy to solve. You can do handwriting or even face recognition (using such a library or module) and several companies work on self driving cars, but SET DECIMALS TO 11 and ? VAL("75397.275") and you get 75397.27499999999, so computers can do stuff only humans can also do, maybe even better than humans, but they can't even convert a number in a string to the same numeric representation they can calculate with? Even though they are named computer? computing/calculation machines? Really? Yes, that's the way it is. Bye, Olaf. RE: record number in grid not table ameedoo3000 (IS/IT--Management) (OP) 23 Apr 17 19:44 yes.Absolutely. Bye, Ahmed.