AppGini Community Forums

I am working on adding an email notifications and have got it working but the email sends only the key instead of the data for some of my fields. I need to figure out how to get it to send the actual data from other tables and not just numbers associated with that data or the email is worthless as it only has a bunch of numbers in it.

I have this code from the hooks help. I believe the problem is $value . How do I modify this code to retrieve the actual data from my fields for the email?

function tablename_after_insert($data, $memberInfo, &$args){

// to compose a message containing the submitted data,
// we need to iterate through the $data array
foreach($data as $field => $value){
$messageData .= "$field: $value \n";
}

@mail(
// mail recipient
"[email protected]",

// subject
"A new record needs your attention",

// message
"The following new record was submitted by {$memberInfo['username']}: \n\n".
$messageData,

// sender address
"From: [email protected]"
);

return TRUE;
}

From reading further it looks like I need to be able to extract data using the values from 1 table which are referenced in another table to compose my email notification correctly.

I can attach my appgini file if this helps. Still trying to figure out how to take the values in $value and get the actual text in the other table to compose the email properly.

Anyone have any idea how to do this?

You should use something like this: You should replace names in the above query with actual parent lookup field name, parent table name and parent table primary key name. The actual lookup field value will be returned in $lookup_value.

Sorry but that is extremely confusing to me.

I have attached my appgini file and the php file from my hooks folder I want to have the after insert email sent from. I want to include

Ticket Number
Ticket Priority
Customer
Site ID
Job Address
Job City
Job State
Job Zip
Job Site Phone
Customer Ticket
Problem Description

I am not sure of the proper coding for my tickets.php file in the after inset function area to get the proper values from my database.

Any help would be greatly appreciated.

Thanks

I have been trying to add my own code to allow for populating the email with data from other tables but all that happens is I break my ticket.php file somehow in the hooks folder and my webpage is blank. When I remove the code my ticket webpage comes right back so its definitely the code I am putting in.

I tried both creating a new variable

$lookup_value = sqlValue("select SiteAddress from Sites where id='{$data['selectedID']}'");

Even that 1 line makes the webpage go blank. I have to be missing something.

Check this post it pulls data from another table might help you out.

http://forums.appgini.com/phpbb/viewtop ... f=7&t=1445

Try this:
Note the absence of ' ' around selectedID.

Now when I enter a ticket I get this error on saving it to the database.

Unknown column 'id' in 'where clause'

Is my syntax for constructing the message correct? Here is my code....

// to compose a message containing the submitted data,
// we need to iterate through the $data array

$siteaddy = sqlValue("select SiteAddress from Sites where id='{$data[selectedID]}'");
$sitecity = sqlValue("select SiteCity from Sites where id='{$data[selectedID]}'");
$sitestate = sqlValue("select SiteState from Sites where id='{$data[selectedID]}'");
$sitezip = sqlValue("select SiteZip from Sites where id='{$data[selectedID]}'");
$sitephone = sqlValue("select SitePhone from Sites where id='{$data[selectedID]}'");
$sitecontact = sqlValue("select SiteContact from Sites where id='{$data[selectedID]}'");
$jobpriority = sqlValue("select JobPriorityNames from JobTicketPriority where id='{$data[selectedID]}'");
$jobstatus = sqlValue("select Status from JobTicketStatuses where id='{$data[selectedID]}'");

@mail(
// mail recipient
"[email protected]",

// subject
"A new ticket has been created",

// message
"The following new ticket was submitted by {$memberInfo['username']}: \n\n".

"Ticket Number: {$data['TicketNumber']} \n".
"Priority: {$jobpriority} \n".
"Status : {$jobstatus} \n".
"Site ID : {$data['JobSite']} \n".
"Address : {$siteaddy} \n".
"City : {$sitecity} \n".
"State : {$sitestate} \n".
"Zip : {$sitezip} \n".
"Phone : {$sitephone} \n".
"Contact : {$sitecontact} \n \n \n".
"Problem : {$data['ProblemDescription']} \n".


// sender address
"From: [email protected]"
);

Unknown column 'id' in 'where clause'

From that error, it looks like either Sites, JobTicketPrioritytable or JobTicketStatuses table doesn't actually have a field named "id"?

I was just about to post the same thing. One of those tables doesn't have an 'id' field.

Ok so no more errors now. Email came through but none of the fields where data is pulled from other tables showed up in my email only the headings.

This is an example email. The ticket number, side id, and problem all are fine why are these other fields all blank? My source code is above. Still something missing in my syntax somewhere.

Ticket Number: 23444444444444444test
Priority:
Status :
Site ID : 00238
Address :
City :
State :
Zip :
Phone :
Contact :


Problem : dgrfssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssdgrfssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssdgrfssssssssssssss

It's clear that it's your sqlValue() calls that are failing. The three populated fields in the email all come from $data[].

Sometimes it's the smallest typos that are the hardest to spot and cause the most grief
See if that one-character change doesn't solve it.

Still empty values in my email. I didn't realize how difficult this was going to be to make work. I know I am close .LOL.

My Sites table has the following:
Indexes: Documentation Keyname Type Cardinality Action Field
SiteID UNIQUE 391 Edit Drop SiteID
CustomerName INDEX 97 Edit Drop CustomerName

So I used SiteID instead of id in this case.

my code which is showing blank for all values except Ticket Number, Side ID, and Problem is the following:

function Tickets_after_insert($data, $memberInfo, &$args){


// to compose a message containing the submitted data,
// we need to iterate through the $data array

$siteaddy = sqlValue("select SiteAddress from Sites where SiteID='{$data[SelectedID]}'");
$sitecity = sqlValue("select SiteCity from Sites where SiteID='{$data[selectedID]}'");
$sitestate = sqlValue("select SiteState from Sites where SiteID='{$data[selectedID]}'");
$sitezip = sqlValue("select SiteZip from Sites where SiteID='{$data[selectedID]}'");
$sitephone = sqlValue("select SitePhone from Sites where SiteID='{$data[selectedID]}'");
$sitecontact = sqlValue("select SiteContact from Sites where SiteID='{$data[selectedID]}'");
$jobpriority = sqlValue("select JobPriorityNames from JobTicketPriority where id='{$data[selectedID]}'");
$jobstatus = sqlValue("select Status from JobTicketStatuses where id='{$data[selectedID]}'");

@mail(
// mail recipient
"[email protected]",

// subject
"A new ticket has been created",

// message
"The following new ticket was submitted by {$memberInfo['username']}: \n\n".

"Ticket Number: {$data['TicketNumber']} \n".
"Priority: $jobpriority \n".
"Status : $jobstatus \n".
"Site ID : {$data['JobSite']} \n".
"Address : $siteaddy \n".
"City : $sitecity \n".
"State : $sitestate \n".
"Zip : $sitezip \n".
"Phone : $sitephone \n".
"Contact : $sitecontact \n \n \n".
"Problem : {$data['ProblemDescription']} \n".


// sender address
"From: [email protected]"
);

Try:
Does that work?

If not...

If your field and table names are 100% correct, the problem must be in the where clause. So try:
Where ### is a known valid SiteID.

If that still doesn't work, then something's screwball.

Hrmm definitely something with my syntax.

I changed some lines to this...

$siteaddy = sqlValue("select SiteAddress from Sites where SiteID=00238");
$sitecity = sqlValue("select SiteCity from Sites where SiteID=00238");
$sitestate = sqlValue("select SiteState from Sites where SiteID=00238");

Now the email populates correctly but I am forcing the SIteID as 00238. I need it to grab the information using the SiteID I have entered not a forced one.

When I used

$siteaddy = sqlValue("select SiteAddress from Sites where SiteID={$data[selectedID]}");

It gave me an SQL error when I inserted a new record. Error at line 861 whatever that is.

Okay, the error is definitely in the where clause, when using the variables $data and $selectedID. One of them is probably not what you're thinking it is.

Your next step is to see what you're actually getting from those variables statement. Add this:

I should add, that will abort the script (the exit statement). It will dump those two vars so you can check their values, though, which is what you want.

GOT IT!!!!!

Ok so here is what was missing.

In the top area there are variables commented luckily for me the maker of this program did a great job creating the comments section so we can edit the code!

* For this table, the array items are:
* $data['CorS'], $data['TicketActive'], $data['TicketNumber'], $data['TicketStatus'], $data['TicketPriority'], $data['TicketCust'], $data['JobSite'], $data['JobAddress'], $data['JobCity'], $data['JobState'], $data['JobZip'], $data['JobSitePhone'], $data['CustTicket'], $data['ProblemDescription'], $data['TicketNotes'], $data['TicketUploadsPics']
* Also includes the item $data['selectedID'] which stores the value of the primary key for the new record.

Now using this information ($data['JobSite'] and $data['TicketPriority']) above and the email tutorial template for the after insert area of the hooks php file I ended up with this which seems to be working 100% so far.

// to compose a message containing the submitted data,
// we need to iterate through the $data array

$siteaddy = sqlValue("select SiteAddress from Sites where SiteID='{$data['JobSite']}'");
$sitecity = sqlValue("select SiteCity from Sites where SiteID='{$data['JobSite']}'");
$sitestate = sqlValue("select SiteState from Sites where SiteID='{$data['JobSite']}'");
$sitezip = sqlValue("select SiteZip from Sites where SiteID='{$data['JobSite']}'");
$sitephone = sqlValue("select SitePhone from Sites where SiteID='{$data['JobSite']}'");
$sitecontact = sqlValue("select SiteContact from Sites where SiteID='{$data['JobSite']}'");
$jobpriority = sqlValue("select JobPriorityNames from JobTicketPriority where id='{$data['TicketPriority']}'");
$jobstatus = sqlValue("select Status from JobTicketStatuses where id='{$data['TicketStatus']}'");

@mail(
// mail recipient
"[email protected]",

// subject
"A new ticket has been created",

// message
"The following new ticket was submitted by {$memberInfo['username']}: \n\n".

"Ticket Number: {$data['TicketNumber']} \n".
"Priority: $jobpriority \n".
"Status : $jobstatus \n".
"Site ID : {$data['JobSite']} \n".
"Address : $siteaddy \n".
"City : $sitecity \n".
"State : $sitestate \n".
"Zip : $sitezip \n".
"Phone : $sitephone \n".
"Contact : $sitecontact \n \n \n".
"Problem : {$data['ProblemDescription']} \n".


// sender address
"From: [email protected]"
);


Thanks to everyone for the help. Hopefully this thread will help others figure this out if they need it.

Now I can finally fully deploy my first Appgini application!

Another pieces of information. I had multiple tables which worked together on this project so my Customer Name was not filling in correctly I notice today.

Anyhow I have a table for tickets which has a link to each customer site. Each customer site is linked back to the customer. So when looking at my tables it was difficult to traverse back 2 tables to get my correct Customer Name.

What I did was create a variable which would get my numeric id value of 1 table and then using that variable on the next line I would have it look back to the base Customer table.

Please note in my code I used SiteID to identify each site not the typical id in my tables. Probably would have been a good idea to stick with id for all tables looking back now but this is my first appgini application.

Anyhow so I have this line of code

$customervalue = sqlValue("select CustomerName from Sites where SiteID='{$data['JobSite']}'");

Which grabs my value so I can use this value to get my actual text of Customer Name using this line.

$jobcustomer = sqlValue("select CustomerName from Customers where id=$customervalue");



So now here is the final product of my after insert code which populated my fields from other tables correctly and is working.


// to compose a message containing the submitted data,
// we need to iterate through the $data array


$siteaddy = sqlValue("select SiteAddress from Sites where SiteID='{$data['JobSite']}'");
$sitecity = sqlValue("select SiteCity from Sites where SiteID='{$data['JobSite']}'");
$sitestate = sqlValue("select SiteState from Sites where SiteID='{$data['JobSite']}'");
$sitezip = sqlValue("select SiteZip from Sites where SiteID='{$data['JobSite']}'");
$sitephone = sqlValue("select SitePhone from Sites where SiteID='{$data['JobSite']}'");
$sitecontact = sqlValue("select SiteContact from Sites where SiteID='{$data['JobSite']}'");
$jobpriority = sqlValue("select JobPriorityNames from JobTicketPriority where id='{$data['TicketPriority']}'");
$jobstatus = sqlValue("select Status from JobTicketStatuses where id='{$data['TicketStatus']}'");
$customervalue = sqlValue("select CustomerName from Sites where SiteID='{$data['JobSite']}'");
$jobcustomer = sqlValue("select CustomerName from Customers where id=$customervalue");


@mail(
// mail recipient
"[email protected]",

// subject
"A new ticket has been created",

// message
"The following new ticket was submitted by {$memberInfo['username']}: \n\n".

"Ticket Number: {$data['TicketNumber']} \n".
"Priority: $jobpriority \n".
"Status : $jobstatus \n".
"Site ID : {$data['JobSite']} \n".
"Customer : $jobcustomer \n".
"Address : $siteaddy \n".
"City : $sitecity \n".
"State : $sitestate \n".
"Zip : $sitezip \n".
"Phone : $sitephone \n".
"Contact : $sitecontact \n \n \n".
"Problem : {$data['ProblemDescription']} \n".


// sender address
"From: [email protected]"
);

As a rule of thumb, never use actual data as the primary key. Maybe in the most basic of lookup tables, but even then it's questionable.

In AppGini, you can control the lookups to display other fields, even though they are looking up based on 'id' (technically, the PK field.) So in your user interface you never actually have to see 'id', but it is used under the hood in a way that improves functionality.

Thanks yea I knew when I created the database that my SiteID would be a unique field for every site I made it the primary key. Looking back I should have just used id but its only 1 table so I decided to leave it as is.

Definitely tricky getting the email notifications to work how I needed them to. The table specific hooks example is fine if all your required values are in the table you are working with but mine are from multiple tables. Thanks for all the help. This was a big milestone for my project.