Get email of Sales Employee into CrystalReport

[former_member267979]

Hello,

is somehow(sql select) possible get email of user to CrystalReport. I have user connected to Sales Employee and my goal is get the email from card ORDR - Sales Order.

what I did try:

SELECT

T0.DocNum, T0.CardCode, T0.CardName, 
T1.ItemCode, T1.Quantity, T1.Price, 
T2.[SlpCode], T2.[SlpName],
T3.[E_Mail]
FROM ORDR T0  
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T2  ON T0.SlpCode = T2.SlpCode
INNER JOIN OUSR T3 ON T2.SlpName = T3.E_Mail

but this doesn't work as I need

Thank You

[kothandaraman_nagarajan · Accepted solution]

Hi,

Do you want to add email id for sales emp or user who creating document?

Please try this query to get user's email ID,

SELECT 

T0.DocNum, T0.CardCode, T0.CardName,  

T1.ItemCode, T1.Quantity, T1.Price,  

T2.[SlpCode], T2.[SlpName], 

T3.[E_Mail] 

FROM ORDR T0   

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry  

INNER JOIN OSLP T2  ON T0.SlpCode = T2.SlpCode 

INNER JOIN OUSR T3 ON T3.UserId = T0.UserSign

Thanks

[former_member267979 · Accepted solution]

I have tried it, but when I use in form 'E_Mail' ..it clear all other existing values in report ... makes them empty

in my case I have user connected to Sales Employee

my goal is show Sales Emloyee from 'ORDR' and his email which have to be taken from user card/list 'OUSR'

[former_member267979 · Accepted solution]

I try this,

SELECT

T0.DocNum, T0.CardCode, T0.CardName,

T1.ItemCode, T1.Quantity, T1.Price,

T2.[SlpCode], T2.[SlpName] ,

T3.[salesPrson], T3.[email]

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OHEM T3 ON T0.SlpCode = T3.salesPrson

before I had problem,  that when I drag/drop the field into the form, it made empty all values in form

but after restart CrystalReport it is ok ann works fine

Thanks everybody for all hints

[Former Member]

Hello,

You may try with below query:

SELECT 

T0.DocNum, T0.CardCode, T0.CardName,  

T1.ItemCode, T1.Quantity, T1.Price,  

T2.[SlpCode], T2.[SlpName], 

T3.[E_Mail] 

FROM ORDR T0   

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry  

INNER JOIN OSLP T2  ON T0.SlpCode = T2.SlpCode 

INNER JOIN  OUSR T3 ON T2.SlpName = T3.U_Name

Here you have to be sure the value in the field SlpName and U_Name is similar i..e Case sensitive to be checked up. For example, If the value in SlpName is JOHN then in U_Name it must be JOHN and not John or john.

Hope it will help you.

Regards,

Kuldeep

[former_member267979]

I have tried it, but when I use in form 'E_Mail' ..it clear all other existing values in report ... makes them empty

I have report about Sales Order 'ORDR', on this card is Sales Employee

Sales Employee I have connected to user and rightly there is the E_Mail

my idea is put on the report name of Sales Employee and append to him email from user 'OUSR'