The editable Interactive Grid is intended to edit data that is already valid. This is no different from an APEX form page. The form or grid displays valid data for the user to enter/edit and validation is done when the page is submitted. A few times I have seen people ask about a different use case where the data comes from some external source and may not be valid. This came up again recently with a customer and it motivated me to work up a sample app to demonstrate some possible solutions.
If this use case interests you or even if you just want to learn more about client side validation and interactive grid you should download the IG Validate app. It only needs the EMP/DEPT sample data set installed and APEX version 21.2 or greater.
This is an advanced topic for people familiar with Interactive Grid and its related components and APIs. For background search this site for other posts about IG.
So in this use case the data to display and edit in the IG may not be valid or complete. For all web apps the server must validate all incoming data. APEX does this well with many fully declarative validation options. Validation can also be done on the client as a UX optimization. APEX has room for improvement in this area. APEX only does validation when the data is submitted. For IG this is when the page is submitted or in an ajax request when the IG is saved.
The issue people run into is that IG only sends rows that have been edited to the server. What if the user doesn’t correct all the errors or at least edit all the rows that have errors. The server can only validate what it is sent. This is not a flaw with IG but rather the result of trying to apply it to a use case that it wasn’t designed for.
The first solution that typically comes to mind is to send all the rows. The ask is “Is there an option for IG to send all the rows on save”. There isn’t. The follow up is “can you please add one”. I think it is unlikely that we will add such an option because it is very inefficient and as we will see there are better options. Note that the old tabular forms did send all the rows and this was a source of inefficiency that we intentionally fixed. Tabular forms didn’t scale well.
The workaround I see people try is to programmatically make an edit to each record in the model. This way every row is changed and will be sent to the server. This works but exactly when and how it is done can make a difference. Doing this on page load is not great because any refresh of the data such as with setting a filter or changing the sort order will give a warning that there are changes and if the user continues the changes are lost and now the grid can be saved without validating all rows. Another minor issue is the whole grid looks edited to the user but they made no changes. This just looks odd.
The best time to force each row to be changed is just before save. The sample app page EMP Validated 5 does this when the IG region Save button is pressed and page EMP Validated 6 does it just before the page is submitted. In both cases it is a hidden query only column that is modified. You could use a visible column but then the cells in that column are shown as edited when they may not have been edited by the user.
For full details on this solution and to play around with it. Download and run the IG Validate sample. On page EMP Unvalidated insert some invalid data. For example enter names that contain numbers, spaces, or special characters. Choose a manager that is not a manager or president. Enter empty string for Ename, Sal, Hiredate. Make up a new Job value. Enter a negative salary. Enter a commission when job is not salesman or no commission when job is salesman. Then try each of the other pages to see how these errors are handled. You should read the Notes for each page and also look at the page configuration including code and code comments. Just about all of the code examples use documented APIs any exceptions to this are clearly marked as undocumented in the code.
A big drawback of any solution that sends all data is that when the page is initially shown to the user they have no idea what the errors are. The errors are not indicated until the IG or page is saved.
This solution is also very inefficient. Not only is all data sent to the server, the server must process each row. The validation of every row is what you want but when there are no errors it must also check every row to see if it needs to be updated. It also selects all the rows and sends them all back to the client. So it is not just extra data sent in the request but also the response and extra processing overhead on the server.
A better solution is to validate all the data in the IG model on the client side any time the model data is fetched (or reverted). This is demonstrated in the sample app on pages EMP Validated 3 and EMP Validated 4. The difference between these pages is one marks the invalid data as errors and the other as warnings. Errors must be corrected by the user before the grid can be saved, warnings do not. Some use cases will want to make the user correct all the errors. In other cases you may want to let them correct some warnings or make other changes without having to fix everything at once. The whole concept of a validation “warning” is unique to the model layer of IG in APEX.
The benefit of this approach is that the user can see what is wrong before saving and only the data that has changed is sent. It doesn’t matter if the IG Save button is pressed or if the page is submitted.
A variation on this approach would be to do the validation on the server as part of the report rendering and incorporate it in a hidden column(s). This essentially means do the validation as part of the SQL select statement. Or perhaps the validation information is already part of the table if it is an interim data staging table. This is necessary if any of the validations cannot be done in JavaScript. Then the same technique of looping over the model and calling model.setValidity can be used to show the errors/warnings. The difference being that the actual validation messages come from hidden column(s) set by the server rather than being checked by the client.
The page EMP Validated 1 just adds server side validation. Making sure you have solid server side validation should always be the first step. Page EMP Validate 2 adds client side validation. This is a good example of how to do client side validation for items used in IG. In the past I wrote about client side validation. The key was to set Fire on Initialization to On for DA JavaScript validation. Here a different technique is used that overrides the item reinit method, which is something that wasn’t possible too long ago. It corrects a problem that happens for some item types when activating a cell when the previous cell in the same row had a custom validation error. It is also an example of client side validation that depends on the value of another item. Specifically COMM depends on the JOB column.
Page EMP Validated 2a shows how model errors can be shown at page level just like normal APEX form validations. This could be of interest in other use cases as well.
All of these solutions to using interactive grid to get the user to correct initially invalid data relies on getting all of the data to the client. If the source has hundreds of records or more and the IG region is just showing the first 50 or so this isn’t going to work. Even if there are just 20 or so records and the user enters a filter that reduces the set to 5 they will be able to save without correcting all the errors. There are many more details to work out, especially on the server side, that are going to be very specific to your use case. The intent here is not to show a full working example but to show some general techniques that may be helpful in crafting a solution that fits your specific needs.
There are other cases where having all the data on the client is needed. For example it may be helpful when trying to do cross row validations or computations on the client side. You need to ensure that the amount of data is not so much that it causes performance problems. There are comments in the code on page 5 that give some hints about how to load all the data into the model. There are a number of ways and how you do it is going to depend on your needs. One way is to use pagination type Page and set the rowsPerPage to more than the expected number of rows. Another is to use the model fetchAll method. Setting Lazy Loading option to On can be helpful. In all cases setting the model pageSize option can be helpful to reduce the number of requests needed to get all the data. You should understand the ramifications of how scroll and virtual pagination options affect what data is in the model and what is rendered to the DOM.