Create dynamic LOV in oracle apex

0
votes

Im creating an application. I need to use a multi select list for tabular form.

Below is a screen shot of the tabular form.

*enter image description here*

In this i created a multi select list, using

HTMLDB_ITEM.SELECT_LIST_FROM_LOV_XL(9,' ','USERNAME','multiple size = "3"')

Soon after "Asign users" is clicked, It goes to another page where User names are selected, and inserted into the table user_det_t and an id work_assignment_id is generated using trigger.

and that id is shown up in the tabular form.

Now for that work_assignment_id there will be a set of user ids which are assigned in that new screen, meaning.. a process has 1 assignment id.. which has many user name assigned. Now I need to show the users for each process in the corresponding multi select list, just for display purpose.

In order to do that, we need to call an LOV, I created an LOV named "USERNAME" called in this.

HTMLDB_ITEM.SELECT_LIST_FROM_LOV_XL(9,' ','USERNAME','multiple size = "3"')

Now the problem is I couldnt create a dynamic LOV by that way.

Can anyone help me out of this?

1
oracleoracle-apex
Why would you use a multi-select list just for display purposes? Why not just use a display-only item populated with a concatenated list of the usernames - possibly separated by <br/> tags to format nicely? - Tony Andrews
What you said will be fine if the number of users are limited, say 5 or 6. But it wont look good if there is a large number of people assigned to it. say 20people. A single row will show the entire list of 20 people, If its a multi select list, it is fixed with a certain height and we can scroll through it. - Ashwin
It's not really a tabular form right? From the header styling I'd guess it's an interactive report. Is it? If it's for display purposes, you probably also have search-capabilities. You understand that this approach isn't really friendly in that way, right? How would someone be able to use the search functionality to find the tasks a user is assigned to? You'd need another page of course. How necessary is it to display like this? f you want search, why not generate multiple lines per task? You could create another saved report with grouping to only show the tasks then for example. - Tom
Thanks for your valuable answers, @Tom Nope, this is not an interactive report, this is a tabular form only and I have been told to do this in a single page only. By the way, I found how to do it. I just created a normal select list and added some dynamic action to bring the values into it. And finaly I converted that select list into a multiselect list by adding 'multiple size = 5' so that only the first 5 names will be visible and other names need to be scrolled. - Ashwin
Ok, no problem. Just asking to make sure. Feel free to answer your own question! And even though you found an answer, I still posted one myself, and probably is better than having to implement a dynamic action with plenty of code. - Tom

1 Answers

0
votes

Instead of using SELECT_LIST_FROM_LOV you could use SELECT_LIST_FROM_SQL (and _XL if you think the return will be >4000 characters).

From the example: 

SELECT APEX_ITEM.SELECT_LIST_FROM_QUERY(3,job,'SELECT DISTINCT job FROM emp')job 
FROM emp

So you could use (for example)

SELECT APEX_ITEM.SELECT_LIST_FROM_QUERY(3,job,'SELECT username FROM work_user_assignments WHERE assignment_id = '||id) users 
from x