How to move one row at a time in a browse

[G Gabriel]

Re: How to move one row at a time in a browse

Hello to all:
All along I was responding to Oscar's quest for moving a row in a browse up or down, permanently. The comments made about sorting, while legitimate, unfortunately will not work.

What Oscar is attempting to do, as he explained, is to permanently move a row up or down the ranks of a browse so as similar items will line up together. You could say, why not just sort it? then shirts will be line up together then pants and so on?

Theoretically speaking, you could. Unfortunately, and this being one of the shortcomings of browses in alpha, that will work only if you are showing ALL rows in the browse.

If your browse is filtered and if you sort it based on any field in the embeded browse, it will sort, BUT, it will show ALL records in the browse, not just the filtered ones.

There is a way to show only the filtered records and sort them at the same time which require some xbasic, But simply sorting the records will not work if Oscar's browse is filtered somehow and I believe it is since it reflects orders by a certain customer.

As to Robin's other thread, I have not read it, I will take a look at it if I find it.

Thanks

[MoGrace]

Re: How to move one row at a time in a browse

Here's the other thread:
http://msgboard.alphasoftware.com/al...ad.php?t=62595

I have since modified the script I posted there and put an error trap before the fetch_prev() in the UP button. If the user tries to move above the first record the error trap redirects him to the last record in the browse instead. The DOWN button does the same at the last record but moves to the first record instead. The only part I would still like to change is that the cursor is seen moving back to the original record after it is changed instead of just appearing there. I could probably zip up the tables if anyone is interested.

The hardest part in trying to do this is to keep the records in the sort order that reflects the new position and put the cursor back on the record that was moved.

Peter's point about needing a sequentially numbered field is exactly correct. To do that in an invoicing app would be difficult as line items are added. In the model I am working on (Vending Machines), the rows are all added and numbered first and then the product data is copied into them. So switching them around by renumbering them is a bit easier.

Update: I just reread Stan' post about numbering the line items - that would give you the sequential field needed, providing is not a calc field. The button script could then renumber them similarly to what I did with the lanes. If Oscar would zip some tables maybe we could play with it ;-)

[G Gabriel]

Re: How to move one row at a time in a browse

Robin:
I only read the first paragraph of your thread, I did not read your code nor any of the responses as I am pressed for time, Sorry!

Help me out here: if you are at the very top record in the browse, why would you want to move it up? up where?

The only reason I could see a need for trapping BOF or EOF is to prevent accidental execution of the script.

At any rate, I resolved your issue as you see on the up button in the form in the attached zip file. You could repeat the same idea with the bottom button.

[G Gabriel]

Re: How to move one row at a time in a browse

The hardest part in trying to do this is to keep the records in the sort order that reflects the new position and put the cursor back on the record that was moved.

See the zip file attached. It does just that.

[MoGrace]

Re: How to move one row at a time in a browse

Pretty cool G., I tweaked it a little to work in a set but you have surely eliminated the problems I was having - thanks!

[Peter.Greulich]

Re: How to move one row at a time in a browse

Gabriel's method is very clever. But it relies on maintaining the table in record order and swapping the values of the records themselves. That's fine for a one field table, but if you have a hundred fields it can be problematic, and the minute you query or index - you're out of luck.

[Mike Wilson]

Re: How to move one row at a time in a browse

Oscar,

If it's a matter of sorting the invoice in an order that you wish, maybe something like the approach in the attached might work. Open the database, Invoice form will load. Push the red button. Choose the desired invoice, double click the rows in the order you wish it displayed (kinda like the arrange form field tab order).

Mike W

[G Gabriel]

Re: How to move one row at a time in a browse

Peter:
That's a good point, in fact I meant to indicate that if any one uses this technique, they must swap ALL FIELDS, otherwise you will end up with a mess.

As I mentioned earlier, there is a way to sort the embedded browse and show only the filtered records and that might be the way to do it if you have a large number of fields.

Alternatively, if you have a large number of fields, you could send all field values to a blob instead of variables and later import them from the blob.

All these issues should resolve, hopefully with the newly engineered browse.

[MoGrace]

Re: How to move one row at a time in a browse

Peter or Gabriel,
Please explain why switching values in the sort field between two records would require that ALL fields in those records be swapped as well. That doesn't seem necessary to me. This proposed script is only "moving" one record at a time.

[G Gabriel]

Re: How to move one row at a time in a browse

Robin:
What the script does is take the fields' values of the bottom row and assign them to the top row and vice a versa.

if the bottom record is: first name: John Last name: Smith
Top record: first name: Mark Last Name: Douglas

If you swab the last Name fields only, you will end up with John Douglas & Mark Smith !

[MoGrace]

Re: How to move one row at a time in a browse

Hi Gabriel,
I see what you mean in a scenario like that, but if a special field were added just to sort by, then it would not be a problem and the script could work more generically. Hence Stan's idea of line numbers and Peter's suggestion for some sequentially numbered field. This is what Oscar needs to add to his line items table when the record is added. The field could not be a calc field since its value must remain static. But a calculated value could be passed to the record when it is created. That's about as far as I have gotten in understanding how he might do it. Once each row has a number assigned then it can be swapped.

[oroma]

Re: How to move one row at a time in a browse

Gabrile: that last scrip works great, but how to refresh the screen as some times it will appears as one of the items got duplicated, and how will I keep the item I am moving higlited all the way during the moving.
Thanks

[G Gabriel]

Re: How to move one row at a time in a browse

Not exactly.
You could add a field to the table (more like a shadow field for recno) simply for the purpose of sorting but that, as I mentioned earlier, will not work since as I said, sorting the embedded browse with any field will show all the records. Furthermore, it's pretty cumbersome. for example, if you add a field say field1 to sort with and you have an embedded browse showing in field1 the following values:

112
121
159
165

and let's say you want to move the row that has 159 up in place of the one with 112. How are you going to do that?

You could run a find by key query, locate the one with 112 and assign it the number 165, then run a second query looking for 165, in which case there will be two of them! you could resolve that by giving the first record an out of range number like 999999, then find the one with 165 give it the new number of 112, go back to the one with 999999 and re-assign it the final number of 165. And after doing all of that, guess what, when you look at the browse, it will look the same. You might have to close and re-open the browse for the two rows to take their new positions. And since you will end up closing & re- opening the form, then you might as well forget about this newly added field, it serves no purpose, just close and e-open and assign a query script onActivate that will sort the embedded browse.

Alternatively, and I hope you are still following me, you could do a hybrid approach wherein you would only swab that field1 value (instead of swabbing the values of all the fields) as in my script, but then again, to see the final result, you would close & re-open which negate that approach.

Realistically speaking, if you have way too many fields, or even any number of fields, the best way par none is to use a blob variable.

I will see if I could cook up something along these lines.

[G Gabriel]

Re: How to move one row at a time in a browse

The above response was meant for Robin.
Oscar:
They are not duplicated, it's just two records with the same value.
How to keep the row highlighted?
Let me think about that.

[G Gabriel]

Re: How to move one row at a time in a browse

Oscar:
Here it is with the row being moved is highlighted.