Stages

[Former Member]

Hi,

In the Sales Opportunites i have 5 stages.

1. Lead

2. Design

3. Manufacturing

4. Marketing

5. Quotation

As soon as new enquiry come i will enter the details in the sales opportunities module. In the Stage tab, i will enter the 1st stage as Lead. When i enter the second stage i need to send the alert message to design department user.

<b>Note:</b>

I need to send only the new lead number which i have entered. And when i have changed from "Design" to other stage. Alert message should not come to "Design department". For the i have used the below query.

SELECT T0.[OpprId], T0.[Line], T0.[SlpCode], T0.[Step_Id] FROM OPR1 T0 where Step_Id = 2 and OpprId = (select max(OpprId) FROM OPR1)

But when i used this query if i change the stage of the lead from "Design" to other, still alert message is forwarding to "Design" department.

[Former Member]

.

[Former Member]

Hi Suresh,

I understand why its still sending the alert to the Design department.

Rather than saying:

WHERE Step_Id = 2

you could say

WHERE StepLast = 2 (StepLast is the current stage number field therefore it will only email opportunities where the current stage is Design)

Kind regards,

Adrian

[Former Member]

Hi,

Thanks for your reply. But there is no field in the table OPR1 named

<b>StepLast</b>.

Please let me know where is this field available.

regards

Suresh S

[Former Member]

Hi,

The StepLast field is in the OOPR table.

Give this query a go:

SELECT T1.[OpprId], T1.[Line], T1.[SlpCode], T1.[Step_Id] FROM OOPR T0 INNER JOIN OPR1 T1 ON T0.OpprId = T1.OpprId WHERE T0.[StepLast] = 2 AND T1.[Step_Id] = 2

Regards,

Adrian

[Former Member]

Hi,

Thanks for your reply.

Now i need to filer only the sales opportunites raised for the currect date . How can i filter. If i use getdate it is not displaying

SELECT T1.[OpprId], T1.[Line], T1.[SlpCode], T1.[Step_Id] FROM OOPR T0

INNER JOIN OPR1 T1 ON T0.OpprId = T1.OpprId WHERE T0.[StepLast] = 3 and T1.[Step_Id] = 3 and T0.[status]='O'

and T0.[OpenDate] = getdate()

regards

Suresh S