Creating Dynamic LOVs

Question

I have a form in oracle apex with more than seven items on it. they are

SUBJECT_ID,GRADE_ID,DOMAIN_ID, CATEGORY_ID, STANDARD_CODE, STANADARD_STATEMENT, LEARNING_TARGETS.

I want these items SUBJECT_ID,GRADE_ID,DOMAIN_ID, CATEGORY_ID, STANDARD_CODE type to be select list. additionally, I want to make LOVs for each of these items.

LOV for SUBJECT_ID: I am making this LOV using a table SUBJECTS having TWO columns. MY query is SELECT SUBJECT_ID, SUBJECT_NAME FROM SUBJECTS It's working fine.

LOV for GRADE_ID: I am making this LOV using a table GRADES having TWO columns. MY query is SELECT GRADE_ID, GRADE_NAME FROM GRADES It's working fine.

LOV for DOMAIN_ID: I am making this LOV using a table DOMAIN having TRHEE columns. MY query is SELECT DOMAIN_ID, DOMAIN_NAME FROM DOMAIN WHERE SUBJECT=:P48_SUBJECT_ID. It's working fine.

LOV for CATEGORY_ID: I am making this LOV using a table CATEGORIES having FOUR columns. MY query is SELECT CATEGORY_ID, CATEGORY_NAME FROM CATEGORIES WHERE DOMAIN=:P4.8_DOMAIN_ID It's working fine.

LOV for STANDARD_CODE: I am making this LOV using a table CURRICULUM having MORE THAN EIGHT columns. MY query is SELECT CURRICULUM_ID CI, STANDARD_CODE SC FROM CURRICULUM WHERE SUBJECT=:P48_SUBJECT_ID AND GRADE_ID=:P48_GRADE_ID AND DOMAIN_ID=:P48_DOMAIN_ID AND CATEGORY_ID=:P48_CATEGORY_ID. It's not working for me.

Kindly tell me how I can correct the 5th LOV. Thanks

"It's not working for me." is not a constructive statement as it does not tell us what the error is or why you think it is not working. Please [edit] the question and include a complete description of the issue/error. — MT0, Jul 26 '22 at 09:07

score 1 · Answer 1 · answered Jul 26 '22 at 09:27

I wouldn't say that any of LoV queries you posted return desired result and "work fine". Their format should be:

select display_value,  --> you see it on the screen
       return_value    --> you don't see it; it is stored into the table
from ...

Code you posted suggest just the opposite, e.g.

SELECT SUBJECT_ID,     --> are you REALLY displaying ID to users and
       SUBJECT_NAME    --> storing NAME into the table?
FROM SUBJECTS

As of your final LoV: just as MT0 commented, we have no idea what "not working" means. You posted a whole lot of more or less useless information (queries that "work"; what should we do with them?), but said nothing about problem you have.

Therefore, I'll guess: you forgot to include

P48_SUBJECT_ID, P48_GRADE_ID, P48_DOMAIN_ID, P48_CATEGORY_ID

into the Parent Item(s) property within the "Cascading List of Values" section, e.g.

Note that query you posted presumes that all page items have a value; if any of these is NULL, query won't return anything so that would be my second guess:

SELECT curriculum_id ci, standard_code sc
  FROM curriculum
 WHERE     (   subject = :P48_SUBJECT_ID
            OR :P48_SUBJECT_ID IS NULL)
       AND (   grade_id = :P48_GRADE_ID
            OR :P48_GRADE_ID IS NULL)
       AND (   domain_id = :P48_DOMAIN_ID
            OR :P48_DOMAIN_ID IS NULL)
       AND (   category_id = :P48_CATEGORY_ID
            OR :P48_CATEGORY_ID IS NULL)

In that case, switch the "Parent required" property OFF.

Thank you all for the suggestions and comments. Thankfully, the problem is resolved. I was selecting the display and return values for the first four LOVs. The problem was only with the fifth one. Instead of creating LOV in the shared components, I directly wrote the SQL query on the list of values. And submitted the category_id. So it worked. Cheers — tayyeb, Jul 27 '22 at 09:08

Creating Dynamic LOVs

1 Answers1