email rpt for current record when click submit

[Stan Mathews]

Re: email rpt for current record when click submit

Shouldn't be so long as there is a value visible.

Is "{id}" the syntax you would use to refer to the id in a script? What happens if you try it without the quotes?

Looking at an example in the webhelp

query.filter = "firstname = " + quote(dataSubmitted.firstname) + ".and. lastname = " + quote(dataSubmitted.lastname)

it appears you reference a grid field with componentname.fieldname

So maybe you need

Req = yourgridname.id

[joannristau]

Re: email rpt for current record when click submit

That makes sense. I will try that next....

[Steve Wood]

Re: email rpt for current record when click submit

I'm confused about where you are starting out with this. You have a grid and want to print a report based on one of the records visible on the screen, right? What is the user doing to initiate the report? Are they just clicking Submit on the grid, or clicking a link on the desired grid record? Describe what action takes place to start this process out.

Some points:

The syntax {req_ID} is only of use in creating a link within the grid. The {req_ID} is replaced at runtime with the req_ID value from your record.

LinkPage.a5w?id={Req_id} make sense IF that is your syntax describing a link in a grid. That syntax might produce a hyperlink URL such as linkpage.a5w?id=1001. In this case, the page linkpage.a5w would see a variable named ID, with a value of "1001", and your filter might be:

query.filter = "id="+quote(id)

or if your table value is numeric:

query.filter = "id="+id

I might be off on that last expression as I never use numeric ID's in my tables. It might be: query.filter = "id="+convert_type(id,"N")

The following syntax would not be valid in any context because of the {}'s:

response.redirect("LinkPage.a5w?id={Req_id}")

And (sorry) Stan's last post is inaccurate for the web.

[joannristau]

Re: email rpt for current record when click submit

Hi Steve. Thanks for your reply. I thought about this issue long and hard last night and tried several different things, but didn't get any farther other than more error message :-)

Yes the user accesses an a5w page with a grid. There is a submit button that the user clicks. The submit button is where the AfterValidate code was to

1) generate a report based upon the submitted record,
2) email the user with the pdf report attached.

The grid only references one table -- requests.dbf .

The record layout for the key fields is as follows (there are quite a few more, but these are the first few in the list):

Req_id (character - auto increment field -- primary key)
org_id (char)
Organization (char)
submit_date (date)
submitted_by (char)
approver (char)
description (char)

Ideally the would like the email to include the Req_id and the description in the message body.

I guess this would be similar to an email sent to a website customer with details of the order they placed.

Originally I was using recno() for the query criteria but I was getting the first record in the table in the report.

Next I tried creating a page with just a5 code to print the report and email it. I then changed the code on the submit button to call that page, which is where the response.redirect("LinkPage.a5w?id={Req_id}") code is. I have gotten myself totally confused.

I went through the web components on the sample db but didn't find anything that I could use for a guide for this.

Thank you again. I will make some screenshots of what I'm trying to accomplish and will post them shortly too.

[joannristau]

Re: email rpt for current record when click submit

Here are some screenshots. Hopefully this will help.

[Bob Moore]

Re: email rpt for current record when click submit

JoAnn,

Are you using V9 or the V10 beta?
Are the values coming from a grid component or a dialog? The syntax for retrieving the values will be different, since grids have rows and columns of data.
Are you using a SQL back end?
If so, what SQL database are you using?

The key field needs to contain something before you can filter anything. I've run into this with SQL Server when the key is auto-generated. The field data isn't present until it's submitted to SQL Server. As such, how can you referernce it? What I do is use a UUID and let Alpha generate the UUID as the default value. I think I use default value = api_uuidcreate() for a grid entry but check that out in the help file to confirm since I'm relying on memory here.

You can also use a date/time function to do something similar. Search this message board for key values and you will find a few discussions that cover this. If you want, you can use an auto generated integer based key (if you want to see sequential numbering for the records in the table) but add the UUID as a key that you can use within the Alpha grids and also to link your related tables. You can hide this in a div to keep the display clean. This works very well.

I think I understand what you are trying to accomplish and I also believe it can be accomplished within one page/script without having to re-direct to another page but more specifics are needed.

Hope that helps.

[joannristau]

Re: email rpt for current record when click submit

Hi Bob,
Thank you very much for your reply. Below are the answers to your questions.

I am using V9.
Values are coming from a grid component.
I am not using a SQL backend. Using A5 database table.
The table requests has a primary key on req_id and it is the auto-increment field.

I will also do some research on api_uuidcreate() .

Thanks!!

[Steve Wood]

Re: email rpt for current record when click submit

Your screenshot helps a bit.

You need to get your terminology down. If the right-hand side of the screen is a Grid, then there is no AfterValidate event which you referenced in your post below. The code you show your attachment is for a grid when the record is updated (AfterUpdateRecord event). But your screenshot refers to a NEW RNE record, so which is it?

The fact that your rec_id does not exist indicates this is a NEW record, since an auto-increment will not exist until the record is saved to the database.

There is no grid event that will fire AFTER a new record is saved, so a grid has no access to your autoincrement value until that record is saved and visible on the screen.

Your code has this line: CurRec = session.recno() but I don't see where you have created a value for session.recno(). And for that matter, what is a session.recno()? The parens indicate this is a function like alltrim() is a function. Is there a function named session.recno(), I don't think so.

You need to completely forget about using recno() in your code. Never rely on the record number for anything at all; you have no control over what that value is. The same physical records may have a different recno() on different occassions. You reference a record based on its primary key, which you happen to call rec_id (poor name choice, I use "ID" in 100% of my tables as the primary key.).

Personally, whenever I need to do anything exciting, like send an email or referenced a just-created record, I create my data-entry routine in a Dialog. It's more work, but you have control.

If you did this, your AfterValidate code would need to include a record saving routine. Part of that would be to get the autoincrement value just after you saved the record, and then use that value to pull the record for your email routine. You don't need to use the api_uuidcreate() for this, just the autoincrement (although I do the same as Bob because I want that uuid value).

Like this:

dim tbl as p
tbl=table.open("[PathAlias.ADB_Path]\mytable")
tbl.enter_begin()
tbl.myfield = currentform.controls.mydialogfield.value
tbl.enter_end()
pkid = tbl.id
tbl.close()

' now you have your primary key from the autoincrement, the var->pkid above. Use that pkid to get the record, run your report, send the email.
' at the end, use currentform.redirecttarget = "mypage.a5w" to go back to the grid.

[Bob Moore]

Re: email rpt for current record when click submit

JoAnn,

If you want to use the grid, just create a field that you can populate as a key field within the grid. There are a number of options here as previously mentioned... I use a field typically called UUID and set the default value as =api_uuidcreate().

If you use a field named UUID within the grid, you can reference this field within your script as DataSubmitted.UUID. DataSubmitted.fieldname is used to reference the current record values within a grid. Just filter with this value in your script and you should be all set.

[Steve Wood]

Re: email rpt for current record when click submit

To clarify, Bob's solution does not mean giving the UUID field a default value with a Field Rule. He means generating the value right in the event code and write it to the database from the event. You would generate the value in the event code like:

mypk = remspecial(api_uuidcreate())
datasubmitted.uuid = mypk

And then you know your primary key for the current record, the value in the mypk variable. You can use this to continue your script.

Note - I use remspecial() because the resulting UUID looks cleaner, it omits the dashes in a normal uuid value.

Also, the uuid field in the grid needs to be a Hidden field. Hidden means editable but not visible.

[joannristau]

Re: email rpt for current record when click submit

Thank you VERY much Steve and Bob for the replies. I will try these today or tonight to see if I can get it to work. I'm excited that I might be able to get the email piece to work! The VP that has requested this project will be so happy if I can get the email piece to work.

Thanks again guys!

[joannristau]

Re: email rpt for current record when click submit

THANK YOU!!!

You guys are geniuses!!! Its working!!!

I added the following in my BeforeValidateRecords

mypk = remspecial(api_uuidcreate())
datasubmitted.uuid = mypk

and then in the AfterUpdateRecords I added the following query syntax and it now works!!

query.filter = "alltrim(uuid) = alltrim(var->mypk)"

An email is generated for the submitted record and sent as an attachment.

Thank you SOOOO MUCH!