Prompt Validation

[sandy_vitty]

Hi All

         Have a strange requirment. Donot know how to accomplish this.
We have a cascading value prompt where the user has to enter the employee ID. On this prompt i need to do 2 validation.
1. Check if that employee  id exists in our employee table. If not ask them to reenter a valid emp id
2. If he exists and does not have any transaction then display an message saying no transaction.

The reports i cannot display the second message in report is that because it is a cascading prompt and i have other prompt taking this emp id as input.

I was thinking to use javascript to accomplish the first validation and a conditional block for the second validation. I am able to acomplish the second validation.

But was not able to see how i can put a simple sql select statment inside javascript.

Thanks

[Mpotla]

can u put a prompt with a parameter in the prompt page and hide it? the query for the hidden prompt will be the main query so that if there are no rows for the main query, there wont be any items in that hidden prompt. now u can have a JavaScript to chk the number of options in the hidden prompt, when the parent prompt is selected (ie options.[0].selected=false ..something like this).....and alert user that no data exists for this ID....
just a vague idea ....... not sure whether it will help you ...

if ur successful ... please share the idea/logic ....

all the best.....