Hello,
I have a problem in my app.
When I click on edit link in interactive report, page refreshes and opens an entry with the ID that I clicked on, from a table connected to report.
- Table from the interactive report is:
Table1
ID | Number_ID | Other Info
1 | 1 | White
2 | 2 | Blue
3 | 4 | Orange
4 | 2 | Red
5 | 3 | Green
***********
Number_ID contains the ID of the number value from Table2
- Table That has the number value is:
Table2
ID | Number | Date
1 | 123 | 1.1.23
2 | 123 | 2.2.22
3 | 123 | 3.3.21
4 | 234 | 2.3.12
**************
This Table2 has same numbers with different date
I have a Region R that is declared as Form and connected to Table1.
I have an Item P22_ID that is connected to the ID from Table1.
I have an Item P22_Number defined as PopupLOV, and its List Of Values is PL/SQL defined:
declare
v_sql varchar2(4000);
begin
if :P22_ID is not null then
v_sql :='
select Table2.Number as Number,
min(Table2.ID) as ID
from Table2 Table2
group by Number
';
end if;
return v_sql;
end;
This code should say that when I select specific ID from interactive report linked to the same page, sending the ID selected to the P22_ID item (editing mode), SQL query inside v_sql variable should be executed.
P22_Number Item has source set to:
Region: R
Column: Number_ID
Data: Number
This is all ok until this point, and everything works.
The min aggregation function is used because I get multiple LOV selections when one Number value has multiple ID-s, and I do not want this, so min solves this issue.
Main problem is because I used min function (I tried with max as well), when I click to edit one Number, in edit mode, P22_Number shows the Number value, and when I click to edit another number, P22_Number shows ID of that number. I do not want ID to be shown, I only want Number value.
Interactive report:
EDIT | 1 | White
EDIT | 2 | Blue
EDIT | 4 | Orange
EDIT | 2 | Red
EDIT | 3 | Green
When I click to edit first entry, P22_Number = 1.
When I click to edit second entry P22_Number = 123. This case is preferred.
When I remove min form SQL code:
select Table2.Number as Number,
Table2.ID as ID
from Table2 Table2
I always get Number value shown when Editing an entry, but the LOV would have multiple selection options for the Number 123, for example:
LOV:
123,
123,
123,
234
and this is reasonable because number 123 has different ID-s.
I am banging my head to understand why is this happening.
Can anyone help?
Thanks!
M.P.