This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Nov 4, 2013 4:35 AM by newbi_egy RSS

detail tabular form

newbi_egy Explorer
Currently Being Moderated

db11gxe , apex 4.0 , firefox 24 ,

i have a master form and a detail tabular form in a page ,

the master is based on table "Patients" , and the detail is based on "Patient_tests" ,

i insert the details of the patients in the master , and the tests in the detail tabular form

 

what i want is :

create a tabular item beside the tabular form to view the "Price" of each test inserted in the "test"column of the "Patient_tests" tabular form , but i do not know how .

 

the price is existed in a table called "Tests" which has the tests details .

how to do this task ?

 

thanks in advance

  • 1. Re: detail tabular form
    Nicolette Expert
    Currently Being Moderated

    Newbi_egy

     

    The "Price" do you want to display it in a different region?

    Or do you want it to display as a column in the "Patient test" tabular form?

     

    The "Price" does it need to be edible or is it display only?

     

    Apex has the limitation to only have one wizard created tabular form on a page.

     

    Nicolette

  • 2. Re: detail tabular form
    newbi_egy Explorer
    Currently Being Moderated

    The "Price" do you want to display it in a different region?

    no , it would be better if it is in the same region .

     

    The "Price" does it need to be edible or is it display only?

    it would also be better if it is display only .

     

    Apex has the limitation to only have one wizard created tabular form on a page.

    i think this means that i can not create another tabular form ?

     

    btw : the test_name column of the pat_tests region is a "pop-up lov" based on a query ,

    what i want is when the user selects a value from the pop-up lov , the price of it is inserted into the price column beside it ,

    and when he selects another test from the pop-up lov in the second record of the pat_tests region , the price also is inserted into price column beside it ??

  • 3. Re: detail tabular form
    Nicolette Expert
    Currently Being Moderated

    Newbi_egy

     

    newbi_egy wrote:

     

    Apex has the limitation to only have one wizard created tabular form on a page.

    i think this means that i can not create another tabular form ?

    Yes this exactly what it means. But that is no problem in your case beceause you don't want to do that any way.

     

    btw : the test_name column of the pat_tests region is a "pop-up lov" based on a query ,

    what i want is when the user selects a value from the pop-up lov , the price of it is inserted into the price column beside it ,

    and when he selects another test from the pop-up lov in the second record of the pat_tests region , the price also is inserted into price column beside it ??

     

    To accomplish that you would need a dynamic action that will set the price depending on the test.

    What are your skill on javascrip and pl/sql? So my answers can take that into account.

     

    Can you set up what you have so far on apex.oracle.com.

    In this case it is easier to follow what needs to be done if I can have access to your application so that you can compare different settings.

    If it is not possible to use the patient, patient test and test tables. Use the demo order, order items and price information tables from the demo application.

     

    Nicolette

  • 4. Re: detail tabular form
    newbi_egy Explorer
    Currently Being Moderated

    > To accomplish that you would need a dynamic action that will set the price depending on the test.

    can i create dynamic action on a tabular item ?

    > What are your skill on javascrip and pl/sql? So my answers can take that into account.

    in java script i am zero skills , but in pl\sql i think i am good .

    >Can you set up what you have so far on apex.oracle.com.

    i think you only need the tabular form and the pat_tests and tests tables ?

    right ?

  • 5. Re: detail tabular form
    Nicolette Expert
    Currently Being Moderated

    Newbi_egy

     

    newbi_egy wrote:

     

    > To accomplish that you would need a dynamic action that will set the price depending on the test.

    can i create dynamic action on a tabular item ?

    Yes you can. You use a jQuery selector to bind the action to the elements. For more information read the jQuery API.

    And I will show you how in the demo you set up on apex.oracle.com.

     

    >Can you set up what you have so far on apex.oracle.com.

    i think you only need the tabular form and the pat_tests and tests tables ?

    right ?

    The more the demo you set up apex.oracle.com represents your actual situation the easier you make it for yourself in copying the steps back to your own application.

     

    Nicolette

  • 6. Re: detail tabular form
    newbi_egy Explorer
    Currently Being Moderated

    i have done it ,

    elbyaly

    eyesmagicx@yahoo.com

    decuk9

     

    but i do not know about the "price" column , should i create it in the table ? or can i create a tabular item not based on a column of a table ? or what ?

     

    the page is called "update" in the "patients" application

  • 7. Re: detail tabular form
    Nicolette Expert
    Currently Being Moderated

    Newbi_egy

     

    What I have done.

    I made a copy of your page so you can see te difference.

     

    • Part1 add  the price column to the tabular form
    1. Go to the report attributes of the tabular form.
    2. Under tasks click "Add Derived Column"
    3. Go to the column attributes of the derived columns
    4. Column Formating > HTML Expression =>
    5. <span id="PRICE_#ROWNUM#" name="PRICE">#DERIVED$01#</span>
    6. This will give you easier access to the price on the page.

     

    • Part 2 the definition of the dynamic action

    Event change

    Selection Type  jQuery selector

    jQuery Selector [name="f04"]

    Action  Execute Javascript Code

     

    The javascript code

    /*Declare the variable being used*/
    var row, priceElement, testElement,price,getPriceAJAX,price;
    /*Get the jQuery object of the DOM element that triggered the dynamic action.*/
    testElement = apex.jQuery('#'+this.triggeringElement.id);
    /*Get the row in which the triggering element is located*/
    row = testElement.closest('tr');
    /*Travers back to the element that holds the price*/
    priceElement = row.find('[name="PRICE"]');
    /*Create the object that is going to get the price from an ondemand proces*/
    getPriceAJAX  = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=get price',$v('pFlowStepId'));
    /*Add the value of the triggering element this value will be submitted and set to apex_application.g_x01*/
    getPriceAJAX.addParam('x01',$v(this.triggeringElement.id));
    /*Run the dynamic action and get the price*/
    price = getPriceAJAX.get();
    /*Set the price to the price element the pice element is a span so set the text*/  
    priceElement.text(price); 

     

    Explation of the parameters of  htmldb_Get

    object Object where the output variable could be set to but we are not using it.

    flow – Flow_id this is the application id. In javascript available through $v('pFlowId')

    req –  The request for an ondemand process is this 'APPLICATION_PROCESS= process name' The name of the ondemand process is case sensitive.

    page – The page id In javascript available through $v('pFlowStepId')

     

    • Part 3 the ondemand process.

    Create a pl/sql process that calculates the price.

    apex_application.g_x01 will hold the name of the test.

    use sys.htp.prn to print the price so it will returned by the htmldb_Get.get function.

    Sorry but I can't be more specific on how to calculated the price. I was expecting the price to be an attribute of the test but it isn't a column in the test table so I have no idea how to calculated the price.

     

    Nicolette

     

    Edited the javascript to correct the bug mentioned by newbi_egy in the tread Sending many parameters through the column link. Now it works for newly added rows too. Beware that priceElement.text(price);  only works when the pice is span or div element not an input element then .val() should be used. Nicolette

  • 8. Re: detail tabular form
    newbi_egy Explorer
    Currently Being Moderated

    would i need all of this javascript if i add the column to the pat_tests table ? and ofcourse to the tests table ?

  • 9. Re: detail tabular form
    Nicolette Expert
    Currently Being Moderated

    Newbi_egy

     

    You will need all of that javascript.

    Technically you could do it in fewer lines of code due to how jQuery works. But that would make the code harder to understand.

     

     

    As for adding a price column to the pat_test table. Only do that when the price is dependend on the patient.

    The pat_test table is an intersection table between the patient and test table to make a many to many relation between test and patient possible.

    Normaly the pat_test table would only hold a foreign key column to the patient table. And a foreign key column to the test table.

     

     

    Here is a course on datamodelling form Oracle

    And a lot of example datamodels

     

    Nicolette

  • 10. Re: detail tabular form
    newbi_egy Explorer
    Currently Being Moderated

    when i did this :

    > Part1 add  the price column to the tabular form

    from step 1 to step 6 ,

    the column is not displayed . i do not know if this is normal or not ?

     

    and about "part 2" , i did all you said but there was one step you did not mention which is "Select which page elements you would like the dynamic action to control."

    i choosed "jquery selector" , and wrote [name="f04"]

     

    about "part 3" the price is a column of the "tests" table , but i forgot to add it , i am sorry for that , i will add it now in order to see it when you go to the website again .

    i think you can do it for me and explain it if i do this ?

    thanks a lot to you

  • 11. Re: detail tabular form
    Nicolette Expert
    Currently Being Moderated

    Newbi_egy

     

    newbi_egy wrote:

     

    when i did this :

    > Part1 add  the price column to the tabular form

    from step 1 to step 6 ,

    the column is not displayed . i do not know if this is normal or not ?

    In your patient application on apex.oracle.com are the derivered columns displaying nicely.  So what do you mean exactly. You mean that the value is (null). That is beacause there is no dynamic action to calculate the value. And you didn't add <span id="PRICE_#ROWNUM#" name="PRICE">#DERIVED$01#</span> to the column formatting so even if there was a dynamic action as descripted in part 2 the javascript wouldn't work.

    If you mean that the header is empty that is because   is html for a non breaking space.

    Also note that the [row selector] column should always be the first column in a tabular form.

     

    and about "part 2" , i did all you said but there was one step you did not mention which is "Select which page elements you would like the dynamic action to control."

    i choosed "jquery selector" , and wrote [name="f04"]

    I'm sorry but read part 2 again and you will see that I did't write it as a step trough the wizzard but just listed all the settings. And the selector type and selector are mentioned

     

    about "part 3" the price is a column of the "tests" table , but i forgot to add it , i am sorry for that , i will add it now in order to see it when you go to the website again .

    i think you can do it for me and explain it if i do this ?

    Well I still don't see a price column in test table so if you need help with that part you should add it.

     

    If you make it first work on apex.oracle.com. I would be able to explain any steps that are missing.

     

    Nicolette

  • 12. Re: detail tabular form
    newbi_egy Explorer
    Currently Being Moderated

    i have modified all mistakes i did before :

    i have modified the patients table (droped the test_name column , and added a test_no column , then added a foreign key constraint between it and the

    test_no column of the "tests" table .

    2- i added the price column to the "tests" table with values .

     

    please explain to me how to deal with this situation ,

     

    thanks a lot

  • 13. Re: detail tabular form
    Nicolette Expert
    Currently Being Moderated

    Newbi_egy

     

    I have changed the display of the test no cloumn to "Popup key LOV(query based)". This displays the test name to the user instead of the test no.

    The get price is no changed to:

    declare
      cursor c_test(b_test_no in number)
      is
        select price
        from   tests
        where  test_no = b_test_no
      ;
      v_test_no number;
      v_price   number;
    begin
       v_test_no := to_number(apex_application.g_x01);
       open c_test(v_test_no);
       fetch c_test
       into  v_price;
       close c_test;
       sys.htp.prn(to_char(v_price,'L999G990D00'));
    end;

    This gets the price for the selected test and return it to the javscript.

     

    Nicolette

  • 14. Re: detail tabular form
    newbi_egy Explorer
    Currently Being Moderated

    i changed the dynamic action (get price) to execute pl\sql code and wrote the above code , but vainly , the price is not retrieved ?

    what about the on demand process ? can i delete it ? or can not ?

1 2 Previous Next