Forum Stats

  • 3,770,167 Users
  • 2,253,079 Discussions
  • 7,875,357 Comments

Discussions

In v20.1 Is it possible to setup a FormRegion's items independent of a report?

Amba
Amba Member Posts: 36 Green Ribbon
edited Jul 3, 2020 7:33AM in APEX Discussions

Hello

v20.1 has great new features for app development. My objective is to create a form, based on a SQL or Table/View that is independent of a Report.

To illustrate, I used APEX's sample EMP table and a  "LISTVIEW FORM based on Table/View" wizard process to create PAGE 6 (the report) and PAGE 7 (the form) which operates just as expected.  The important part is ListView Report region's Attribute LINK target that sets P7_EMPNO = \&EMPNO.\ which provides the key to the PAGE 7 FORM region to load up the data in it. Wonderful! (figure 1)

However, I am not able to re-create this effect on my own without PAGE 6. I want to use PAGE 7 (I tried using the Initialisation Process) see figure 2 to set my custom P7_EMPNO_USR item with a Value (the item has a preset empno key# 7839 just for testing..) but APEX does not fetch the data from the table.

What is the _best_ way to do this? .. I am keen to get this working and would like to do it in accordance to APEX good practice... if it is possible, I want to attempt building form based applications using APEX. thank you!  btw, I'd like to avoid PL/SQL if at all possible..

[PS: I have tried to setup a separate Region:StaticContent with P7_EMPNO_USR and a CUSTOM_GO_BUTTON item --> DynamicAction[ which refreshes the Form Region] to no avail...]]

Screen Shot 2020-07-02 at 6.40.08 PM.png

pastedImage_6.png

Tagged:
KeyserSven W.

Best Answer

  • Keyser
    Keyser Member Posts: 829 Bronze Trophy
    edited Jul 2, 2020 3:49AM Accepted Answer

    You've changed the "Initialize form" process from "Form Initialization" to "User Preferences"

    Form - Initialization

    Process to initialize form region items. Initialization can either be fetching data from the region source, using the primary key value(s) or simple initialization of the form region items.

    User PreferencesProcess to set User Preferences for the end user.

    So you've told it not to do all the good stuff i.e. fetching and do my stuff instead

    Change it back to "Form Initialization"

    Add a new PL/SQL process before the "Form Initialization" process to set the EMPNO item (:P13_EMPNO := 7698;)

    e.g.

    pastedImage_3.png

    and then run the page

    Amba
«1

Answers

  • Sven W.
    Sven W. Member Posts: 10,534 Gold Crown
    edited Jul 2, 2020 3:06AM

    Yes that is easily possible.


    I'm not sure why it doesn't work in your case. I think you are missing just a tiny part somewhere.
    Do you have a form region on page 7? The item then needs to be connected to this form. Only then will the row fetch process during page load populate this item.

    Check the source properties of the item. Type might need to be database column.

    pastedImage_0.png

  • Amba
    Amba Member Posts: 36 Green Ribbon
    edited Jul 2, 2020 3:19AM

    thanks @sven W. yes it has a form Region:FormListView and its linked to source:LocalDatabase with TableView to table EMP.

    What do you mean by item in this case? my custom item P7_EMPNO_USR is also linked to the form region..

    puzzling indeed..

    pastedImage_0.png

  • Amba
    Amba Member Posts: 36 Green Ribbon
    edited Jul 2, 2020 3:22AM

    All I get is a blank/NULL form with my custom Item value in it...

    pastedImage_0.png

  • Keyser
    Keyser Member Posts: 829 Bronze Trophy
    edited Jul 2, 2020 3:49AM Accepted Answer

    You've changed the "Initialize form" process from "Form Initialization" to "User Preferences"

    Form - Initialization

    Process to initialize form region items. Initialization can either be fetching data from the region source, using the primary key value(s) or simple initialization of the form region items.

    User PreferencesProcess to set User Preferences for the end user.

    So you've told it not to do all the good stuff i.e. fetching and do my stuff instead

    Change it back to "Form Initialization"

    Add a new PL/SQL process before the "Form Initialization" process to set the EMPNO item (:P13_EMPNO := 7698;)

    e.g.

    pastedImage_3.png

    and then run the page

    Amba
  • Amba
    Amba Member Posts: 36 Green Ribbon
    edited Jul 2, 2020 4:34AM

    Thanks @Keyser  I think that makes a lot of sense...best to let APEX to things automatically as much as possible...

    It should have worked but it didn't I'll keep trying..

    [ It is interesting that in your example screenshot, the Editable Region is blank. More on that below..]

    First: I changed my Initialization Form process to 'Form - Initialization' as per your note..
    I changed my Region:FormListView's Source to SQL Query, accepting the default but adding  this in the WHERE clause...

    select ROWID, EMPNO, ...from EMP where EMPNO = :P7_EMPNO_USR

    In principle, that should have worked?

    Second: As above, I changed my Initialization Form process to 'Form - Initialization'  as per your note..

    I added a new Process: InitFormWithUSR above the Form Initialization of type  PL/SQL and put in the statement

    :P7_EMPNO_USR := 7839;

    I saved and ran it but I got the same blank/null form.

    Now, I changed the Editable Region to the Region:FormListView and I got an error with the PL/SQL process: InitFormwithUSR

    ora_sqlcode: -6502

    ora_sqlerrm: ORA-06502: PL/SQL: numeric or value error

    Interestingly any statement in pl/sql like (1=1;) gives the same error with the Editable Region set.

    It makes no difference whether I set the Region:FormListView's source to be SQL or Table/View in this case, I get the same error.

    DK if its important but Item P7_EMPNO is a Primary Key, Hidden and Protected. I changed the Protected switch but it still dosn't make any difference.

    I am probably missing something really small and overcomplicating it ..

    thanks again

  • Amba
    Amba Member Posts: 36 Green Ribbon
    edited Jul 2, 2020 4:52AM

    wow. it finally worked @Keyser

    I added both statements to the PL/SQL and with the EditableRegion unset, like in your example.

    :P7_EMPNO_USR := 7839;

    :P7_EMPNO := 7839;

    then, I realized that my custom item being unset was causing APEX to throw an error which is what the ORA error was.

    now this works with my .._USR item set as optional.

    It also works with just the (:P7_EMPNO := 7839;)

    Thanks a lot @Keyser and @Sven W  I will definitely get further with APEX forms development now. PL/SQL is a steep learning curve but I'll start with the basics..

    KeyserSven W.
  • Amba
    Amba Member Posts: 36 Green Ribbon
    edited Jul 2, 2020 4:54AM

    haven't worked out _why_ the EditableRegion should be unset, though..

  • Keyser
    Keyser Member Posts: 829 Bronze Trophy
    edited Jul 2, 2020 5:30AM

    good, i'm glad it's working for you

    My objective is to create a form, based on a SQL or Table/View that is independent of a Report.

    now change this...

    :P7_EMPNO := 7839;

    to this...

    :P7_EMPNO := :P7_EMPNO_USR;

    Add a "Submit" button

    run the page, type in a valid empno and click the button

    pastedImage_34.png

    pastedImage_37.png

    as far as i'm aware you can't refresh a form region as you would with an IR or IG so you have to submit the page instead (with the button), it then runs your 2 page processes and displays the appropriate row

    Amba
  • Amba
    Amba Member Posts: 36 Green Ribbon
    edited Jul 3, 2020 12:53AM

    thanks again @Keyser that just worked!   The form even updates the EMP table  via the default form settings like the  pre-defined Update button etc.  That is truly fast  development.

    Re:

    as far as i'm aware you can't refresh a form region as you would with an IR or IG so you have to submit the page instead (with the button)

    thanks for that ~ explains why my earlier experiments with DynamicActions and Refresh did not work. AFIK it isn't documented anywhere..

    [Incidentally, for the benefit of others reading this thread, I found this excellent article from v5.1  that implements my use case. https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/3341/index-en.html  and there are a number of options for the implementation.]

    Incidentally, would you recommend a good book for AJAX by APEX that is up to date (obviously not to v20.1 though close enough)?

  • Keyser
    Keyser Member Posts: 829 Bronze Trophy
    edited Jul 3, 2020 2:43AM

    I've been waiting for Dimitri Gielis to publish his book that is hinted on at his website because I like the way that he writes his blog but I think he's busy with a lot of other Apex related work.

    It might be a good idea to start a new thread on here and ask the community for book recommendations

    If you go to https://apex.oracle.com/shortcuts/  there is a link to "Books" but there hasn't been any recent additions

    There is also a link to the "Hands On Labs"

    I would recommend the "Oracle Application Express - Curriculum", download and go through the powerpoint presentations (get a 30 day free powerpoint trial if you don't have it already), it gives a high level overview of a lot of Apex features

    Amba