Is it possible, and if so, then how to create two pages (one report, one edit form), but base the report on a view, and base the edit form on a table?
I have a very complex view, which is not update-able, so I would need to have the edit form be based on raw data from the tables. How is this possible?
First you need to create your report, and base it on your table. Then create your form.
To link them up you will need to know the primary key of the table your form is based on. I will be assuming your table has a PK consisting of 1 or 2 columns, and your automated row fetch + DML process uses this PK.
This is because the PK values form the link between the row in the report and the row in the form.
In your report attributes, you then locate the Link Column section - or if you want to use a column in your report as a link, then go to the options for the column, and locate the Link Column section there. The link has to be a "Link to custom target". Choose an icon or define the link text.
The link on the report attribute will mean you have to choose between single row column icon or a custom link. If a link is already in place, create a new null column in your report query, and configure the link in that column.
Settings:
Target: Page in this Application
Page: the page number of your form
Clear Cache: page number of your form
Then you pass along the values that uniquely identify a row in the table used for your form (your pk). Example:
Item 1: Name: "P2_EMPNO" Value: "#EMPNO#"
That's the gist of it, and is also how the link between a form and report is generated when you'd use the wizard for a form with a report.
More complex situation? For example, your PK has more than 2 columns. In Apex 4.1 you can base the automated row fetch and dml on the rowid of your table (a lifesaver). In this case you can consider to include the rowid of record of the table in the view (it needs to be aliased in your view of course). You can then pass this rowid to the item containing the rowid on your form page.
Not on 4.1, or rowid is not an option, and the PK has more than 2 columns: you will need to write your own row fetch and dml process or make a view with an instead-of trigger. I consider it a better practice to make a view of your table, and concatenate the PK columns into a column for your view. This column can then acts as your PK value in the apex generated processes. Either way, you need to come up with a way to identify a row, and provide the right way of fetching and processing on the form page. See this question for some more info.
