Forum Stats

  • 3,769,367 Users
  • 2,252,958 Discussions
  • 7,875,006 Comments

Discussions

APEX "Child Form" cannot load data, if DB not auto creating the PK

George...
George... Member Posts: 4 Blue Ribbon
edited Mar 29, 2021 9:22PM in APEX Discussions

Hi, all.

I have a problem that is vexing me a great deal.

My simple table is connected to a form. There is a PK, which is NOT IDENTITY generated.

My Form is ALWAYS passed a valid PK, and if the value does not exist in the table, then a NEW record is created as expected.

However, if the value does exist in the table, then the values are NOT being collected back into the form, and therefore the save on submit is also failing.

My Processor is Init, and I have also tried using ARF to no avail.

FWIW, everything works as it should of course if I change the table to make the ID Autocreated, but this is not how this must work. I have to seed the value for NEW records (which works) and use the same value to look up and edit the table - which does not.

I hope someone knows the answer, and can assist. I obviously havent "tried everything" (yet) or it would be working - but I have spent a huge amount of time trying the wrong things :-(

George ...

Tagged:

Best Answer

  • George...
    George... Member Posts: 4 Blue Ribbon
    Accepted Answer

    All, I was able to make this work eventually.

    For the benefit of anyone else who reads this here is what I learned.

    The wizard creates a form that by default uses the presence or absence of a value in the PKID to control visibility of the create/update controls and the associated SQL (Insert/Update).

    The "Processors" ARF and INIT use the same logic and cannot easily be changed. To create a new record the PKID field MUST be empty on Load. Because I have to provide that data I push it to a hidden field on calling the FORM and use JS to plant it onto the PKID field after the SUBMIT.

    The tricky part about all this is in the CALLING page. My LINK does not know when it is pressed if it is to update or create a record, and therefore CANNOT be used to call the FORM directly :-(

    So, my LINK points to a Javascript Function on the calling page which performs a SUBMIT. From there it is straightforward to create a couple of branches and test for an existing record in the DB. two branches means two links and one of them populates the PKID on the form, the other does not. This controls the New/Update logic, and as mentioned earlier, the FORM submit puts the PKID where it is needed using JS.

    Extreme Kudos to Vinish Kapoor for providing the key to all of this which was the JS call to perform the submit.

    Thanks to each of you who put effort to read and attempt to help.

    George ...

Answers

  • AndyH
    AndyH Member Posts: 749 Bronze Trophy

    How about creating a new page using the Form Wizard and compare what it builds with your page?

    It's unusual to pass an intended PK to a page - its presence/absence is often used to determine whether the page is meant to be creating a new row or updating a current one.

  • InoL
    InoL Member Posts: 9,341 Gold Crown

    On the form, did you specify the correct item as the primary key item?

    Maybe your form uses ROWID as PK?

  • George...
    George... Member Posts: 4 Blue Ribbon
    Accepted Answer

    All, I was able to make this work eventually.

    For the benefit of anyone else who reads this here is what I learned.

    The wizard creates a form that by default uses the presence or absence of a value in the PKID to control visibility of the create/update controls and the associated SQL (Insert/Update).

    The "Processors" ARF and INIT use the same logic and cannot easily be changed. To create a new record the PKID field MUST be empty on Load. Because I have to provide that data I push it to a hidden field on calling the FORM and use JS to plant it onto the PKID field after the SUBMIT.

    The tricky part about all this is in the CALLING page. My LINK does not know when it is pressed if it is to update or create a record, and therefore CANNOT be used to call the FORM directly :-(

    So, my LINK points to a Javascript Function on the calling page which performs a SUBMIT. From there it is straightforward to create a couple of branches and test for an existing record in the DB. two branches means two links and one of them populates the PKID on the form, the other does not. This controls the New/Update logic, and as mentioned earlier, the FORM submit puts the PKID where it is needed using JS.

    Extreme Kudos to Vinish Kapoor for providing the key to all of this which was the JS call to perform the submit.

    Thanks to each of you who put effort to read and attempt to help.

    George ...

  • InoL
    InoL Member Posts: 9,341 Gold Crown

    Because I have to provide that data I push it to a hidden field on calling the FORM and use JS to plant it onto the PKID field after the SUBMIT.

    You can use the Default Value of the PK item. Sounds much easier.

    Or use a process just before the DML process to get the PK value when submitting the page.

     My LINK does not know when it is pressed if it is to update or create a record,

    Can you explain that? Apex usually works with an Edit icon for an existing record, and a separate Create button to create a new record. How are you creating this one link for both Edit and Create?

  • AndyH
    AndyH Member Posts: 749 Bronze Trophy

    So, my LINK points to a Javascript Function on the calling page which performs a SUBMIT

    An alternative that might allow the page to act in a more 'standard' manner would be to have your link set a hidden value in the page. Then have a process before the form initialisation process that runs if the hidden value matches a record - if so, the process populates the primary key. The standard form initialisation process should then see this as a record to update from the database. You could then modify the standard post-submit PK process (usually called when in 'SAVE' mode) to populate the PK from the hidden field.

  • George...
    George... Member Posts: 4 Blue Ribbon

    Thanks for the suggestions. I see complexities in both the solution that we chose and those that are suggested.

    INOL asked a question that indicates (to me) that I had not completely explained the situation that causes this issue. Standard APEX uses two entry methods to a FORM. Usually a button to ADD a record and a Link to EDIT.

    In my case I have ONLY one link, which carries the PKID. At the time it is pressed the LINK does not know wheter there is or is not a record to edit! How is this so? Good question.

    My data source is a form Gantt Chart (JET). Each entry in the chart is a specific timeline element for a machine in our manufacturing plant. So the Horizontal Axis is (as usual in Gantt) Time. Y Axis is Machines, and the data elements (Tasks) are "Operator Shifts".

    The purpose of the tool is "assign actual operator names" to each shift. The Gantt gives up an ID for each TASK, but the related table of Operators is completely empty until people are selected and added. Only when we click on a task is there exposure of the PKID that will be used in the ADD/EDIT process using the FORM.

    I get that this isnt the norm for an APEX parent/child implementation, but then the real world doesnt have to conform to what APEX expects :-) Fortunately the solution we came up with works really well, is quite simple and easy enough to understand. Making the page submit after a call to JS also allows some other functionality to be implenmented, then we drop back into the branching that is out of the box from APEX.

    Thanks again to all for the suggestions. I added this additional detail as much for the benefit of others. Nobody should have to struggle as I did with this.