I am relatively new to APEX and still getting familiar with the nuances in functionality between various regions such as Forms, Interactive Grids, and and so forth.
I have a Page with an Interactive Grid. The IG has a Source of Type “Table / View”, let's say the source table is called TABLE_A. I set “Include ROWID Column” to True, since the underlying table doesn't have it's own Primary Key. It does have one column, RECORDNUM, which is a FK to another table. On this IG for TABLE_A, I have a column that is of Type “Link”, which opens a Modal Dialog Page in the same application. The Modal Dialog Page is a Form Region that has a Source of Type “Table / View” as well, let's call the source table here TABLE_B. I pass RECORDNUM as well as another column, YEAR, from TABLE_A (IG) to TABLE_B (Form). Here is where I start encountering issues, or at least certain trade-offs. I'd like to design all of this in a manner that is idiomatic for Oracle / APEX.
If I pass RECORDNUM and YEAR from TABLE_A to TABLE_B, the Form won't actually filter to the appropriate record unless RECORDNUM and YEAR are set to the Primary Key. Well, this has some side-affects. For one, if I have these set as the Primary Key, then I can't add a true Primary Key to the underlying table (both of these tables are very old, probably 30+ years).
The reason I want to add a true Primary Key to the underlying table is a) to take advantage of the declarative aspects of APEX and b) where I can I would like to modernize the structure of the underlying tables. But primarily for reason (a), I would simply like for APEX to manage all that for me. However, I then have the issue of being able to filter my Form. I still need to filter on RECORDNUM and YEAR, but I can't do that if they are not Primary Keys. So then, I thought, why not try to make my TABLE_A Interactive Grid have a Source of SQL Query instead of Table / View, and from there join to TABLE_B and grab the newly created PK, and pass that through the Link column over to the Form / TABLE_B. However, I then run into further issues (if I try to still use ROWID, it doesn't like that, and if I don't, well, then, how am I to perform updates declaratively on the source table of the IG? Also, that throws an error “An editable model requires an identityField”).
TL;DR
I would like to know what would be an idiomatic way to link TABLE_A to TABLE_B using an Interactive Grid (Parent) and a Form (Child), respectively? I can see why a Form can't filter on a FK (since that is frequently a many-to-one relationship), but I also don't know how to supply the PK of TABLE_B to the Form representing TABLE_B, when linking from the Interactive Grid representing TABLE_A.