Hi, I have run into a situation with PK/FK relationships slightly different than everything else I have ran into so far, as an Oracle APEX developer.
I am on APEX 21.1.5.
I have a table that looks like this, called Person:
- Name (VARCHAR2) - Name of the person
- PhoneNumber (NUMBER) - The main phone number of the individual
- AddressId (NUMBER) - FK to the Address table
The Address table looks like this:
- Id (NUMBER) - Unique identifier / PK
- StreetAddress (VARCHAR2) - Street address
- City (VARCHAR2) - City
- Etc.
In my APEX application, I have an Interactive Grid providing access to the Person table. Now, what follows is just what I have come up with, but I am open to designing things differently. One of the columns has the following details:
- Type: Link
- Link: Page in this Application
- Set Items
- Name: P5_ADDRESSID → Value: &ID.
- Link Text: <img src="#IMAGE_PREFIX#app_ui/img/icons/apex-edit-view.png" class="apex-edit-view" alt="">
- Source: Database Column (Person.AddressId)
The link text is a magnifying glass. When I click the magnifying glass, it opens up a Modal Dialog that allows me to View, Modify, Create, or Delete the Address (in a Form region) for the given Person, based on whether the incoming AddressId is null.
The problem I am running into is this: while I can access the Address for a given Person, performing any CRUD operations on the given Address doesn't update the Person.AddressId column. For example, if a given Person doesn't have an associated Address record, I can click the magnifying class that then links to the page to manage an address. Since the AddressId was null, I have the create button. I enter in the address info, click Create, and on the database side I see a new record with the info I entered, but I cannot access that record through the UI because, of course, the Person.AddressId field was not updated.
So what is the APEX/low-code/declarative way of doing this? How can I update Person.AddressId if I perform updates to the corresponding Address record? I thought about adding a DA to my CREATE, SAVE, DELETE buttons, but looking at the various Actions, I don't see a way to save/update/delete Person.AddressId without writing a PL/SQL procedure, and I would like to avoid that if possible. It seems to me like I simply haven't set up these two pages in the correct arrangement, and there is probably some “best practice” or perhaps “out of the box” way to do this that I haven't figured out yet.
Note that the relationship between Person and Address is 1-to-1, with an Address belonging to the Person record. If a Person record that had an Address record was deleted, I would cascade the deletion to the Address record, but if an Address is deleted, the Person record to which the Address belongs would not necessarily be deleted.