Oracle APEX: Assign primary key as interactive grid ROWID, Use select Interactive Grid ROWID in SQL query

0
votes

To preface -- I am as green as at gets.

I am tasked with building an app for internal org use. We have a DB with patient data, and in interface with a hospital electronic medical records system. patient data entered into the EMR is sent to us via interface to update the patient profile in our database. Partial matches require manual intervention.

  1. Message is received in a table within a staging schema
  2. attempts to match to existing patient
  3. if there are only 'partial matches' a status is set to 'mismatch'

I want to:

  1. Display a summary of all 'mismatch' status records. I want to use an interactive grid to select individual records.
  2. Set ROWID of interactive grid rows to the respective primary key of the record in the staging table.
  3. Use the selected Interactive Grid ROWID (user selects a specific record in the interactive grid) to select the matching primary key of the corresponding record in staging table to create SQL query to find potential matches within our DB. Potential matches are displayed in a second table.

Questions:

  1. How do I set the rowID of an Interactive grid to the unique key column of the staging table? --Some research shows I need a hidden item, is this correct?
  2. How do I access a rowID that has been selected in the Interactive grid to use in a SQL query?

My humble thanks and appreciation

1
sqloracleplsqloracle-apexoracle19c
Sorry if I've misread your question but you do understand the difference between a ROWID, and the Primary Key of a table? Typically you would choose one or the other, not both, for an interactive grid (e.g. you might use ROWID if the primary key of the table is a compound key). – Jeffrey Kemp

1 Answers

0
votes

So, your question is a bit confusing, but as far as I understand it. You are getting some data from table A, trying to match it with table B. If it matches, it irrelevant for us. If a match is not found you want to show it so that it can be manually matched.

In apex in order to update a table, you need to select what is the primary key by which it will update the data. That is usually a column in the table, but it can also be rowid(just include it in the SQL like any other column).

What I would suggest for you from what I understand of your situation.

Display the mismatched rows in an interactive grid, with rowid as primary key. Then you will need to have a column by which you match, if these entries already have some sort of key by which you tried to match but failed, display that. And have that column be a PopupLOV so the user can edit what value is in that field and set it to the appropriate match. One thing you will need to be careful about. You are editing a Unique key, or perhaps even Primary key, you might get conflicts here. Even if you only display unmatched data in the LOV, you can still have a user editing multiple rows and trying to match two rows to the same value, that will fail with an error that isnt particularly user friendly.