Skip to Main Content
  • Questions
  • insert only one row on IG report depends on value of page item

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Huy.

Asked: October 09, 2020 - 4:35 am UTC

Answered by: Connor McDonald - Last updated: October 15, 2020 - 4:32 am UTC

Category: Application Express - Version: 20.1.0.00.13

Viewed 100+ times

You Asked

Hi everyone,
I have a challenge case as: I have a select list page item and one IG report. I want to when I choose a page item valued by 'W', it allows IG report only one row. While I choose another value, it still allows many rows for IG report.

I try after submit with code :
Declare 
v_count number;
Begin

select count(*)
into v_count
from E_EDUFIT_MARKS_DETAIL md, e_edufit_marks m
where md.marks_id = m.marks_id
and m.type_marks = 'W'
AND md.marks_id = :P2645_MARKS_ID;

if v_count > 1  then return 'FALSE';
end if;
end;


I think it may be successfully when one row is created before. I wonder that when create new records, user click add row (over 2 rows) it wil be not run. I have not found a way to solve that problem. I hope you help me have a good idea. Thank in advance.

and we said...

I think you would be better off with a unique constraint on the database here, because otherwise even if the code works, multiple users will be able to do the operation at the same time.

For example, to have a single row of type W and multiple rows of anything else for (say) each employee, you would do:

create unique index IX on EMP  ( case when type = 'W' then empno end );

assuming here that empno is the primary key.

and you rated our response

  (2 ratings)

Reviews

Not done for my question

October 14, 2020 - 7:59 am UTC

Reviewer: A reader

Thank for your code. I try it which follows your sample, I run it on DB:
create unique index IX on e_edufit_marks m  ( case when m.type_marks = 'W' then m.marks_id end );

where marks_id is a primary key

But when I test on oracle apex with page_item selected 'W', it still allow add over 1 row. Can you make more clear for me?
Connor McDonald

Followup  

October 15, 2020 - 4:32 am UTC

What happens when you press Save in the IG ?

Follow

October 16, 2020 - 6:18 am UTC

Reviewer: A reader

When I press Save on IG button, it will allow one row for any value of page_item. And when editing the old record, Save button process nothing.

But I do not use Save button to create a new record.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.