Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Creating items based on a pl/sql function OR referencing report columns in a pl/sql query (APEX 4.0)
Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #543869] Fri, 17 February 2012 05:13 Go to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hello Smile

I have the following situation which I have thought of a way to resolve (but which I can't do...):
I have a form on a page to enter the details of a new project.
For each project I need to have X rows created in table MILESTONES containing the project ID, each checkpoint ID and a DATE for each checkpoint. (Where X is the number of checkpoints.)

So for example I create example_project, ID = 987. there are 3 checkpoints A, B and C
the milestone table should look like:
PROJECT_ID     CHECKPOINT   DATE
987            A            21.02.2012
987            B            04.03.2012
987            C            15.03.2012

To enter that into the database with PL/SQL, I can do BUT the problem is that table CHECKPOINTS is editable. So the number of checkpoints can change.

My initial thought was just to create a report/tabular form displaying each checkpoint with a datepicker field. The problem is that I don't know how (or even if you can) reference a specific report field in the PL/SQL function.

If that's the best way to go about this, how do I do it? and if not, how should I go about the whole thing?

thanks Smile
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #543905 is a reply to message #543869] Fri, 17 February 2012 09:28 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
I'm not completely sure what it is you want to do and what it is you have problems with. Have you already figured out how to enter the entries into the milestones table and just want to edit these dates? Or don't you have the rows in your milestones table at all and need to insert them? And what exactly do you want to do in your pl/sql function which references these dates?
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544093 is a reply to message #543905] Mon, 20 February 2012 02:27 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Sorry I wasn't clear, I'll try again Razz

On the page, I create the project. I then need to enter the rows into the milestone table. (So, yes, insert them not edit them)
Without the date, I think I can do it just not when I need to have them enter the date themselves.

The PL/SQL is just the insert process.

Hope that clears it up...
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544114 is a reply to message #544093] Mon, 20 February 2012 04:11 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Are the checkpoints in another table? If you want an editable field then you can use the APEX_ITEM package. So in your region you could then have
select 
    apex_item.display_and_save(1, checkpoint) checkpoint, 
    apex_item.date_popup2(2, null, 'DD-MON-YY') milestone_date
from checkpoints
where <<whatever conditions you have>>


And in your process (assuming P1_PROJECT_ID holds the id of the recently inserted project):
begin
    for i in 1..apex_application.g_f01.count loop 
        --apex_application.g_f01 holds the values for items you've given number 1 to
        --and g_f02 holds the ones with number 2

        insert into milestones(project_id, checkpoint, date)
        values(:P1_PROJECT_ID, apex_application.g_f01(i), apex_application.g_f02(i));
    end loop;
end;


If you don't have this checkpoint table then there is still a technique for doing this, but unless you actually need it I won't confuse you with it Smile Just let me know if this is what you needed.
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544118 is a reply to message #544114] Mon, 20 February 2012 04:30 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Quote:
but unless you actually need it I won't confuse you with it


haha thank you for the consideration Razz

As far as I can tell this is what I was after
just one thing, my checkpoint table has the ID and the Checkpoint name, so to display the name but have the ID in the other table would this work:
select 
    apex_item.hidden(1, checkpoint_ID) Checkpoint_ID,
    apex_item.display_and_save(2, checkpoint) checkpoint_name, 
    apex_item.date_popup2(3, null, 'DD-MON-YY') milestone_date
from checkpoints
then
begin
    for i in 1..apex_application.g_f01.count loop 
                insert into milestones(project_id, checkpoint_ID, date)
        values(:P1_PROJECT_ID, apex_application.g_f01(i), apex_application.g_f03(i));
    end loop;
end;


thanks

[Updated on: Mon, 20 February 2012 04:31]

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544123 is a reply to message #544118] Mon, 20 February 2012 04:40 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Yes it would work. But what I would rather do is:
select 
    apex_item.hidden(1, checkpoint_ID) || checkpoint checkpoint_name,
    apex_item.date_popup2(2, null, 'DD-MON-YY') milestone_date
from checkpoints


What you can see is that I removed the "display_and_save" for the text of the checkpoint name (you don't need it in your process, so there's no need to save it), and I just concatenated your hidden value with the checkpoint name. If you put the hidden field in a column of its own the report will show an empty column. By concatenating the hidden field with the display value you will see the name of the checkpoint, but the id is what's stored. Then you can just use the process from my previous post.
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544127 is a reply to message #544123] Mon, 20 February 2012 04:43 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Ok, perfect! Thanks Smile
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544132 is a reply to message #544127] Mon, 20 February 2012 05:17 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hi again, got a bit of a problem,

THe MILESTONE table has a Primary Key on 'ID', so I need another hidden column with a new ID for each checkpoint...

I tried:
begin
    for i in 1..apex_application.g_f01.count loop 
        insert into PMO_MILESTONES(MILESTONE_ID, CHECKPOINT_ID, PROJECT_ID, DATE)
        values((select S_PROJECT_MILESTONE.nextval next_val from dual),
 apex_application.g_f01(i), :P4_PROJECT_ID, apex_application.g_f02(i));
    end loop;
end;

but it didn't like me doing that...(it said: sequence number not allowed here )
Can I put a default value in a hidden apex_item?
thanks
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544149 is a reply to message #544132] Mon, 20 February 2012 06:09 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try with
insert into PMO_MILESTONES
  (MILESTONE_ID, 
   CHECKPOINT_ID, 
   PROJECT_ID, 
   DATE
  )
   values
  (S_PROJECT_MILESTONE.nextval, 
   apex_application.g_f01(i), 
  :P4_PROJECT_ID, 
   apex_application.g_f02(i)
  );
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544159 is a reply to message #544149] Mon, 20 February 2012 06:33 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
That works, thanks

Just one last thing, I've got the format for the date as 'DD-MON-YY HH24:MI' in the APEX_ITEM. But when I select a time later than 12 o'clock it says the hour must be between 1 and 12...I'm not sure why because it doesn't have to be in the database, I've got times 16:30 and 13:15 already and there was no problem with them...
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544163 is a reply to message #544159] Mon, 20 February 2012 06:46 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Try to do a to_date(apex_application.g_f02(i), 'DD-MON-YY HH24:MI')
I believe the date picker might be returning a string.
Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544164 is a reply to message #544163] Mon, 20 February 2012 06:50 Go to previous message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
You were right it was (well I assume you were right... Razz ). Thanks for the help its all working now Smile
Previous Topic: switch for choosing different values form column
Next Topic: Select approved/rejected for each Role if N° of roles can change
Goto Forum:
  


Current Time: Tue Mar 19 00:18:24 CDT 2024