Data email

[situ]

Hello,

I have a database with some tables, and I want to sent an email to one email address. The table has a lookup from other table. When user receives the email, the result are numbers and not text,like fields of database. Is it possible due id number?

[benzoD]

Lookup fields are stored as integers and unless you write a hook that does another lookup before the email is sent, you're out of luck. So far I haven't been able to get around this limitation and I've had to remove any lookup fields from my emails. Depending on how many different types of data you have, you could write an elseif hook that would send whatever data you need in the subject or body without it using a lookup field.

Something like:

Code: Select all

if ($data['location'] == '1') {
    @mail("[email protected]", "Earth is fun!", $messageData, "from: [email protected]")
} elseif ($data['location'] == '2'){
    @mail("[email protected]", "Sun is hot!", $messageData, "from: [email protected]")
}     

Only feasible if you have a limited amount of lookup fields, though.

[peebee]

Had exactly the same issue but resolved with a LEFT JOIN

Example:
In my case, 2 x tables concerned were "jobs" and "agents".

jobs.agentto is the lookup field (stored as integer) that I wanted to include in the email. Parent table/field for jobs.agentto is agents.agentref (text field)

jobs.ourref is the primary key for the jobs table. agents.id is the primary key for the agents table

The following query provides the desired result (works for me anyway):

$agent=sqlValue("select a.agentref from jobs j left join agents a on a.id=j.agentto where ourref='".makeSafe($selected_id)."'");

then

@mail (Agent = $agent.......

Replace with your own table/fields and hopefully you should receive the result you are after. Good luck.

PS: I'm still using Appgini V5.12 but I'd presume nothing has changed that would effect things in the current version.

[benzoD]

That's a great solution. I'll have to give it a go with my app. Thanks peebee.

[TheCodeRed]

I simply call the reference to the other table after the stored integer value is retrieved. Essentially, once the value of the look up field is gathered, take that value and look for it in the coordinating table. Return the value that you need from that table.

If the value from the lookup field you are retrieving is 3 and the lookup table is names.
Find the names.id = 3 and then select whatever field data you need.
SELECT 'FirstName', 'LastName' FROM 'names' where 'id'=3
$Newname = $row[FirstName].' '.$row[LastName];

Here is a simple sample from one of my e-mail hook files. (This is just a short blurb from the whole Hook file, just to see how I am referencing First and Last names from a lookup table)

Code: Select all

$SalesRep=sqlValue("select Cust_SalesRep from Customer where Cust_Id='".$data['SR_Customer']."'");
		$SalesRepFName=sqlValue("select SR_FName from Sales_Reps where SR_Id='".$SalesRep."'");
		$SalesRepLName=sqlValue("select SR_LName from Sales_Reps where SR_Id='".$SalesRep."'");
		$SalesRepName=$SalesRepFName." ".$SalesRepLName;

[situ]

Thanks for all! I will try in my app.