In grid, select newly added row through UX upon entering

[Davidk]

Re: In grid, select newly added row through UX upon entering

Well, that's cool.

[WindForce]

Re: In grid, select newly added row through UX upon entering

David
Besides the usual huge "THANK YOU", i could swear it's Michael Franks talking in your videos...Great singer with an unusual voice and music style, check him out in youtube. If you didn't know him before then there, a little payback :-)

Jaime

[GGandhi]

Re: In grid, select newly added row through UX upon entering

david that was excellent.

[Davidk]

Re: In grid, select newly added row through UX upon entering

I don't like looping if it's not required... this is much better.

This uses SQL to get the position of the known id in a sorted set. The SQL statement returns just 1 record.

function getRecIdPosition as c (e as p)
	
'debug(1)
	
dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments

flag = cn.open("::Name::Test")

dim newInvId as n = val(e._varInvId)

if flag then

	args.set("argsRecId",newInvId)
	sqlStmt = "SELECT InvoiceId, recPos FROM (SELECT t.InvoiceId,@rownum := @rownum + 1 AS recPos FROM Invoices t, (SELECT @rownum := 0) r ORDER BY InvoiceDate ASC ) `selection` WHERE InvoiceId=:argsRecId"

	flag = cn.Execute(sqlStmt,args)
		if flag then
			dim rsflag as l
			dim invId as n
			dim recIdPos as n = 0
			dim newRecIdPos as n = 0
			rs = cn.ResultSet
			newRecIdPos = rs.data("recPos")
			if newRecIdPos > 0 then
				getRecIdPosition = "setNewRecIdPosition(" + newRecIdPos + ");"
			else
				getRecIdPosition = "alert('new record id not found');"
			end if
		else
			'there was an error - close the connection and exit
			cn.Close()
			dim msg as c 
			msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
			msg = js_escape(msg)
			dim jscmd as c 
			jscmd = "alert('" + msg + "');"
			getRecIdPosition = jscmd
			exit function
		end if  
else
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	getRecIdPosition = jscmd
	exit function
end if

cn.FreeResult()
cn.close()
	
end function

[Davidk]

Re: In grid, select newly added row through UX upon entering

There we go... should be good now.

[GGandhi]

Re: In grid, select newly added row through UX upon entering

is your video private?

[Davidk]

Re: In grid, select newly added row through UX upon entering

See if this helps...

http://www.youtube.com/watch?v=bYGIJu1Bqe0

Components attached. Again... they won't run for you... my connections etc... but the code is there.

[Davidk]

Re: In grid, select newly added row through UX upon entering

Jaime,

How is your "Add" UX set up?
Bound?
With a Submit button?
Server-side event - AfterDialogValidate - ServerSideAction - Save Submitted Data to Tables? With what "After submit action" - edit record just submitted?
How is the UX Closed?

[GGandhi]

Re: In grid, select newly added row through UX upon entering

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

this is from MySQL. so your approach to the last inserted id is probably the best way to do, and looks like even with multiple access you will be granted your own auto increment value regardless of the timing. learned something new today.

[WindForce]

Re: In grid, select newly added row through UX upon entering

Thanks! I thought about last_insert_id() but was afraid about issues in multiuser environments...in this case they willl be at most 20-25 concurrent users but over the years i saw cases where people do save at the same exact millisecond...
Is there a way to grab that id in the UX itself after saving the record but before closing?

[Davidk]

Re: In grid, select newly added row through UX upon entering

Hi David
The behavior in the screencast is what i want, but as i stated later, i cannot alter the grid sorting ( gandhis method is to sort by id descending so the newly added id will be on top)...i already have code in this grid (thanks to you) to jump to specific dates by calculating the correct page, but in this case i do not know what to do to get the newly generated id, so i could then retrieve the date from that reservation and calculate the page with the code i have

That why I was asking about autoinc, timestamp, etc. You could use the same technique as jumping to a date. In a SQL Select, grab the latest record using one of several processes... either LAST_INSERT_ID() (which should be ok, but you should be aware of it's nature), or using a timestamp field to return a single record... DESC order, LIMIT 1. Once you have the record Id, use the same technique as with the date to go to the page and record.

You have all the code required. You just need to decide how you want to get at the last record entered.

[-Jinx-]

Re: In grid, select newly added row through UX upon entering

I'm not familar with MySQL as I use MSSQL exclusively at the moment but could you use the MySQL command: LAST_INSERT_ID()
to find what you're looking for? If it was run immediately after inserting there should be very little chance of someone else putting in a different one at the same time.

[WindForce]

Re: In grid, select newly added row through UX upon entering

Hi David
The behavior in the screencast is what i want, but as i stated later, i cannot alter the grid sorting ( gandhis method is to sort by id descending so the newly added id will be on top)...i already have code in this grid (thanks to you) to jump to specific dates by calculating the correct page, but in this case i do not know what to do to get the newly generated id, so i could then retrieve the date from that reservation and calculate the page with the code i have

[Davidk]

Re: In grid, select newly added row through UX upon entering

sorry david, I thought you saw the screencast I posted just before your post in post #10.
my apologies.

Yes... I saw the screencast. I must have misunderstood the need. The screencast shows a grid... then a UX for a new record is displayed. In the background the grid is re-sorted in DESC order based on Invoice Number and is on Page 1 of 4 pages. The new record gets added to the bottom of Page 1... so that the Invoice #'s read 33,32,31...24,34. The screencast is great but there's no solution posted.

Jaime replys that this is what he wants... so that's excellent... I just didn't understand the question.

[GGandhi]

Re: In grid, select newly added row through UX upon entering

That's a great idea and simple to implement, but it won't do it in this case, i cannot alter the current sorting (by dates)...i am afraid i somehow have to retrieve the newly generated key, then somehow calculate the page and go to it before setting focus, that's the reason David asked those questions :-)

I understand, going to the primary key or the timestamp will probably be the better idea.
the sorting happens only at that time it does not change the way the grid works. something similar to clicking the column header it does not actually change the grid definition.

let me know if you still want to see the way did it.