I cant even get it to work for 2 levels.
When I choose the First Name —> Values
I get a prompt to choose Last Name–>
when I choose a particular Last Name…and click OK.
I get only one first name to choose.
when I click OK. and display the first/last names in the report.
I get a list of last names with the same first name.
Please throw in some ideas…i’m literally struck…
Thanks for all the help.
mak1600 (BOB member since 2006-02-09)
That’s the way it works. From your description, you are prompting for last name, and based on the selection of last name showing a list of first names. The only condition that gets actually applied to the query is your first name selection, thus you get everyone with the selected first name. That’s it, that’s the way it works.
If you want to select actual people, then you need to - in your second LOV - select something other that first name. Something like the “system id” or a full name or anything that is specific to the person (or people) that you’re selecting.
Dave Rathbun 
(BOB member since 2002-06-06)
Thanks for the info.
Here is the problem…
I should prompt a user to enter the last name.
Based on that selection I should prompt for the available First Name
and once First name is selected it should display only the (previous selected) Last Name and First Name…but instead it displays all the first names…Hope you are getting my point. I’m new to BO so would appreciate if you can explain me how I can achive this. Employee NUmber is unique.
Thank You
mak1600 (BOB member since 2006-02-09)
Customize your LOV for first name so that it displays the first name but you select the employee number.
Dave Rathbun (BOB member since 2002-06-06)
I tried that…but still it doesnt like it. It displays all the first names and last names in the report for the choose firstname
mak1600 (BOB member since 2006-02-09)
This is a question that gets asked repeatedly – here’s another one in this same forum, with the same answer. Like a lot of us, you’d like cascading prompts to impose cascading limits – and as the other topic shows, it unfortunately just isn’t possible in current versions.
Dave was suggesting that you actually build your second LOV to display Employee numbers (or some such key) as the first column (so, then, the LOV is actually on Employee numbers, not ON first name), with the First Name as the second column. So – when the user picks an entry, they’re selecting a key value. They just “see” the employee name.
Anita Craig (BOB member since 2002-06-17)
Thanks very much for your reply. I even tried that. When I choose Em-ployee Number along with First Name and full the First Name filed in the report, I get all the employees with that First Name ( It disregards the last name that I had choose earlier in the prompt). I donn know if we can even do this in BO6.5. Anyways I’m still trying to find a work around. I appreciate all your help. I have one more quicky question.
I have a model database and a production database ( seperately).
Do I have to choose the option export LOV’s with universe option?( coz the data is going to change in production) as I have modifed the LOV’s for the above problem.
Thanks very much in advance.
mak1600 (BOB member since 2006-02-09)
Then, you haven’t done what Dave suggested. Based on the behavior, you must still be building the LOV on First Name, and including Employee number as an additional object in the LOV.
You need to build the Lov on Employee number – and include Employee name as a second object in the LOV.
Anita Craig (BOB member since 2002-06-17)
Thanks very much Anita. I dont really know where I’m doing wrong. Anyways I’m attaching a file with the screen shots and a read.txt file that has info regarding the screen shots. I have got it to detail level. I would really really appreciate if you can take a look at this and let me know where I’m doing wrong.
Thank You.
pics.zip (206.0 KB)
mak1600 (BOB member since 2006-02-09)
That wasn’t what I expected at all. I was expecting that you were building LOV queries, as covered in this entry from our Designer FAQ! Is there a reason you’re using “prompt” rather than picking from a LOV?
If someone else has done something like this with Prompts instead of LOVs – and has an idea of whether he can even do this with “Prompts” – please chime in!
Anita Craig (BOB member since 2002-06-17)
The final LOV has to be based on employee number, not first name. You can display the first name, but you have to pick the number. That is what the query condition has to be on, otherwise you don’t have a unique value, and you get everyone with the same first name.
Dave Rathbun (BOB member since 2002-06-06)
It looks like I get to be the bad guy here. You’re heading down a very dangerous path. BO is not a trivial application. What you’re building is not going to look like what people recommend.
Steve Krandel (BOB member since 2002-06-25)
Hey Dave,
I was not able to achieve what you said. Please take a minute to look at the screen shots that I have attached in my previous message and do let me know where I need to do the change [or] please give me example on how to implement this…
Thanks very much in advance…
mak1600 (BOB member since 2006-02-09)
I have given an example. 
Assume 3 objects, customer number, last name, first name
Create a custom LOV for Customer Number in Designer. This LOV should include the Customer Number as the first result object, and the First Name as the second result object. Apply an ascending sort to First Name. Create a condition for this custom LOV that prompts for a last name. Save the LOV.
Save the universe.
Create a query with Customer Number, First Name, and Last Name as output objects. Create a condition on Customer Number, use Equal To, and provide a prompt for the customer number.
Execute the query. When prompted for the customer number, click the Values button. You will be prompted for the Last Name. Review the list, pick a last name. Note that your original LOV now includes a list of customer numbers + first names for those customers that have the last name selected from the prior LOV. Select a customer and execute the query. You will only get the customer for the selected customer number.
That’s essentially what I already posted, as re-described by Anita here:
You can build this quite easily in Island Resorts Marketing as as prototype.
Dave Rathbun (BOB member since 2002-06-06)
Thanks very much Dave. I was successful in displaying the results accordingly. Once again thanks to You and Anita for all the support. Appreciate it.
Was wondering if this is possible in BO.
Instead of choosing from the list of values, can i type in some letters in the name and the lov's only would show the names starting with the letters that I typed in. Like it works for search. For example, If I typ ein Jo and click Values for last names, I should see only the values starting with Jo.
Just wanted to know if this can be achieved in BO.
Once agin kudos to you and Anita.
mak1600 (BOB member since 2006-02-09)
Dave,
I used
Lastname MatchesPattern Prompt ('Please Choose the Last Name') in my Employee Number Edit Lov's Condition.
Would appreciate if you can let me know where I’m going wrong.
Thanks in advance.
mak1600 (BOB member since 2006-02-09)
You need to either train the user to enter the wildcard or append it yourself. To append it yourself, you’ll need to create a predefined condition object in Designer that performs that logic. Something like:
table.last_name like @prompt( ... ) || '%'You will need to fill in the @prompt syntax properly, of course, you can review the Designer help for that function if you’re not familiar with it. The || is the concatenation for Oracle and some other databases, you’ll need to know what the concatenation operator is for your database.
Once you’ve created that predefined condition object, use it in the LOV rather than a manually created condition. That way you ensure that the user can enter a number of letters and the % will automatically be appended at the end.
Dave Rathbun (BOB member since 2002-06-06)
Dave,
Thanks for your reply. I want to incorporate this feature for what I have been doing.
I have created cascading prompt by following your steps.
In the first step : I pull Emp NUmber, First Name as Query Objects and my Condition would be [ LastName in list Prompt ( “choose last name”)
In second step: I would pull all the query objects and my condition would be Employee NUmber equal to Prompt(‘Choose the first Name’).
When run…
What I intend to do.
In the second step…When prompted to choose Last Name{ when I type in something like ‘Jo’ then I should get the lov’s starting with ‘Jo’ }
Can you please tell me where I need to create a condition object and how this object can be used in my above problem.
Thanks a million for all the help.
Thank you
mak1600 (BOB member since 2006-02-09)
I understood your question. Please tell me what you didn’t understand about my previous response as it will solve your problem.
You cannot do what you want to do on the query panel, you must create a predefined condition in Designer and use it on the LOV for Empoyee Number/First Name.
Dave Rathbun (BOB member since 2002-06-06)
Thanks dave.
I went to the SQL code and made the change. And checked ‘Do not generate SQL before running’. It works…
Is this the only way that this can be achieved?..can this be achived without having to modify the code?
tableName.lastname like @variable('Choose last name') ||'%'
mak1600 (BOB member since 2006-02-09)