How do I add Up / Down sort buttons to grid or dialog repeating seciton?

[LukeS]

I would like to add up and down button or arrows to a grid component or a dialog repeating section that would allow a user to move a record up or down in the list. I am sorting the grid or repeating section by a unique id number automatically assigned when the item in added to an order guide database table. I would imagine this would work by just swapping the unique id number field of the record above or below the item in the grid or repeating section.

I would do a simple sort but I want to be able to allow the sales team, who will possibly be using this interface, to place the items in the order guide in the order they walk through there accounts stores which does not follow alphabetical or any other logical order. That is why I figgured using the id number would make the most sense. I have added the buttons to a grid but am trying to figure what java or xbasic code is needed to complete this function.

Thanks for any help

[LukeS]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

I have now created a field named "sorted_placement" in the order guide database table with a default value of 0 which I am using to sort by. This seems to make more sense and is probably easier to implement. So now all I need the down button to do is increment this field by one. I am getting stuck on how to handle the custom component needed on the button press. Should it be a server or client side script? I have done some basic programming but not really sure where to start here, can someone point me in the right direction.

[Clipper87]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

I would do an ajax callback upon clicking the button. The XBasic server side function can then alter the sorted_placement value & when finishing sending back the grid method "{grid.Object}.refresh()" to refresh the grid.
Are you using a SQL table of DBF ?

[LukeS]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

I am using a DBF database but that may change to mySQL in the future if we decide to use the application. Is there a similar example on the wiki or somewhere showing simple data changes to items in a grid? I really learn best by looking at examples. I was trying to find a central list of all the Xbasic API functions, I found the Xbasic explorer in the code editor but this is not available in the web custom component code editor.

Using a ajax callback as you said is nice because it will resort the grid after update but still need to see some code to get me moving in the right direction.

[Clipper87]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

See the videos about ajax callbacks; you'll need them all the time. Then simply add a button to the field list, in the onclick event assign ajax callback & create the xbasic function. A little experimenting will teach you a lot!

[LukeS]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Ah.. the video was under the version 10 video page, "User-defined Ajax Callbacks" #66 helped. I am able to pull the data from the grid using "e._currentRowDataNew.SORT_PLACEMENT" but I am having a hard time finding out how to store data to a DBF database table in Xbasic. I am trying to find where I can read up on how to use the "table.open", etc needed to write to a DBF table.

[Davidk]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Mis-read the post... you want to actually move the record... got to read slower.

[LukeS]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Is there a similar guide to this "Learning Xbasic - Using Xbasic with SQL Tables" but for DBF tables. That guide is excellent but only shows a few lines of code on how to interact with DBF or active linked SQL databases. Am I missing something here, is there documentation on how to interact with DFB tables in Xbasic?

[Clipper87]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Take a look at this video: it is an alternative way of doing what you intend to do http://screencast.com/t/oGazMPZw

[Davidk]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Luke, have a look at this and see if it helps. I did a similar thing to your request with an Access database and the concept is very close to where you're heading so I should be ok. I tried, at first, to do this simply... by using javascript, but everything happens too quickly. A change to data in a grid with a submit and refresh won't behave in order without setting timeouts it seems. So, do it with xbasic. Here we're going directly after the dbf table. The table I'm using has a two part key and a field for sorting. E.g.

RecId RecId2 SortId FName

1 1 10 David
2 1 20 Luke
3 1 30 Fred
4 2 10 Cathy
5 2 20 Percy
6 2 30 Ken

Let's say the grid is filtered on RecId2 and Sorted on SortId, so in the grid we'd see just the 1st 3 records as above.

The code grabs the current row you're on - let's say Row 1. So the RecId = 1 and RecId2 = 1, and increments SortId by 11, so now you'd have 21, 20, 30 in SortId

Next, go after the table again, but just for RecId2 = 1 - you want all 3 records in this query, and in SortId order. Starting at 10, update each record SortId so that you now have 10, 20 30 in SortId.

Close everything up in code, refresh the Grid, and point to the row you're working on. Same concept for moving a record up. A couple of things to watch out for are the 1st record and last record. If you're on the Fistand moving up or the last record and moving down then there's no need to change the Sort Id - so you can put some code in to check that part. In my tblTest, RecId is a character field - so I have quotes in the code to take care of the current row data. RecId2 is numeric, and SortId is numeric. Hope this helps get you closer.

function MoveDown as c (e as p)
	
Dim currRow as n
Dim sortCnt as n
dim tbl as P
dim qry as P
sortCnt = 10
tbl = table.open("tblTest")
query.filter = "RecId = '" + e._currentRowDataNew.RecId + "' .and. RecId2 = " + e._currentRowDataNew.RecId2
query.order = "SortId"
query.options = ""
qry = tbl.query_create()
tbl.fetch_first()
tbl.change_begin()
	tbl.SortId = tbl.SortId + 11
tbl.change_end()

query.order = "SortId"
query.filter = "RecId2 = " + e._currentRowDataNew.Recid2
query.options = ""
qry = tbl.query_create()
tbl.fetch_first()
while .not. tbl.fetch_eof()
	tbl.change_begin()
		tbl.SortId = sortCnt
		sortCnt = sortCnt + 10
	tbl.change_end()
	tbl.fetch_next()
end while
qry.drop()
tbl.query_detach()
tbl.close()

currRow = e._currentRow
currRow = currRow + 1
MoveDown = "{grid.Object}.refresh();setTimeout('{grid.Object}.setFocus(" + currRow + ")',500);"

end function

[LukeS]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

David thanks for the response, seeing other code really helps me learn a lot! After a lot of trial and error from reading up on different xBasic functions on the wiki I was able to get it working. For anyone else who is searching on how to do this, I will also post my code, hope it helps.

I have a few questions on your code:
1) I see you used a query command to filter the results to rows that matched RecID2, I used index_primary_put to filter only select Accunt_num rows. Is the query method more efficient?
2) I watched the video posted in Clipper87 post above and it seems you use the set timeout function when you edit the values on the client side grid via javascript and then call the submit button. What is the setTimeout passed out of your function used for?

I have a order guide table setup with the following fields:
Id - Auto generated integer
Account_Number
Product_Number - multiple product entries per account number
Sort_Placement - Used to determine where in the grid an item is placed, when a new product is added to the order guide grid the database rules just copy over the ID number to this field so that it is added to the end of the grid
Order_Qty - How many to order, get cleared once a order is finished.

ID  Account_num  Product_num  Sort_placement  Order_qty
0   1522         413          0               0
1   10762        414          2               0
2   10762        411          1               0
3   10762        415          3               0
4   10762        423          8               0
5   10762        126          6               0
6   1522         120          5               0
7   1522         123          7               0

The table has up and down buttons that pass in 1 or -1 into the the function respectively. With a little more modification the code could also handle move up or down by largert numbers, for example a button to move a record down by 10. The Xbasic code swaps the value of the Sort_placement field in the current record with the target record. This effectively moves a record up or down.

function OrderGuideItemMove as c (e as p)
	'Variables Passed in
	dim MoveBy as I
	MoveBy = val(e.moveBy)
	
	dim MoveBy as I
	dim RecordNum as I
	dim tbl as p
	dim tbl_lookup as p
	
	'Assign RecordNum (key) of current row record to integer varible, "e._currentRowKeys[1]" is of type C
	RecordNum = e._currentRowKeys[1]
	
	tbl = table.open("order_guide")
	tbl_lookup = table.open("order_guide")
	
	'Goto current record of current row
	tbl.fetch_goto(RecordNum)
	
	'### Set lookup table to current row, then sort\index by the Sort_Placement column, then move to correct record for sort ###
	dim AccountNum as I
	tbl_lookup.fetch_goto(RecordNum)
	tbl_lookup.index_primary_put("Sort_placement")
	AccountNum = val(e._currentRowDataNew.ACCOUNT_NUM)
	
	'If down button is pressed get pointer to record on row below
	if (MoveBy > 0) then
		tbl_lookup.fetch_loc_next(e._currentRowDataNew.ACCOUNT_NUM)
	end if
	
	'If up button is pressed get pointer to record on row above
	if (MoveBy < 0) then
		tbl_lookup.fetch_loc_prev(e._currentRowDataNew.ACCOUNT_NUM)
	end if

	'### Swap Sort_Placement values of two rows to move record ###
	dim Primary_Sort_Value as I
	dim Lookup_Sort_Value as I
	
	Primary_Sort_Value = tbl.sort_placement
	Lookup_Sort_Value = tbl_lookup.sort_placement

	tbl.change_begin()
	tbl.Sort_Placement = Lookup_Sort_Value
	tbl.change_end(.t.)
	
	tbl_lookup.change_begin()
	tbl_lookup.Sort_Placement = Primary_Sort_Value
	'debug(1)
	tbl_lookup.change_end(.t.)
	
	tbl.close()
	tbl_lookup.close()
	
	'debug(1)
	OrderGuideItemMove = "{grid.Object}.refresh();"

end function

I am also really interested in your attempt to this on the client side via javascript. The one problem with how I am doing it on the server side is it is pretty slow to send it off the the server then wait for the grid to refresh. It would be ideal to do all the moves on the client side then click the submit button to save all the changes to the server table.

[LukeS]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Take a look at this video: it is an alternative way of doing what you intend to do http://screencast.com/t/oGazMPZw

This is really interesting, this may allows me to simplify things a bit and just pass the new values to the server with a submit.

[Davidk]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Query or Index... either is ok. Given the size of the table, Index may yield faster results.
The setTimeout is used to delay a javascript command. Stuff happens too quickly in javascript at times and we need to allow sufficient time for the refresh to happen before we highlight the correct row.

[Davidk]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Here is a javascript version, but I'm not crazy about it. This just moves a record down.

On a button put this code:

var rowNum={Grid.rowNumber};
var newSort=0;
var oldSort=0;
var cntSort=11;
oldSort = Math.abs({grid.Object}.getValue('G','SORTID',rowNum));
newSort = oldSort + cntSort;
{grid.Object}.setValue('G','SORTID',rowNum,newSort);
{grid.Object}.setFocus(rowNum);
{grid.Object}.submitGridPartRow(rowNum);

setTimeout('{grid.Object}.refresh();',1000);

setTimeout('resetSort();',2000);

Create a Javascript function:

function resetSort()
{
	var setSort=10;
	var rowNum = {Grid.Object}._selectedRow;
	while (rowNum<4)
	{
	   {grid.Object}.setValue('G','SORTID',rowNum,setSort);
	   setSort = setSort + 10;
	   rowNum = rowNum + 1;	  
	}
   	{grid.Object}.submitGridPart();
}

In order to get the timings right, I had to split up the code, and put the second "re-numbering" part into a separate function, and call that function using setTimeout. I have to change it a bit because right now I'm only looking after 3 rows - got to get the grid row count in there.

[LukeS]

Re: How do I add Up / Down sort buttons to grid or dialog repeating seciton?

Query or Index... either is ok. Given the size of the table, Index may yield faster results.
The setTimeout is used to delay a javascript command. Stuff happens too quickly in javascript at times and we need to allow sufficient time for the refresh to happen before we highlight the correct row.

The table I showed is just a few entry test table the real table if implemented would be about 8,000. I was reading up on query in the wiki, it may be faster over a index because a query is loaded once and then remains static until refresh, where an index updates on every table update.

Ahh okay, the set timeout in your code is just to delay after the refresh to allow the grid to render before setting focus.

I was looking through your code in post #14. It seems like you are doing a grid refresh and repopulating the whole grid from the server on every item move. At that point I would rather just do it in server-side xBasic.