Creating a new record in another table.

[kmullins]

I am trying to create a link on a Grid Component that will copy some values from the row and then go create a new record in another Grid Component fill in the values and then link the new record number into the component that I started from.

I am trying to adopt something from the Web Applications Demo but that seems to only work if the record already exists in the target table.

For reference (/Web_Applications_Demo_V9/default/CustomerGridwithLink.a5w)

This is how A5 filled in the - Is Linked? field: Catalog_title={Donation_title},Catalog_description={Donation_details},Value={Value},Restrictions={Restrictions}

The final URL is:
catalog_items.a5w?catalog_items_link_1C=Catalog_title={Donation_title}&catalog_items_link_2M=Catalog_description={Donation_details}&catalog_items_link_3N=Value={Value}&catalog_items_link_4M=Restrictions={Restrictions}


Any help is greatly appreciated.

Thanks
Kevin Mullins

[drgarytraub]

Re: Creating a new record in another table.

The basic strategy that I would use is to have your link go to an a5w page that contains the xbasic to do whatever you want, and then the last line of that code would be a response.redirect back to whatever page you want.

Gary

[kmullins]

Re: Creating a new record in another table.

The basic strategy that I would use is to have your link go to an a5w page that contains the xbasic to do whatever you want, and then the last line of that code would be a response.redirect back to whatever page you want.

Gary

Thanks Gary. I was thinking I was going to have to go in that direction. Now that I am, there are some steps that I have to believe are very easy that I can't seem to find any examples to help me.

I have the code to pass some variables into a new record. I can't figure out how I would get the auto created key for the record I just submitted. Here is the code I have thus far.

t = table.open("[PathAlias.ADB_Path]\catalog_items")
t.enter_begin()

	if eval_valid("Session.Title")
		t.Catalog_title = alltrim(Session.Title)
	Else
	end if
	
	if eval_valid("Session.Description")	
		t.Catalog_description = alltrim(Session.Description)
	Else
	end if
	
	if eval_valid("Session.Restrictions")		
		t.Restrictions = alltrim(Session.Restrictions)
	Else
	end if	
	
t.enter_end(.t.)

Thanks again
Kevin

[kmullins]

Re: Creating a new record in another table.

I thought I might add a little more about what I am trying to do because I might be making it more difficult than it needs to be.

I want to be on a record in a Grid, push a button or link or something that takes the values from that record, creates a new record in a parent table, writes the values it captured into the new record in the table and then returns the Catalog id that was created and right it to the original record I was on so that the two records are linked together by that ID.

Any insight is appreciated.
Thanks
Kevin Mullins

[kmullins]

Re: Creating a new record in another table.

Ok, I have been working on this all day in the hopes of solving my problem. I think I am close. However I can't seem to figure out how to get back to the record I stated on to update it. Attached is what I have so far, this is the code contained in an A5W page that a grid link calls from the donations grid and passes some session variables, including the original donation_id.

<%a5
DIM Session.CatalogID as C

t = table.open("[PathAlias.ADB_Path]\catalog_items")
t.enter_begin()

	if eval_valid("Session.Title")
		t.Catalog_title = alltrim(Session.Title)
	Else
	end if
	
	if eval_valid("Session.Description")	
		t.Catalog_description = alltrim(Session.Description)
	Else
	end if
	
	if eval_valid("Session.Restrictions")		
		t.Restrictions = alltrim(Session.Restrictions)
	Else
	end if	
	
t.enter_end(.t.)
Session.CatalogID = t.Catalog_id.value_get()

t.close()

dim tbl as P
dim qry as P

tbl = table.open("[PathAlias.ADB_Path]\donations")

query.filter = "Donation_id = 'session.DonationID'"

qry = tbl.query_create()


    tbl.change_begin(.t.)

    tbl.Catalog_id = Session.CatalogID

    tbl.change_end(.t.)

tbl.query_detach()
tbl.close()

%>

My filter didn't work because it updated the first Donation_ID record not the one the session.DonationID references.

Thanks in advance for any help anyone might be able to provide.

Kevin Mullins

[Steve Wood]

Re: Creating a new record in another table.

I'd like to suggest a different approach. But first, tell me, do you always have just one record in this grid that will have the button or link? And where do all of those session variables come from? If you have more than one grid record, and those session variables are supposed to represent that records value, how and when do you populate them?

[kmullins]

Re: Creating a new record in another table.

Thanks Steve I would love any other suggestions you have. This is a work flow I am creating for someone to build a catalog for a school auction. Donations are entered on the donation page with information about the item. From the collection of donations you then combine them together as Catalog items. Catalog_items is one to many to donations (with Catalog_id as the key field). Most of the time a catalog_item is only one donation item, so I am trying to make it easier to create the catalog item by pulling forward the Title, Description, restrictions. They are only point in time references because they might be changed for the Catalog_item print out.

Attached is the donation grid, and the properties of the donation grid. It links to the A5W page that has the code entered above and pushes forward the session variables not declared in the above code. I need to create the Catalog_id to be able to tie the donation item up to it with the catalog_id value. Ultimately I would then take the use to a new A5W page that is a Grid Link of the Catalog_item page and the donations grid so they could add additional donation items to the newly created catalog_item.

Thanks for taking the time to help me out with this one.

Kevin Mullins

[Steve Wood]

Re: Creating a new record in another table.

OK, a refinement, not a different approach....

You don't need to make your variables session variables. The create_catalog.a5w will see them all as page variables, so just make them normal variables.

You don't need to pass anything other than the ID, although its OK the way you have it. The ID is the primary key for your table, so you could use it to pull over information using lookup().

Note - none of this will work unless the grid row is SAVED before firing the link. The values won't be available unless the record is saved.


You don't need to eva_valid() any of the variables, just DIM them and they will exist.

The first part of your script is fine, but no need for variables to be session. It would really be better if you only brought over the ID and used lookup() to get the other values. The primary reason is that your Title or Description could contain an & or other character which would blow your entire script; and you might run into limits on URL length. And DIM the vars, and don't use eval_valid(). No IF ELSE, just populate the table with all values. You can leave off ".value_get()". For web, you don't need the .t. in the enter_end() function.

For the second part of your script, scrap all of the query stuff and just use record_update() to update the appropriate record. TIP - you need to include the ".dbf" in the table name when using this function.

Your filter did not work because of syntax. Your single quotes around the variable made it a literal value, not the contents of the variable.

To get back to your original grid, put this at the bottom of your script:

response.redirect(Request.Referer)

Although you do need to use eval_vaild(request.referer) on this and provide a default redirect. In this script, request.referer should always exist, but since it is not persistent, you need to provide a fallback.

Now, having said all of that, this is a PERFECT opportunity to use AJAX. Let me know if you want to attempt that. It would run your script while leaving them on the same screen with not even a flicker.

Since I happen to have this up on my screen at the moment, to get you started, create a Custom field based on a dummy Calculated Field, and define it similar to this (this is a shopping cart button example):

Buy_render = "<input name=\"setvar\" type=\"button\" value=\"Add to Cart\" onclick=\"alert('Your item ("+Data.Item_no+") was added');a5_AJAX_Callback('addtocart.ajax.a5w','var=abc&id="+data.id+"');\"/>"

[kmullins]

Re: Creating a new record in another table.

Thanks for the help Steve. I finally got it working the way I would like. Once I get this change rolled out I will defiantly look into turning it into an Ajax call, one learning step at a time though.

Thanks again this really help me out!

Kevin