Help needed to copy a new record in a sql grid with a detail view

[HRSue]

Hi again! I just posted a question and since I received such quick responses, I thought I would try again with another item I am having difficulty with.

I have a sql grid that has a detail view. When I click on 'New Record' on the grid then the detail view pops up and I am able to enter the new record information. However, I need to be able to click on a row within the grid and copy that line then edit it in detail view. I tried following videos, looked in the forum, googled questions but I must not be asking right because I only see dialog "stuff".

Can anyone tell me how I can create a copy record (or point me to the video that isn't about a dialog) button or something?

Thank you!
Sue

[-Jinx-]

Re: Help needed to copy a new record in a sql grid with a detail view

If you want to copy an entire record and save it to a new id I think you'll have to write code for it. I'm personally not sure of another way to do it. If you haven't written this before it may seem a little daunting but once you've done it a couple times it's not too bad. If you'd like I can show you an example where I did that.

[-Jinx-]

Re: Help needed to copy a new record in a sql grid with a detail view

Oh, and check this out if you haven't already:
http://wiki.alphasoftware.com/Learni...ith+SQL+Tables

[HRSue]

Re: Help needed to copy a new record in a sql grid with a detail view

Awesome! Thanks AH - I will check that out for sure! If you have an example of what you did, I'd greatly appreciate seeing it! Thanks again for your quick response!!!

[-Jinx-]

Re: Help needed to copy a new record in a sql grid with a detail view

OK, Here's an example. I was just going to copy and paste something I am actually using in here but they are all a bit more complex so I just wrote this code quick. So, it's untested and there may be some mistakes so please run it on some sample data until you get the bugs worked out. Reading through the link I posted will help you understand this better as well.

function CopyRecord as c (e as p)
'This is for javascript to be executed at the end of your function
dim vjscmd as c	
'Here we are getting the unique id from the row in the grid in which the button was pressed to copy the record
dim copyid as c = e._currentRowDataNew.youruniqueidcolumnname
dim cn as sql::Connection
	if cn.Open("::Name::Yourconnectionstringname") then
		'Here we are making the actual SQL command, any args (arguments see below) are put in with a colon in front of them.
		dim SQLCommand as c = "SELECT * FROM yourtablename WHERE youruniqueidcolumnname = :copyid"
		dim args as sql::Arguments
		'Here you're adding arguments.  the first value inside the double quotes is the argument name and the second is the arguments value
		args.add("copyid",copyid)
			'This is actually executing the SQL command along with the arguments.
			if cn.Execute(SQLCommand,args) then
				dim rs as SQL::ResultSet
             'rs contains all the results of our SQL command
				rs = cn.ResultSet
				'Now we are adding all of the data you want to copy to the new record to our arguments using the data that you called from the last SQL command.
				'when you read your result set (rs in this case) you can use rs.Data("columnname") or rs.Data(1) (for the first row, 2 for the second etc..) If you don't change your names I prefer method 1.
				args.add("columnname1",rs.Data("columnname1"))
				args.add("columnname2",rs.Data("columnname2"))
				'Keep going until you have everything you want to copy to the new record.  Don't include the unique id column, whatever that may be  We'll let the SQL DB set that.
				'After you are finished with all of your argument additions do something like this:
				SQLCommand = "INSERT INTO yourtable name (columnname1, columnname2) VALUES (:columnname1, :columnname2)"
				'Add aditional to the above as needed.
					if cn.Open("::Name::Yourconnectionstringname") then
						if cn.Execute(SQLCommand,args) then
							'Here I included a timeout of 1 second to give the SQL time to execute before refreshing the grid, you can experiment with it.
							'Also, you can add any other javascript you may want to execute after the function to this command as well.
							vjscmd = "setTimeout(function(){{grid.Object}.refresh()},1000);"
							'clear your argument values
							args.clear()
						else
						vjscmd = 	"alert('Execute Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"
						end if
					else vjscmd = "alert('Connection Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"
					end if
			else
				vjscmd = "alert('Execute Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"
			end if
	else
		vjscmd = "alert('Connection Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"		
	end if
'This send out the javascript
cn.Close()
CopyRecord = vjscmd

end function

When you copy it into your xbasic editor the colors should show up and make it more readable.

So, you'd put the above code in your Xbasic Funcitons for your grid and you'd create a button in your Grid fields and then edit the onClick Event. You could pick "Ajax Callback" and set the Callback type to InternalXbasicFunction. The function name in my example would be CopyRecord. Also, check the option for "Compute current row data".

You'll want to stick a debug switch "debug(1)" in the above code and run this in working preview as well to make sure the variables are coming in right.

[-Jinx-]

Re: Help needed to copy a new record in a sql grid with a detail view

I fixed the above, I think...haha 03/05/13

I just noticed that I have to put everything from "'Now we are adding all of the data you want to copy to the new record to our arguments using the data that you called from the last SQL command." to "vjscmd = "setTimeout(function(){{grid.Object}.refresh()},1000);""
inside of the second "if" nest.

I'll fixt it tomorrow.

[HRSue]

Re: Help needed to copy a new record in a sql grid with a detail view

Thank you for taking your time to help me with this! I'm going to ask someone more advanced in javascript/sql to read thru this with me. I appreciate your help. I was hoping for an easy button that had text already for me. I know that if I were to just copy a grid line to a grid it would be easier oh well....thanks again!!

[-Jinx-]

Re: Help needed to copy a new record in a sql grid with a detail view

Hi Sue,
There may be an easier way, that is, if I misunderstood what you want. For instance, it's pretty easy to make a button that copies a cell or two from one grid row to another using only Javascript. Still have to code a bit though.

What I wrote is actually mostly Xbasic and is done on the server side. JavaScript is used to call the Xbasic function and to return the end vjscmd. In any case, I wanted to use Alpha 5 as a dev platform where I didn't have to code but gave up on that idea after about two weeks of using it. Eventually, you're probably going to have to learn this stuff.

I'll fix the code above in a few minutes to correct my mistake. If you have any questions, feel free to ask. I was pretty much at the same point as you about 4 months ago and learned most of this stuff from the forum, Wiki, and videos.

You can change the SQL SELECT command and comment out the insert command. Then paste the code into a new a5w page or whatever and go to the interactive side like shown in the videos I sent. Then you can play without messing stuff up and actually see how the nuts and bolts work.

[HRSue]

Re: Help needed to copy a new record in a sql grid with a detail view

I appreciate it! What I want is to take a current row in the grid and copy the data from that row into a new row. Then click on the row and edit it with any updated info needed. So if a person has 15 position_id's to add they don't have to retype all that info. They can find a row similar to what they need, copy the data, open it in detail view and edit what needs to be edited, if anything. The only catch is now we have the Position_ID auto incremented using the sql server trigger on insert records. The Position_ID is the key field so it can't be blank. So how can I also copy the word of "AutoIncrement" in that Position_ID field so that the trigger will work? Make sense?

[-Jinx-]

Re: Help needed to copy a new record in a sql grid with a detail view

I THINK I fixed the above code now. Maybe it makes more sense too. I indented the different nest levels of the conditional branches. You wouldn't need to use the if-then-else branches for testing. They are in there for error checking but probably should be in the production version of your code.

[-Jinx-]

Re: Help needed to copy a new record in a sql grid with a detail view

I appreciate it! What I want is to take a current row in the grid and copy the data from that row into a new row. Then click on the row and edit it with any updated info needed. So if a person has 15 position_id's to add they don't have to retype all that info. They can find a row similar to what they need, copy the data, open it in detail view and edit what needs to be edited, if anything. The only catch is now we have the Position_ID auto incremented using the sql server trigger on insert records. The Position_ID is the key field so it can't be blank. So how can I also copy the word of "AutoIncrement" in that Position_ID field so that the trigger will work? Make sense?

If you go into your SQL database and set it up corrrectly like we talked about in the other thread there is no need for user to even see the Position_ID field. Your DB will take care of it. You don't leave it "blank" per say, you just hide it or set it to display:none; in the style. When you save a new record, even if you only enter one field, the SQL DB should assign the Position_ID "Automagically" with no help from Alpha.

I think, after reading your last post, you really do need some version of what I wrote above. If the above code looks Greek to you, just take some time to watch the videos in the link I provided and if you look further down on that page you can actually copy and paste code from that page into your interactive Xbasic and follow along directly with the videos.

If you have done any coding before a light will come on pretty quickly. My programming background is actually CNC machines and Robots!

[HRSue]

Re: Help needed to copy a new record in a sql grid with a detail view

After a week of trying to figure this out (I'm not a programmer at all), I gave up. I can't figure it out but thank you so much for trying to help me! On to the next....

[-Jinx-]

Re: Help needed to copy a new record in a sql grid with a detail view

I hate to see you give up. If you still want to work on this you can post a copy of your component and a create table script (or exact list of the fieldnames and types) and someone should be able to help you out.

Also, you can buy mentoring time from alpha and a real live person can walk you through while sharing a desktop view.

[HRSue]

Re: Help needed to copy a new record in a sql grid with a detail view

I appreciate your help and suggestions. I'll come back to this after I figure out why I'm getting script error and my detail view is not closing after I hit submit. It goes back to the first tab. I have a feeling it has to do with Internet Explorer 8 and alpha v11? No idea but now I'm getting stressed with alpha since I have to have this web page go live today I'll keep trying on this issue and let you know if/when I get it! Thanks again!!!!

[leiferikburoe]

Re: Help needed to copy a new record in a sql grid with a detail view

Hi
I don�t now if this is any help
Have a look at Video 72 Located in Version 10 Videos
BR
Erik