CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
J.J.
J.J.

Reputation: 127

Oracle Apex 20 Many to Many Relationship - Adding records

I am a beginner in APEX and I need help with making many-to-many model work in apex. What seemed quite straightforward in ACCESS has been giving me headache for weeks now. Basically what I am trying to do is to generate record when i expedite goods from our warehouse (each expedition from our warehouse can have multiple items at certain quantities) I have following tables:

EXPEDITION_RECIEPT:

create table EXPEDITION_RECIEPT (
    id                             number, pk,
    info                           varchar2(4000 char)
)
;

MASTER_ITEMS:

create table MASTER_ITEMS (
    id                             number, pk,
    description                    varchar2(4000 char)
)
;

MASTER_ITEMS_EXPEDITION_RECIEPT:

create table MASTER_ITEMS_EXPEDITION_RECIEPT (
    id                             number, pk,
    EXPEDITION_RECIEPT_ID          FK,
    MASTER_ITEM_ID                 FK,
    QTY                            NUM
)
;

I looked into Master-Detail Pages but to my understanding this is mainly applicable for One-to-Many relationships. I havent found way to make my model work with Master-Detail. My intuition tells me that I should use collections. I have managed to find a way to populate my table with collections but the problem is that once i create the record with colletcions and add it into my MASTER_ITEMS_EXPEDITION_RECIEPT table I have no longer option to open it again and make changes to it.

Can someone please give me any suggestion on what is the proper way to do this and dirwct me to some example. I imagine that most of bussiness oriented apps have this somehow implemented.

Thank you for any suggestion.

Jakub

Upvotes: -1

Views: 388

Answers (1)

Littlefoot
Littlefoot

Reputation: 143013

Excuse me, but - how is this many-to-many relationship? One expedition receipt can have one or many items. If so, that's one-to-many.

Tables you posted look OK to me.

Master-detail page would have

  • expedition_receipt as a master and
  • an interactive grid (based on master_items_expedition_receipt table) as a detail
  • expedition_receipt_id column would inherit its value from the master region
  • you'd select master_item_id from a LoV (list of values) (either a select list, or a pop-up LoV)

Upvotes: 1

Related Questions