Hi
I am looking for some guidance. We need to compare different database fields and create the output based on the comparison when there is a match. However, 2 of the fields is first and last name and the other field has full name (which has been calculated via our ERP system). We then want to translate the calculated name to the contacts email address. Our report displays the calculated name (calculated via our ERP system) but we need to translate this into the email address. Example of what we are trying to archive below.

The report shows the following calculated "John Smith" and table1 contains files Firstname, Lastname and email. We need the calculated data to translate and pull through eh table1.email.

e.g. John Smith will show jsmith@example.com

I have pulled all first name and emails through on a sub report but cannot link the correct details with the calculated field, and basically I was trying to match the first and last name fields to the calculated filed and then display the email address for this field (using a formula). if there is another way to achieve this, please advise.

There is a reason for the calculated field as the name can change based on the order.
thanks

Hi
our ERP system calculates the full name of the person who placed the order, crystal reports then uses this calculated field to display the full name which isn't actually part of the table (it appears as calc_name, this is what we will call it for this example), we will call this table2. We don't have the license for developing for our ERP system to create the calculated field for puling through the email, the same way it calculates the full name. The 3 fields, which we will call table1, are the first name, last name and email address associated with the first and last name. I need to get crystal reports to match the calculated field, which is the full name (e.g. John Smith)with the fields in table1 so i can display the email address associated with the displayed full name. Table1 doesn't have a field having the full name.
so basically the report displays the calculated field, which is the persons full name. We need a formula to get the table1 data to be associated with the calculated field data so i can display the correct email address. Hope i have expanded the explanation more.

Yes crystal is really the front end to display data.
Our ERP system has the option to add calculated fields (if we have the SDK license it will allow us to add more calculated fields with data) so crystal will see the calculated field under the table (where we add the calculated field) but it isn't actually a field within that table, that's what I meant when I said "it is not part of the table" because it is not (if I were to browse the actual database I would not see calc_billcontct as a field under the ShipHead table, which is table 2 in my previous posts). The calculated field calculates it from the other table within the report data definitions.
Ok, the calculated field in the report data definitions appears as calc_bilcontct and the calculated field is pulling data from the CustCnt table (table 1 in my previous post). I have added the table 1 (within the report data definitions on the ERP system) so we can see the CustCnt table within the report. So the calculated field will display the full name of the Contact, and because we cannot get data from adding calculated fields in our ERP system (which would have been the way to do it but we don't have the license), I basically need a formula within crystal to get the data from CustCnt table (email address) by using the Calc_Bilcontct displayed data.
I have tried different formulas but it doesn't seem to achieve what I want it to do.

Below is examples of formulas I have tried
//@subreport {ShipHead.Calc_BilContct}
//if ("{CustCnt.FirstName}+{CustCnt.LastName}"={ShipHead.Calc_BilContct})then {CustCnt.EMailAddress}
//IF
// {CustCnt.FirstName} + {CustCnt.LastName}
//= {ShipHead.Calc_BilContct} then {CustCnt.EMailAddress}
// else ""
//IF ISNULL({CustCnt.FirstName}) OR ISNULL({CustCnt.LastName}) THEN
   // "Other"
//Else
    //{CustCnt.EMailAddress}
//if ("{CustCnt.FirstName}" and "{CustCnt.LastName}") is ({ShipHead.Calc_BilContct}) then {CustCnt.EMailAddress} else "no email"
//{CustCnt.FirstName}+{CustCnt.LastName}=""

//if {CustCnt.FirstName} + " " + {CustCnt.LastName} = {ShipHead.Calc_BilContct} then {CustCnt.EMailAddress} else "err"

//if "{CustCnt.FirstName}"+"{CustCnt.LastName}" in{ShipHead.Calc_BilContct} then "{CustCnt.EMailAddress}" else "err"

In summary, the report displays the name of the Bill to contact (as explained, is the calculated field) but I need a formula to check the displayed data of the calculated field and then match it to the CustCnt information so I can then display the email address for the displayed contact. E.g. The calculated field displays John Smith, the formula checks John Smith against the First and Last name within the CustCnt table, when a match is found, it will display the email address from the CustCnt table for that contact.

Hope this makes a bit more sense.

Thanks for replies
I have tried intermediate tables to achieve the desired result. I will try different tables to see if I can achieve it. I appreciate your comments. thanks