That is an interesting idea. I'm not sure it's less overhead, though. The autonumber is performed once, then the number is always there for you to use. If you use the "select into" then every time you want to use it, it has to go to the table and count the number of records, which of course will not be indexed since you're using <=
Karen -----Original Message----- From: Bob Thompson <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wed, Jan 14, 2015 1:01 pm Subject: [RBASE-L] - Re: Property ... Search - Fast alternative Thanks. That seems that it might work as well and much less overhead. I will take a more in-depth look. Good idea. Bob Thompson Sent from my iPod On Jan 14, 2015, at 9:40 AM, Stephen Markson <[email protected]> wrote: Hello, Instead of using the AUTONUM column, could you use this? SELECT COUNT(*) INTO vRecNumber FROM BOMHeader WHERE Item<=.vItem Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]]On Behalf Of [email protected] Sent: January-12-15 10:03 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Property ... Search - Fast alternative I found a very fast and workable alternative to the "Property Search" question I had posted earlier. Perhaps it can be of use to others. To recap, I needed to be able to pull up a complex form, (not based on a grid or list view) and give the ability for a user to quickly move to any desired record in the table and from there be able to move forward or backward a row at a time in the order the form was called up in. In this case, I was dealing with Item Master and Bill of Material Master tables. Karen Tellef responded that she used the "Property ..jumpto" function with grids and suggested a method of possibly using a grid by sizing it to a single data field. However, when looking at the command, it does not reference any form control, but was a table command, so it had to work at the table level and I did not need to use a grid. So my solution was this. 1)Place an autonum column in the table. I named it Bom_Rec_No. Autonum the data using the ORDER BY clause. This number now corresponds to the internal record number in the Rbase table. AUTONUM Bom_Rec_no IN bomheader USING 1 1 order by item num 2)On my form, I placed a button with the eep that contained following core code. (I did not list all the error checking etc. here) ... ask for the desired item number, conduct various checks and error routines then Set var vRecNumber = Bom_Rec_No in BomHeader where item = .vitem ....other various checks and messages SET VAR vQuotes = (CVAL('QUOTES')) set var vSearch = ('Property Table BomHeader ' + .vQuotes ++ 'JumpTo ' + (CTXT(.vRecNumber)) + .vQuotes) --goto first record as this speeds up search Property Table BomHeader 'FIRST' --Jump to desired record &vSearch I call the form by Edit BOM_Master order by Item asc This puts the records in the same order as the autonum column and in the logical order for the user. When the user clicks the office button, they are asked for an item number, the form jumps directly to that item very quickly. They can then move forward or backward in the table. So they can enter item 750, it jumps to 750. The NEXT button goes to 751, 752, etc. etc. The PREV button goes to 749, 748 etc. etc., using the standard NEXT ROW / PREV ROW eep's. I placed the above AUTONUM command in the app that creates new Items or Bill of Materials. So that if items are added, they are properly numbered in item sequence. New Items and BoM's are added only occasionally, so the renumbering is not a big issue in my case. A nice feature might be for Rbase to allow the retrieval of the internal table record number, similar to the Grid's GetProperty function, but allow you to obtain it with a WHERE clause. This would eliminate the need for the AUTONUM column and coding above. Set var vRecNumber = RBTI_Table_RecordNum in tablename where column_name = .vValue Thanks Karen for a suggestion that led to a final solution! -Bob
